ADO.NET Entity Framework(5)esql
目录
1.2 ObjectQuery<DbDataRecord> 1
4.8 GroupBy(keys,projection) 6
比起 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();
|