程式師世界 >> 編程語言 >> .NET網頁編程 >> 關於.NET >> ADO.NET Entity Framework(5)esql (二)

ADO.NET Entity Framework(5)esql (二)

編輯：關於.NET

esql函數統計類

Avg

平均值

myContext context = new myContext();
string esql = "SELECT value AVG(it.ItemValue) FROM myContext.DBItemList as it";
ObjectQuery<int> query = context.CreateQuery<int>(esql);
foreach (int n in query)
{
Console.WriteLine(n);
}

/* print:
3
*/

BigCount

個數(long)

myContext context = new myContext();
string esql = "SELECT value BigCount(it.ItemValue) FROM myContext.DBItemList as it";
ObjectQuery<long> query = context.CreateQuery<long>(esql);
foreach (long n in query)
{
Console.WriteLine(n);
}

/* print:
15
*/

Count

個數(int)

myContext context = new myContext();
string esql = "SELECT value Count(it.ItemValue) FROM myContext.DBItemList as it";
ObjectQuery<int> query = context.CreateQuery<int>(esql);
foreach (int n in query)
{
Console.WriteLine(n);
}

/* print:
15
*/

Max

最大值

myContext context = new myContext();
string esql = "SELECT value Max(it.ItemValue) FROM myContext.DBItemList as it";
ObjectQuery<int> query = context.CreateQuery<int>(esql);
foreach (int n in query)
{
Console.WriteLine(n);
}

/* print:
6
*/

Min

最小值

myContext context = new myContext();
string esql = "SELECT value Min(it.ItemValue) FROM myContext.DBItemList as it";
ObjectQuery<int> query = context.CreateQuery<int>(esql);
foreach (int n in query)
{
Console.WriteLine(n);
}

/* print:
1
*/

Sum

合計

myContext context = new myContext();
string esql = "SELECT value Sum(it.ItemValue) FROM myContext.DBItemList as it";
ObjectQuery<int> query = context.CreateQuery<int>(esql);
foreach (int n in query)
{
Console.WriteLine(n);
}

/* print:
54
*/

聯合使用

myContext context = new myContext();
string esql = "SELECT Max(it.ItemValue) as Max , Min(it.ItemValue) as Min FROM myContext.DBItemList as it";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);
foreach (DbDataRecord r in query)
{
Console.WriteLine("Max:{0},Min:{1}", r["Max"], r["Min"]);
}

/* print:
Max:6,Min:1
*/

與group by一起使用

myContext context = new myContext();
string esql = "SELECT ID as ItemID , Max(it.ItemValue) as Max , Min(it.ItemValue) as Min FROM myContext.DBItemList as it group by it.ItemID as ID";
ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);
foreach (DbDataRecord r in query)
{
Console.WriteLine("ItemID:{0}, Max:{1},Min:{2}", r["ItemID"], r["Max"], r["Min"]);
}

/* print:
ItemID:a, Max:6,Min:2
ItemID:b, Max:5,Min:1
ItemID:c, Max:6,Min:2
*/

數學類

Abs

絕對值

Abs(-2) Round

隨機數

Round(748.58)

日期

CurrentDateTime() CurrentDateTimeOffset() CurrentUtcDateTime() Day( expression ) Day(cast('03/12/1998' as DateTime)) --返回:12 GetTotalOffsetMinutes --返回:

SQL Server 2008 only

Hour ( expression ) Hour(cast('22:35:5' as DateTime)) --返回:22 Minute( expression ) Minute(cast('22:35:5' as DateTime)) --返回:35 Month (expression) Month(cast('03/12/1998' as DateTime)) --返回:3 Second( expression ) Second(cast('22:35:5' as DateTime)) --返回:5 Year( expression ) Year(cast('03/12/1998' as DateTime)) --返回:1998

字符

Concat ( string1, string2)

字符串連接

Concat('abc', 'xyz') --返回:abcxyz IndexOf( string1, string2)

字符串位置查找

IndexOf('xyz', 'abcxyz') --返回:4 Length ( string )

字符串長度

Legth('abcxyz') --返回:6 Reverse ( string )

字符串反轉

Reverse('abcd') --返回:dcba ToLower( string )

大寫轉小寫

ToLower('ABC') --返回:abc ToUpper( string )

小寫轉大寫

ToUpper('abc') --返回:ABC Trim( string )

去兩端空格

Trim(' abc ') --返回:abc LTrim( string )

去左端空格

LTrim(' abc') --返回:abc RTrim( string )

去右端空格

Left ( string, length)

左端截取

Left('abcxyz', 3) --返回:abc Right ( string, length)

右端截取

Right('abcxyz', 3) --返回:xyz Substring ( string, start, length)

兩端截取

Substring('abcxyz', 4, 3) --返回:xyz

esql語句查詢語句

SELECT

myContext context = new myContext();

string esql = "SELECT it.ItemValue as a,it.NameID FROM [DBItemList] AS it";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)
{
Console.WriteLine("{0},{1}", r["a"], r["NameID"]);
}

SELECT
1 AS [C1],
[Extent1].[ItemValue] AS [ItemValue],
[Extent1].[NameID] AS [NameID]
FROM [dbo].[DBItemList] AS [Extent1]

WHERE

myContext context = new myContext();

string esql = "SELECT VALUE it FROM [DBItemList] AS it WHERE (it.ItemValue=5 or it .ItemValue=5) and it.NameID='n01' ";

ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);

foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}

SELECT
[Extent1].[AutoId] AS [AutoId],
[Extent1].[NameID] AS [NameID],
[Extent1].[ItemID] AS [ItemID],
[Extent1].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [Extent1]
WHERE (([Extent1].[ItemValue] = 5) OR ([Extent1].[ItemValue] = 5)) AND ([Extent1].[NameID] = 'n01')

GROUP BY

myContext context = new myContext();

string esql = "SELECT it.ItemID,Sum(it.ItemValue) as ValueSum FROM [DBItemList] AS it GROUP BY it.ItemID ";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)
{
Console.WriteLine("{0},{1}", r["ItemID"], r["ValueSum"]);
}

SELECT
1 AS [C1],
[GroupBy1].[K1] AS [ItemID],
[GroupBy1].[A1] AS [C2]
FROM ( SELECT
　　[Extent1].[ItemID] AS [K1],
　　SUM([Extent1].[ItemValue]) AS [A1]
　　FROM [dbo].[DBItemList] AS [Extent1]
　　GROUP BY [Extent1].[ItemID]
) AS [GroupBy1]

ORDER BY

myContext context = new myContext();

string esql = "SELECT VALUE it FROM [DBItemList] AS it ORDER BY it.ItemValue,it.ItemID desc ";

ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);

foreach (DBItemList r in query)
{
Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);
}

SELECT
[Extent1].[AutoId] AS [AutoId],
[Extent1].[NameID] AS [NameID],
[Extent1].[ItemID] AS [ItemID],
[Extent1].[ItemValue] AS [ItemValue]
FROM [dbo].[DBItemList] AS [Extent1]
ORDER BY [Extent1].[ItemValue] ASC, [Extent1].[ItemID] DESC

HAVING

myContext context = new myContext();

string esql = "SELECT it.ItemID,Count(it.ItemValue) as ValueSum FROM DBItemList AS it GROUP BY it.ItemID HAVING SUM(it.ItemValue) > 5";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)
{
Console.WriteLine("{0},{1}", r["ItemID"], r["ValueSum"]);
}

SELECT
1 AS [C1],
[GroupBy1].[K1] AS [ItemID],
[GroupBy1].[A2] AS [C2]
FROM ( SELECT
　　[Extent1].[ItemID] AS [K1],
　　SUM([Extent1].[ItemValue]) AS [A1],
　　COUNT([Extent1].[ItemValue]) AS [A2]
　　FROM [dbo].[DBItemList] AS [Extent1]
　　GROUP BY [Extent1].[ItemID]
) AS [GroupBy1]
WHERE [GroupBy1].[A1] > 5

JOIN

Cross Joins
Inner Joins
Left Outer Joins
Right Outer Joins
Full Outer Joins

CASE語句

CASE WHEN THEN ELSE END

myContext context = new myContext();

string esql = "select it.ItemID, it.ItemValue ,(Case when it.ItemValue =1 then '差' when it.ItemValue between 2 and 4 then '好' else '其他' end) as ItemValueRemarks from myContext.DBItemList as it";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)
{
Console.WriteLine("{0},{1},{2}", r["ItemID"], r["ItemValue"], r["ItemValueRemarks"]);
}
/*
c,4,好
c,5,其他
c,2,好
c,3,好
b,5,其他
c,6,其他
b,2,好
b,1,差
c,3,好
a,4,好
a,5,其他
a,2,好
a,3,好
a,6,其他
a,3,好
*/

esql 類型簡單類型

Null

is Null
is not Null

myContext context = new myContext();

ObjectQuery<typeTest> query = context.typeTest.Where("it.b is not Null");

foreach (typeTest r in query)
{
Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值);
}

Boolean

True,False

myContext context = new myContext();

ObjectQuery<typeTest> query = context.typeTest.Where("it.e==True");

foreach (typeTest r in query)
{
Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值);
}

Integer

Float,Double

Decimal

123
123.456
23.34

myContext context = new myContext();

ObjectQuery<typeTest> query = context.typeTest.Where("it.c==123");
foreach (typeTest r in query)
{
Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值);
}

String

"abcd"
N"U字符"
'abcd'

myContext context = new myContext();

// ObjectQuery<typeTest> query = context.typeTest.Where("it.b==N'冬冬'");
ObjectQuery<typeTest> query = context.typeTest.Where("it.b=="冬冬" ");
// ObjectQuery<typeTest> query = context.typeTest.Where("it.b=='冬冬'");
foreach (typeTest r in query)
{
Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值);
}

DateTime

DATETIME'2007-11-11 22:22'
DATETIME'2007-11-11 01:01:00.0000000'

myContext context = new myContext();

// ObjectQuery<typeTest> query = context.typeTest.Where("it.d==DATETIME'1977-11-11 00:00:00.000'");
ObjectQuery<typeTest> query = context.typeTest.Where("it.d==cast('1977-11-11' as System.DateTime)");

foreach (typeTest r in query)
{
Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值);
}

Time

TIME'22:11'
TIME'01:02:03.1234567'

DateTimeOffset

DATETIMEOFFSET'2007-11-11 22:11 +02:00'
DATETIMEOFFSET'2007-11-11 01:01:00.0000000 -02:00'

Binary

Binary'00ffaabb'
X'ABCabc'
BINARY '0f0f0f0F0F0F0F0F0F0F'
X'' –空

Guid

Guid'0321AF86-0AA5-4a86-A086-1D789FA54AA3'
GUID '0321AF86-0AA5-4a86-A086-1D789FA54AA3'

myContext context = new myContext();
ObjectQuery<typeTest> query = context.typeTest.Where("it.a==Guid'0321af86-0aa5-4a86-a086-1d789fa54aa3'");
foreach (typeTest r in query)
{
Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值);
}

REF

myContext context = new myContext();
//string esql = "SELECT it.ItemID FROM DBItem as it";
string esql = "SELECT REF(it).ItemID FROM DBItem as it";
ObjectQuery<DbDataRecord> v = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in v)
{
Console.WriteLine("{0}", r[0]);
}

ROW

myContext context = new myContext();

string esql = "select value row( it.ItemValue ,it.NameID,'wxd' as wxwinter) from myContext.DBItemList as it";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)
{
Console.WriteLine("{0},{1},{2}", r["ItemValue"], r["NameID"], r["wxwinter"]);
}

myContext context = new myContext();

string esql = "select row( it.ItemValue ,it.NameID) as wxd ,it.ItemID from myContext.DBItemList as it";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)
{
DbDataRecord v = r["wxd"] as DbDataRecord;
Console.WriteLine("{0},{1},{2}", r["ItemID"],v["ItemValue"],v["NameID"]);
}

集合

MULTISET(1,2,3,4)
{1,2,3,4}
SELECT VALUE it FROM [DBItemList] AS it WHERE it.ItemValue IN {1,2,3}

myContext context = new myContext();

string esql = "{1,2,3} ";

ObjectQuery<int> query = context.CreateQuery<int>(esql);

foreach (int r in query)
{
System.Console.WriteLine(r);
}
SELECT
[UnionAll2].[C1] AS [C1]
FROM (SELECT
　　[UnionAll1].[C1] AS [C1]
　　FROM (SELECT
　　　　1 AS [C1]
　　　　FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
　　UNION ALL
　　　　SELECT
　　　　2 AS [C1]
　　　　FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable2]) AS [UnionAll1]
　　UNION ALL
　　　　SELECT
　　　　3 AS [C1]
　　　　FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable3]) AS [UnionAll2]

myContext context = new myContext();

string esql = "{row(1 as a,'wxd' as wxwinter),row(2 as a,'lzm' as wxwinter),row(3 as a,'wxwinter' as wxwinter)} ";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)
{
System.Console.WriteLine("{0},{1}",r["a"],r["wxwinter"]);
}
/*
1,wxd
2,lzm
3,wxwinter
*/
SELECT
1 AS [C1],
CASE WHEN ([UnionAll2].[C1] = 0) THEN 1 WHEN ([UnionAll2].[C1] = 1) THEN 2 ELSE 3 END AS [C2],
CASE WHEN ([UnionAll2].[C1] = 0) THEN 'wxd' WHEN ([UnionAll2].[C1] = 1) THEN 'lzm' ELSE 'wxwinter' END AS [C3]
FROM (SELECT
　　[UnionAll1].[C1] AS [C1]
　　FROM (SELECT
　　　　0 AS [C1]
　　　　FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
　　UNION ALL
　　　　SELECT
　　　　1 AS [C1]
　　　　FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable2]) AS [UnionAll1]
UNION ALL
　　SELECT
　　2 AS [C1]
　　FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable3]) AS [UnionAll2]

Object 返回對像

Select 選出的值可以直接創建為對像後,將對像放入字段中返回

myContext context = new myContext();
string esql = "SELECT [WindowsFormsApplication8].[DBItemEx](it.ItemID + 'b') as myObject FROM DBItem as it";
ObjectQuery<DbDataRecord> v = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in v)
{
DBItemEx obj = r["myObject"] as DBItemEx;

Console.WriteLine("{0}", obj.ItemID);

}

CAST 類型轉換

myContext context = new myContext();

string esql = "select value CAST(it.ItemValue as System.String) from myContext.DBItemList as it";

ObjectQuery<string> query = context.CreateQuery<string>(esql);

foreach (string r in query)
{
Console.WriteLine(r);
}
myContext context = new myContext();

string esql = "using System;select value CAST(it.ItemValue as String) from myContext.DBItemList as it";

ObjectQuery<string> query = context.CreateQuery<string>(esql);

foreach (string r in query)
{
Console.WriteLine(r);
}
CAST( d as Edm.Decimal(16, 2) )

OFTYPE

OFTYPE ( expression, [ONLY] test_type )

myContext context = new myContext();
string esql = "OFTYPE(((SELECT VALUE it FROM DBItem as it) ),[WindowsFormsApplication8].[DBItemEx])";
ObjectQuery<DBItemEx> v = context.CreateQuery<DBItemEx>(esql);

與如下效果相同

myContext context = new myContext();
ObjectQuery<DBItemEx> v = context.DBItem.OfType<DBItemEx>();

TREAT

myContext context = new myContext();
string esql = "SELECT TREAT(it AS [WindowsFormsApplication8].[DBItemEx]) FROM DBItem AS it";
ObjectQuery<DbDataRecord> v = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in v)
{
DBItemEx obj = r[0] as DBItemEx;
if (obj != null)
{
Console.WriteLine("{0}", obj.ItemID);
}
}

與如下效果類似

myContext context = new myContext();
ObjectQuery<DBItemEx> v = context.DBItem.OfType<DBItemEx>();

IS 類型判斷

myContext context = new myContext();
string esql = "SELECT TREAT(it AS [WindowsFormsApplication8].[DBItemEx]) FROM DBItem AS it WHERE it IS OF ([WindowsFormsApplication8].[DBItemEx])";
ObjectQuery<DbDataRecord> v = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in v)
{
DBItemEx obj = r[0] as DBItemEx;
// if (obj != null)
{
Console.WriteLine("{0}", obj.ItemID);
}
}

與如下效果類似

myContext context = new myContext();
ObjectQuery<DBItemEx> v = context.DBItem.OfType<DBItemEx>();

esql Namespace 使用SqlServer函數

using SqlServer;

myContext context = new myContext();
string esql = "using SqlServer;select it.ItemValue ,LEN(it.NameID) as NameIDLEN from myContext.DBItemList as it";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)
{
Console.WriteLine("{0},{1}",r["ItemValue"],r["NameIDLEN"]);
}

SqlServer.函數

myContext context = new myContext();
string esql = "select it.ItemValue ,SqlServer.LEN(it.NameID) as NameIDLEN from myContext.DBItemList as it";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)
{
Console.WriteLine("{0},{1}",r["ItemValue"],r["NameIDLEN"]);
}

SELECT
1 AS [C1],
[Extent1].[ItemValue] AS [ItemValue],
LEN([Extent1].[NameID]) AS [C2]
FROM [dbo].[DBItemList] AS [Extent1]

使用NET的數據類型

myContext context = new myContext();

string esql = "using System;select value CAST(it.ItemValue as String) from myContext.DBItemList as it";

ObjectQuery<string> query = context.CreateQuery<string>(esql);

foreach (string r in query)
{
Console.WriteLine(r);
}
myContext context = new myContext();

// ObjectQuery<typeTest> query = context.typeTest.Where("it.d==DATETIME'1977-11-11 00:00:00.000'");
ObjectQuery<typeTest> query = context.typeTest.Where("it.d==cast('1977-11-11' as System.DateTime)");

foreach (typeTest r in query)
{
Console.WriteLine("{0},{1},{2},{3},{4},{5}",r.a,r.b,r.c,r.d,r.e,r.值);
}

esql關系,導航 KEY

myContext context = new myContext();
string esql="SELECT VALUE [TargetEntity] FROM (SELECT VALUE x FROM [myContext].[FK_DBItemList_DBItem] AS x WHERE Key(x.[DBItem]) = ROW(@EntityKeyValue1 AS EntityKeyValue1)) AS [AssociationEntry] INNER JOIN [myContext].[DBItemList] AS [TargetEntity] ON Key([AssociationEntry].[DBItemList]) = Key(Ref([TargetEntity]))";

ObjectQuery<DBItemList> dbitemlist = context.CreateQuery<DBItemList>(esql, new ObjectParameter("EntityKeyValue1", "a"));

foreach (DBItemList r in dbitemlist)
{
Console.WriteLine("{0},{1},{2}", r.AutoId, r.ItemValue, r.NameID);
}

效果同下

myContext context = new myContext();

ObjectQuery<DBItemList> dbitemlist = context.DBItem.First(p => p.ItemID == "a").DBItemList.CreateSourceQuery();

foreach (DBItemList r in dbitemlist)
{
Console.WriteLine("{0},{1},{2}", r.AutoId, r.ItemValue, r.NameID);
}