ADO.NET Entity Framework学习笔记 ESQL查询语句
比起 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 r in 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 r in 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 r in 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 n in 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 n in 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 n in query) { Console.WriteLine(n); }
/* print: 3 */ |
|
string esql = "select value it.ItemID 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 = "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 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 r in 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 r in 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 r in 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 r in 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] ORDER BY [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 r in 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 r in query) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);
} |
|
SELECT TOP (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, params ObjectParameter[] parameters); |
|
myContext context = new myContext(); ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue", "5"); foreach (var r in 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 (ORDER BY [Extent1].[ItemValue] ASC) AS [row_number] FROM [dbo].[DBItemList] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 5 ORDER BY [Extent1].[ItemValue] ASC |
分页 Skip Top
|
Skip与Top一起使用 |
|
myContext context = new myContext(); ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue", "5").Top("3"); ; foreach (var r in query) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);
} |
|
SELECT TOP (3) [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 (ORDER BY [Extent1].[ItemValue] ASC) AS [row_number] FROM [dbo].[DBItemList] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 5 ORDER BY [Extent1].[ItemValue] ASC |
GroupBy(keys,projection)
|
分组 keys: GROUP BY的字段 projection : Select 的内容 ObjectQuery<DbDataRecord> GroupBy(string keys, string projection, params ObjectParameter[] parameters); |
|
myContext context = new myContext(); ObjectQuery<DbDataRecord> query = context.DBItemList.GroupBy("it.ItemID", "it.ItemID,Sum(it.ItemValue) as ValueSum"); foreach (var r in 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 dbitem in r) { foreach (var dbitemlist in dbitem.DBItemList) { Console.WriteLine("{0},{1}", dbitemlist.NameID, dbitemlist.ItemValue); } } |
|
效果与下例相同 myContext context = new myContext();
var r = context.DBItem; foreach (var dbitem in r) { dbitem.DBItemList.Load(); foreach (var dbitemlist in dbitem.DBItemList) { Console.WriteLine("{0},{1}", dbitemlist.NameID, dbitemlist.ItemValue); } } |
esql注释,成员访问,分行
|
注释 |
-- |
|
成员访问 |
. |
|
分行 |
; |
esql运算符
算术运算符
|
加 |
+ |
|
减 |
- |
|
乘 |
* |
|
除 |
/ |
|
模 |
% |
|
负 |
- |
比效运算符
|
等于 |
= |
|
大于 |
> |
|
大于等于 |
>= |
|
空判断 |
IS NOT NULL IS NULL |
|
小于 |
< |
|
小天等于 |
<= |
|
不等于 |
!= <> |
|
字符比效 |
LIKE '' NOT LIKE '' |
|
% : _ : [ ] : [^] : |
逻辑运算符
|
与 |
AND && |
|
非 |
NOT ! |
|
或 |
OR || |
区间
|
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 r in 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 r in query) { Console.WriteLine("{0},{1}", r.ItemID, r.ItemValue); } | |
|
IN 在集合中 |
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 r in 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 r in 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 r in 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 r in 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 r in 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 r in 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 r in 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 r in 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] LEFT OUTER 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 r in query) { Console.WriteLine(r); } //print: True |
|
SELECT CASE WHEN ( 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(1 as 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(0 as 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 r in 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 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); } |
声明：此文转载自 成长的足迹.NET
