比起 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("[email protected]", 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 [email protected] or [email protected]"; 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 |
小于 | < |
小天等于 | <= |
不等于 | border-bottom: 0.5pt solid; padding-left: 7px; pa
相关解决方案
|