ADO.NET Entity Framework ESQL查询语句

来源：互联网发布：淘宝的全球购在哪里编辑：程序博客网时间：2024/05/22 06:52

比起 LINQ to SQL，EF 除了提供 LINQ 查询方式, 还提供了 Entity SQL language

ESQL 类似 Hibernate 的 HSQL，ESQL 与SQL 语言的语法相似，以字符串的方式执行

esql的查询结果集 ObjectQuery

ObjectQuery<实体>

myContext context =new myContext();

string esql ="SELECT VALUE DBItemList FROM myContext.DBItemList";

// ObjectQuery<DBItemList> query = new ObjectQuery<DBItemList>(esql, context);

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

foreach (DBItemList rin query)

{

Console.WriteLine(r.NameID);

}

myContext context =new myContext();

string esql ="SELECT VALUE it FROM myContext.DBItemList as it";

// ObjectQuery<DBItemList> query = new ObjectQuery<DBItemList>(esql, context);

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

foreach (DBItemList rin query)

{

Console.WriteLine(r.NameID);

}

ObjectQuery<DbDataRecord>

myContext context =new myContext();

string esql ="SELECT it.NameID FROM myContext.DBItemList as it";

//ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(esql, context);

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

foreach (DbDataRecord rin query)

{

Console.WriteLine(r["NameID"].ToString());

}

ObjectQuery<简单类型>

myContext context =new myContext();

string esql ="SELECT value count(it.NameID) FROM myContext.DBItemList as it";

// ObjectQuery<int> query = new ObjectQuery<int>(esql, context);

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

foreach (int nin query)

{

Console.WriteLine(n);

}

myContext context =new myContext();

string esql ="SELECT value it.NameID FROM myContext.DBItemList as it";

// ObjectQuery<int> query = new ObjectQuery<int>(esql, context);

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

foreach (string nin query)

{

Console.WriteLine(n);

}

esql的使用

可以在

ObjectQuery的Linq方法,
构造ObjectQuery,
context.CreateQuery返方法,

中使用esql,并得到返回的榄查询结果ObjectQuery

it关键字

[it] 出现在 ESQL 中, 由 ObjectQuery<T>.Name 属性设定，用于标示源查询对象(ObjectQuery)的名称，

类似于 "SELECT * FROM Tab as it WHERE it.ItemValue =14" 。

可以将这个默认值 "it" 改成其他字符串。

myContext context =new myContext();

context.DBItemList.Name = "wxd";

ObjectQuery<DBItemList> list = context.DBItemList.Where("wxd.ItemValue=5");

myContext context =new myContext();

var sql ="SELECT VALUE DBItemList FROM myContext.DBItemList";

var query =new ObjectQuery<DBItemList>(sql, context);

query.Name = "wxd";

ObjectQuery<DBItemList> list = query.Where("wxd.ItemValue=@v",new ObjectParameter("v", 5));

value 关键字

value 后只能返回一个成员

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 nin query)

{

Console.WriteLine(n);

}

/* print:

string esql ="select value it.ItemID from myContext.DBItemList as it";

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

foreach (string rin query)

{

Console.WriteLine(r);

}

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 rin query)

{

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

}

查询参数的使用

myContext context =new myContext();

string esql ="SELECT VALUE it FROM myContext.DBItemList as it where it.ItemValue=@v1 or it.NameID=@v2";

ObjectParameter v1 =new ObjectParameter("v1", 3);

ObjectParameter v2 =new ObjectParameter("v2","n01");

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

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}",r.NameID,r.ItemValue);

}

中文字段

使用[]将字段括起来

myContext context =new myContext();

ObjectQuery<typeTest> query = context.typeTest.Where("it.值 ==22.22");

System.Console.WriteLine(query.CommandText);

foreach (typeTest rin query)

{

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

}

myContext context =new myContext();

ObjectQuery<typeTest> query = context.typeTest.Where("it.[值] ==22.22");

System.Console.WriteLine(query.CommandText);

foreach (typeTest rin query)

{

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

}

得到esql与sql字串

myContext context =new myContext();

string esql ="SELECT VALUE it FROM myContext.DBItemList as it";

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

Console.WriteLine(query.CommandText);

Console.WriteLine(query.ToTraceString())

SELECT VALUE it FROM myContext.DBItemList as it

SELECT

[Extent1].[AutoId] AS [AutoId],

[Extent1].[NameID] AS [NameID],

[Extent1].[ItemID] AS [ItemID],

[Extent1].[ItemValue] AS [ItemValue]

FROM [dbo].[DBItemList] AS [Extent1]

CommandText属性

得到esql字串

ToTraceString方法

得到sql字串

ObjectQuery的Linq方法

Where

用字符串为条件进行查询

ObjectQuery<T> Where(string predicate,params ObjectParameter[] parameters);

myContext context =new myContext();

ObjectQuery<DBItemList> list = context.DBItemList.Where("(it.ItemValue=5 or it .ItemValue=5) and it.NameID='n01'");

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')

OrderBy

排序

ObjectQuery<T> OrderBy(string keys,params ObjectParameter[] parameters);

myContext context =new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.OrderBy("it.ItemValue,it.ItemID desc");

foreach (var rin query)

{

Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);

}

SELECT

[Extent1].[AutoId]AS [AutoId],

[Extent1].[NameID]AS [NameID],

[Extent1].[ItemID]AS [ItemID],

[Extent1].[ItemValue]AS [ItemValue]

FROM[dbo].[DBItemList]AS [Extent1]

ORDERBY [Extent1].[ItemValue]ASC, [Extent1].[ItemID]DESC

Select

射影

ObjectQuery<DbDataRecord> Select(string projection,params ObjectParameter[] parameters);

myContext context =new myContext();

ObjectQuery<DbDataRecord> list = context.DBItemList.Select(" it.ItemValue as a,it.NameID ");

SELECT

1 AS [C1],

[Extent1].[ItemValue]AS [ItemValue],

[Extent1].[NameID]AS [NameID]

FROM[dbo].[DBItemList]AS [Extent1]

SelectValue(projection)

返回只有一组字段的数组

ObjectQuery<TResultType> SelectValue<TResultType>(string projection,params ObjectParameter[] parameters);

myContext context =new myContext();

ObjectQuery<int> query = context.DBItemList.SelectValue<int>("it.ItemValue + it.AutoID");

foreach (var rin query)

{

Console.WriteLine(r);

}

SELECT

[Extent1].[ItemValue]+ [Extent1].[AutoId]AS [C1]

FROM[dbo].[DBItemList]AS [Extent1]

Top(count)

集合的前n个元素

count :前n个元素

ObjectQuery<T> Top(string count,params ObjectParameter[] parameters);

myContext context =new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.Top("3"); ;

foreach (var rin query)

{

Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);

}

SELECTTOP (3)

[c].[AutoId]AS [AutoId],

[c].[NameID]AS [NameID],

[c].[ItemID]AS [ItemID],

[c].[ItemValue]AS [ItemValue]

FROM[dbo].[DBItemList]AS [c]

Skip(keys,count)

跳过集合的前n个元素,

keys : 用于排序的字段

count :要跳过的记录个数

ObjectQuery<T> Skip(string keys,string count, paramsObjectParameter[] parameters);

myContext context =new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue","5");

foreach (var rin query)

{

Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);

}

SELECT

[Extent1].[AutoId]AS [AutoId],

[Extent1].[NameID]AS [NameID],

[Extent1].[ItemID]AS [ItemID],

[Extent1].[ItemValue]AS [ItemValue]

FROM( SELECT [Extent1].[AutoId]AS [AutoId],[Extent1].[NameID]AS [NameID],[Extent1].[ItemID]AS [ItemID],[Extent1].[ItemValue]AS [ItemValue],row_number() OVER (ORDERBY [Extent1].[ItemValue]ASC) AS [row_number]

FROM [dbo].[DBItemList]AS [Extent1]

)AS [Extent1]

WHERE[Extent1].[row_number]> 5

ORDERBY [Extent1].[ItemValue]ASC

分页 Skip Top

Skip与Top一起使用

myContext context =new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue","5").Top("3"); ;

foreach (var rin query)

{

Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);

}

SELECTTOP (3)

[Extent1].[AutoId]AS [AutoId],

[Extent1].[NameID]AS [NameID],

[Extent1].[ItemID]AS [ItemID],

[Extent1].[ItemValue]AS [ItemValue]

FROM [dbo].[DBItemList]AS [Extent1]

)AS [Extent1]

WHERE[Extent1].[row_number]> 5

ORDERBY [Extent1].[ItemValue]ASC

GroupBy(keys,projection)

分组

keys: GROUP BY的字段

projection : Select的内容

ObjectQuery<DbDataRecord> GroupBy(string keys,string projection, paramsObjectParameter[] parameters);

myContext context =new myContext();

ObjectQuery<DbDataRecord> query = context.DBItemList.GroupBy("it.ItemID","it.ItemID,Sum(it.ItemValue) as ValueSum");

foreach (var rin query)

{

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

}

a,23

b,8

c,23

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]

SELECT it.ItemID,Sum(it.ItemValue) as ValueSum

FROM ( [DBItemList] ) AS it

GROUP BY it.ItemID

Include(path)

加载关联数据,参数为实体的[导航属性]的字串,调用Include("导航属性")后,关联数据会加载,这样就不用在[实体.导航属性]上调用Load()方法

ObjectQuery<T> Include(string path);

myContext context =new myContext();

var r = context.DBItem.Include("DBItemList");

foreach (var dbitemin r)

{

foreach (var dbitemlistin dbitem.DBItemList)

{

Console.WriteLine("{0},{1}", dbitemlist.NameID, dbitemlist.ItemValue);

}

效果与下例相同

myContext context =new myContext();

var r = context.DBItem;

foreach (var dbitemin r)

{

dbitem.DBItemList.Load();

foreach (var dbitemlistin dbitem.DBItemList)

{

Console.WriteLine("{0},{1}", dbitemlist.NameID, dbitemlist.ItemValue);

}

esql注释,成员访问,分行

注释

成员访问

分行

;

esql运算符

算术运算符

加

减

乘

除

模

负

比效运算符

等于

大于

大于等于

空判断

IS NOT NULL

IS NULL

小于

小天等于

不等于

字符比效

LIKE ''

NOT LIKE ''

% :

_ :

[ ] :

[^] :

逻辑运算符

与

AND

非

NOT

或

区间

BETWEEN

之间

BETWEEN n AND m

NOT BETWEEN n AND m

myContext context =new myContext();

string esql ="SELECT VALUE it FROM [DBItemList] AS it WHERE it.ItemValue BETWEEN 2 and 4";

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

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);

}

myContext context =new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.Where(" it.ItemValue not BETWEEN 2 and 4");

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);

}

在集合中

IN {v,v}

NOT IN{v,v}

myContext context =new myContext();

string esql ="SELECT VALUE it FROM [DBItemList] AS it WHERE it.ItemValue IN {1,2,3}";

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

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);

}

myContext context =new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.Where("it.ItemValue not IN {1,2,3}");

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);

}

EXISTS

存在

EXISTS(select from)

NOT EXISTS(select from)

myContext context =new myContext();

string esql ="SELECT VALUE it FROM [DBItemList] AS it WHERE exists(Select VALUE it2 From DBItem as it2 Where it2.ItemID=it.ItemID )";

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

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);

}

myContext context =new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.Where("exists(Select VALUE it2 From DBItem as it2 Where it2.ItemID=it.ItemID )");

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);

}

分页

SELECT VALUE it FROM ( [DBItemList] ) AS it

ORDER BY it.ItemValue

SKIP 5

LIMIT 3

集合运算

Union

(合集)

连接不同集合

UNION --自动过滤相同项

UNION ALL --两个集合的相同项都会返回

myContext context =new myContext();

string esql ="(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ) UNION (select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' )";

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

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);

}

EXCEPT

(左并集)

从集合中删除其与另一个集合中相同的项

myContext context =new myContext();

string esql ="(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ) EXCEPT (select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' )";

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

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);

}

INTERSECT

（交集）

获取不同集合的相同项

myContext context =new myContext();

string esql ="(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ) INTERSECT (select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' )";

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

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);

}

ANYELEMENT

集合中的第一个

myContext context =new myContext();

string esql ="ANYELEMENT(select value it from myContext.DBItemList as it where it.ItemID == 'a') ";

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

//如果集合中有两个以上,只返回第一个到集合中

foreach (DBItemList rin query)

{

Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue);

}

SELECT

[Element1].[AutoId]AS [AutoId],

[Element1].[NameID]AS [NameID],

[Element1].[ItemID]AS [ItemID],

[Element1].[ItemValue]AS [ItemValue]

FROM( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]

LEFTOUTER JOIN(SELECT TOP (1)

[Extent1].[AutoId]AS [AutoId],

[Extent1].[NameID]AS [NameID],

[Extent1].[ItemID]AS [ItemID],

[Extent1].[ItemValue]AS [ItemValue]

FROM [dbo].[DBItemList]AS [Extent1]

WHERE [Extent1].[ItemID]= 'a' ) AS [Element1]ON 1 = 1

OVERLAPS

两个集合是否有相交部份

myContext context =new myContext();

string esql ="(select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID=='b' ) OVERLAPS (select value it from myContext.DBItemList as it where it.ItemID == 'a' || it.ItemID=='b')";

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

foreach (bool rin query)

{

Console.WriteLine(r);

}

//print: True

SELECT

CASEWHEN ( EXISTS (SELECT

cast(1 as bit) AS [C1]

FROM (SELECT

[Extent1].[AutoId]AS [AutoId],

[Extent1].[NameID]AS [NameID],

[Extent1].[ItemID]AS [ItemID],

[Extent1].[ItemValue]AS [ItemValue]

FROM [dbo].[DBItemList]AS [Extent1]

WHERE ([Extent1].[ItemID]= 'c')OR ([Extent1].[ItemID]= 'b')

INTERSECT

SELECT

[Extent2].[AutoId]AS [AutoId],

[Extent2].[NameID]AS [NameID],

[Extent2].[ItemID]AS [ItemID],

[Extent2].[ItemValue]AS [ItemValue]

FROM [dbo].[DBItemList]AS [Extent2]

WHERE ([Extent2].[ItemID]= 'a')OR ([Extent2].[ItemID]= 'b'))AS [Intersect1]

))THEN cast(1as bit)WHEN ( NOT EXISTS (SELECT

cast(1 as bit) AS [C1]

FROM (SELECT

[Extent3].[AutoId]AS [AutoId],

[Extent3].[NameID]AS [NameID],

[Extent3].[ItemID]AS [ItemID],

[Extent3].[ItemValue]AS [ItemValue]

FROM [dbo].[DBItemList]AS [Extent3]

WHERE ([Extent3].[ItemID]= 'c')OR ([Extent3].[ItemID]= 'b')

INTERSECT

SELECT

[Extent4].[AutoId]AS [AutoId],

[Extent4].[NameID]AS [NameID],

[Extent4].[ItemID]AS [ItemID],

[Extent4].[ItemValue]AS [ItemValue]

FROM [dbo].[DBItemList]AS [Extent4]

WHERE ([Extent4].[ItemID]= 'a')OR ([Extent4].[ItemID]= 'b'))AS [Intersect2]

))THEN cast(0as bit)END AS [C1]

FROM( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]

Set

去掉重复项

myContext context =new myContext();

string esql ="set(select value it.ItemID from myContext.DBItemList as it)";

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

foreach (string rin query)

{

Console.WriteLine(r);

}

//去掉了重复项

SELECT

[Distinct1].[ItemID]AS [ItemID]

FROM( SELECT DISTINCT

[Extent1].[ItemID]AS [ItemID]

FROM [dbo].[DBItemList]AS [Extent1]

)AS [Distinct1]

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 nin query)

{

Console.WriteLine(n);

}

/* print:

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 nin query)

{

Console.WriteLine(n);

}

/* print:

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 nin query)

{

Console.WriteLine(n);

}

/* print:

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 nin query)

{

Console.WriteLine(n);

}

/* print:

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 nin query)

{

Console.WriteLine(n);

}

/* print:

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 nin query)

{

Console.WriteLine(n);

}

/* print:

联合使用

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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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]

ORDERBY [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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin query)

{

System.Console.WriteLine(r);

}

SELECT

[UnionAll2].[C1]AS [C1]

FROM(SELECT

[UnionAll1].[C1]AS [C1]

FROM (SELECT

1 AS [C1]

FROM ( SELECTcast(1 as bit) AS X ) AS [SingleRowTable1]

UNION ALL

SELECT

2 AS [C1]

FROM ( SELECTcast(1 as bit) AS X ) AS [SingleRowTable2])AS [UnionAll1]

UNION ALL

SELECT

3 AS [C1]

FROM ( SELECTcast(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 rin query)

{

System.Console.WriteLine("{0},{1}",r["a"],r["wxwinter"]);

}

1,wxd

2,lzm

3,wxwinter

SELECT

1 AS [C1],

CASEWHEN ([UnionAll2].[C1]= 0) THEN 1 WHEN ([UnionAll2].[C1]= 1) THEN 2 ELSE 3 ENDAS [C2],

CASEWHEN ([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 ( SELECTcast(1 as bit) AS X ) AS [SingleRowTable1]

UNION ALL

SELECT

1 AS [C1]

FROM ( SELECTcast(1 as bit) AS X ) AS [SingleRowTable2])AS [UnionAll1]

UNIONALL

SELECT

2 AS [C1]

FROM ( SELECTcast(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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin 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 rin dbitemlist)

{

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

}