ADO.NET Entity Framework(4)ObjectQuery
目录
1.2 Context.CreateQuery<T>() 1
1.3 ObjectQuery<DbDataRecord> 2
4.9 OrderBy,OrderByDescending 7
4.10 ThenBy,ThenByDescending 7
5.9 ElementAt,ElementAtOrDefault 17
ObjectQuery<T>数据加载方式
1. ObjectQuery<T> 提供了一个管理[实体对像]集合
2. ObjectQuery<T>继承System.Data.Objects.ObjectQuery, ObjectQuery对ObjectContext进行了封装，
3.可以通过ObjectContext.CreateQuery<T>("esql")的方式创建ObjectQuery<T>
4.可以通过new ObjectQuery<T>(ObjectContext,"esql")的方式创建ObjectQuery<T>,跟据SQL字串的不同，会得到具体的ObjectQuery<值类型>，或ObjectQuery<DbDataRecord>或ObjectQuery<实体>
访问方式
Context.CreateQuery<T>()
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ;
provider=System.Data.SqlClient;
provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" ";
EntityConnection econ = new EntityConnection(econString);
ObjectContext context = new ObjectContext(econ);
context.DefaultContainerName = "myContext";
ObjectQuery<DBItem> queryTab = context.CreateQuery<DBItem>("DBItem");
foreach (var r in queryTab) { System.Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter); } |
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ;
provider=System.Data.SqlClient;
provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" "; EntityConnection econ = new EntityConnection(econString);
ObjectContext context = new ObjectContext(econ);
ObjectQuery<DBItem> queryTab = context.CreateQuery<DBItem>("select value it from myContext.DBItem as it where it.ItemID='a'");
foreach (var r in queryTab) { System.Console.WriteLine("{0},{1}",r.ItemID,r.ItemMatter); } |
ObjectQuery<DbDataRecord>
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ;
provider=System.Data.SqlClient;
provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" ";
EntityConnection econ = new EntityConnection(econString);
ObjectContext context = new ObjectContext(econ);
ObjectQuery<DbDataRecord> queryTab = new ObjectQuery<DbDataRecord>("select it.ItemID,it.ItemMatter from myContext.DBItem as it", context);
foreach (var r in queryTab) { System.Console.WriteLine("{0},{1}",r[0].ToString(),r[1].ToString()); } |
ObjectQuery<简单类型>
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ;
provider=System.Data.SqlClient;
provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" ";
EntityConnection econ = new EntityConnection(econString);
ObjectContext context = new ObjectContext(econ);
ObjectQuery<int> queryTab = new ObjectQuery<int>("select value Count(it.ItemID) from myContext.DBItem as it", context);
foreach (var r in queryTab) { System.Console.WriteLine("个数:{0}", r.ToString() ); } |
Execute方法与ObjectResult
Execute方法
string econString = @" metadata=res://*/myModel.csdl |res://*/myModel.ssdl |res://*/myModel.msl ;
provider=System.Data.SqlClient;
provider connection string="" Data Source=.; Initial Catalog=LingTestDB; Integrated Security=True; MultipleActiveResultSets=True; "" ";
EntityConnection econ = new EntityConnection(econString);
ObjectContext context = new ObjectContext(econ); context.DefaultContainerName = "myContext";
ObjectQuery<DBItem> queryTab = context.CreateQuery<DBItem>("DBItem");
ObjectResult<DBItem> resultTab = queryTab.Execute(MergeOption.NoTracking);
foreach (var r in resultTab) { System.Console.WriteLine("{0},{1}",r.ItemID,r.ItemMatter); } |
ObjectResult<T> 结果集
ObjectQuery<T>.Execute()方法返回ObjectResult<T>对象
类型转换
OfType(TResultType)
ObjectQuery<TResultType> OfType<TResultType>(); |
myContext context = new myContext(); ObjectQuery<DBItemEx> v = context.DBItem.OfType<DBItemEx>(); |
Linq方法
All
判断集合中是否所有元素都满足某一条件 |
myContext context = new myContext(); bool b= context.DBItemList.All(p => p.ItemValue >= 0); |
SELECT CASE WHEN ( NOT EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[DBItemList] AS [Extent1] WHERE ( NOT ([Extent1].[ItemValue] >= 1)) OR (CASE WHEN ([Extent1].[ItemValue] >= 0) THEN cast(1 as bit) WHEN ( NOT ([Extent1].[ItemValue] >= 0)) THEN cast(0 as bit) END IS NULL) )) THEN cast(1 as bit) WHEN ( EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[DBItemList] AS [Extent2] WHERE ( NOT ([Extent2].[ItemValue] >= 0)) OR (CASE WHEN ([Extent2].[ItemValue] >= 0) THEN cast(1 as bit) WHEN ( NOT ([Extent2].[ItemValue] >= 0)) THEN cast(0 as bit) END IS NULL) )) THEN cast(0 as bit) END AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] |
Any
判断集合中是否有元素满足某一条件 |
myContext context = new myContext(); bool b = context.DBItemList.Any(p => p.ItemValue == 4); |
SELECT CASE WHEN ( EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[DBItemList] AS [Extent1] WHERE 4 = [Extent1].[ItemValue] )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[DBItemList] AS [Extent2] WHERE 4 = [Extent2].[ItemValue] )) THEN cast(0 as bit) END AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] |
Take
获取集合的前n个元素 |
myContext context = new myContext(); IQueryable<DBItemList> list = context.DBItemList.Take(3); |
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
跳过集合的前n个元素, Linq To Entity 要求必须先OrderBy |
myContext context = new myContext(); IQueryable<DBItemList> list = context.DBItemList.OrderBy(p=>p.ItemValue).Skip(5); |
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 |
First
集合的第一个元素,集合中没有会报错, |
myContext context = new myContext();
DBItemList f1 = context.DBItemList.First();
DBItemList fi = context.DBItemList.First(p => p.ItemValue == 5);
|
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 5 = [Extent1].[ItemValue] |
FirstOrDefault
集合中的第一个元素,没有则返回类型默认值,对象型默认值为null |
myContext context = new myContext();
DBItemList fi = context.DBItemList.FirstOrDefault(p => p.ItemValue ==5);
if (fi != null) { Console.WriteLine(fi.ItemValue); } |
Where
用LinqExpressions为条件进行查询 |
myContext context = new myContext(); IQueryable<DBItemList> list= context.DBItemList.Where(p => p.ItemValue == 5); |
SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE 5 = [Extent1].[ItemValue] |
Distinct
过滤集合中的相同项
ObjectQuery<T> Distinct() |
myContext context = new myContext();
ObjectQuery<DbDataRecord> list = context.DBItemList.Select("it.ItemValue");
ObjectQuery<DbDataRecord> dlist= list.Distinct(); |
SELECT [Distinct1].[C1] AS [C1], [Distinct1].[ItemValue] AS [ItemValue] FROM ( SELECT DISTINCT [Extent1].[ItemValue] AS [ItemValue], 1 AS [C1] FROM [dbo].[DBItemList] AS [Extent1] ) AS [Distinct1] |
OrderBy,OrderByDescending
排序升,排序降 |
myContext context = new myContext(); IQueryable<DBItemList> list = context.DBItemList.OrderBy(p=>p.ItemValue); IQueryable<DBItemList> list = context.DBItemList.OrderByDescending(p=>p.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 |
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] DESC |
ThenBy,ThenByDescending
ThenBy,ThenByDescending 方法必须跟在 OrderBy 方法或对 ThenBy 方法的另一次调用之后 当用OrderBy,OrderByDescending指定主排序字段后,可用ThenBy呀ThenByDescending指定次排序字段 |
myContext context = new myContext(); IQueryable<DBItemList> query = context.DBItemList.OrderBy(p=>p.ItemValue).ThenByDescending(p => p.ItemID); 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 |
Average,Sum
平均值,求和 |
myContext context = new myContext(); double d = context.DBItemList.Average(p => p.ItemValue); double s = context.DBItemList.Sum(p => p.ItemValue); |
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT AVG( CAST( [Extent1].[ItemValue] AS float)) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1 |
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT SUM([Extent1].[ItemValue]) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1 |
Max,Min
集合最大值，最小值 |
myContext context = new myContext();
var mx = context.DBItemList.Max(p => p.ItemValue); var mi = context.DBItemList.Min(p => p.ItemValue); |
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT MAX([Extent1].[ItemValue]) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1 |
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT MIN([Extent1].[ItemValue]) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1 |
Count,LongCount
集合中的元素个数 |
myContext context = new myContext();
int n = context.DBItemList.Count();
int ni = context.DBItemList.Count(p => p.ItemValue == 5);
long ln = context.DBItemList.LongCount(); |
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT COUNT(cast(1 as bit)) AS [A1] FROM [dbo].[DBItemList] AS [Extent1] WHERE 5 = [Extent1].[ItemValue] ) AS [GroupBy1] ON 1 = 1 |
Concat
连接不同集合，不会自动过滤相同项,两个集合可以不是同一个Context |
myContext context1 = new myContext(); myContext context2 = new myContext();
IQueryable<DBItemList> list1 = context1.DBItemList.Where(p => p.ItemValue == 1); IQueryable<DBItemList> list2 = context1.DBItemList.Where(p => p.ItemValue == 2);
IQueryable<DBItemList> list = list1.Concat(list2); |
SELECT [UnionAll1].[AutoId] AS [C1], [UnionAll1].[NameID] AS [C2], [UnionAll1].[ItemID] AS [C3], [UnionAll1].[ItemValue] AS [C4] FROM (SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE 1 = [Extent1].[ItemValue] UNION ALL SELECT [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID], [Extent2].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent2] WHERE 2 = [Extent2].[ItemValue]) AS [UnionAll1] |
Union
连接不同集合，自动过滤相同项,两个集合要是同一个Context
ObjectQuery<T> Union(ObjectQuery<T> query) IQueryable<T> Union( IQueryable<T> query) IQueryable<T> Uniont( IQueryable<T> query,IEqualityComparer<T>) |
myContext context1 = new myContext();
IQueryable<DBItemList> query1 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "b");
IQueryable<DBItemList> query2 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "a");
IQueryable<DBItemList> v = query2.Union(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
myContext context1 = new myContext();
ObjectQuery<DBItemList> query1 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");
ObjectQuery<DBItemList> query2 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");
ObjectQuery<DBItemList> v = query2.Union(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT [Distinct1].[C1] AS [C1], [Distinct1].[C2] AS [C2], [Distinct1].[C3] AS [C3], [Distinct1].[C4] AS [C4] FROM ( SELECT DISTINCT [UnionAll1].[AutoId] AS [C1], [UnionAll1].[NameID] AS [C2], [UnionAll1].[ItemID] AS [C3], [UnionAll1].[ItemValue] AS [C4] FROM (SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE (N'c' = [Extent1].[ItemID]) OR (N'a' = [Extent1].[ItemID]) UNION ALL SELECT [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID], [Extent2].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent2] WHERE (N'c' = [Extent2].[ItemID]) OR (N'b' = [Extent2].[ItemID])) AS [UnionAll1] ) AS [Distinct1] |
UnionAll
两个集合的相同项都会返回,两个集合要是同一个Context ObjectQuery<T> UnionAll(ObjectQuery<T> query); |
myContext context1 = new myContext();
ObjectQuery<DBItemList> query1 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");
ObjectQuery<DBItemList> query2 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");
ObjectQuery<DBItemList> v = query2.UnionAll(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT [UnionAll1].[AutoId] AS [C1], [UnionAll1].[NameID] AS [C2], [UnionAll1].[ItemID] AS [C3], [UnionAll1].[ItemValue] AS [C4] 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] = 'a') UNION ALL 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] = 'c') OR ([Extent2].[ItemID] = 'b')) AS [UnionAll1] |
Except
从某集合中删除其与另一个集合中相同的项,两个集合要是同一个Context
ObjectQuery<T> Except(ObjectQuery<T> query) IQueryable<T> Except( IQueryable<T> query) IQueryable<T> Except( IQueryable<T> query,IEqualityComparer<T>) |
myContext context1 = new myContext();
IQueryable<DBItemList> query1 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "b");
IQueryable<DBItemList> query2 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "a");
IQueryable<DBItemList> v = query2.Except(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
myContext context1 = new myContext();
ObjectQuery<DBItemList> query1 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");
ObjectQuery<DBItemList> query2 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");
ObjectQuery<DBItemList> v = query2.Except(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT [Except1].[AutoId] AS [C1], [Except1].[NameID] AS [C2], [Except1].[ItemID] AS [C3], [Except1].[ItemValue] AS [C4] FROM (SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE (N'c' = [Extent1].[ItemID]) OR (N'a' = [Extent1].[ItemID]) EXCEPT SELECT [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID], [Extent2].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent2] WHERE (N'c' = [Extent2].[ItemID]) OR (N'b' = [Extent2].[ItemID])) AS [Except1] |
Intersect
获取不同集合的相同项（交集）,两个集合要是同一个Context
ObjectQuery<T> Intersect(ObjectQuery<T> query) IQueryable<T> Intersect( IQueryable<T> query) IQueryable<T> Intersect( IQueryable<T> query,IEqualityComparer<T>) |
myContext context1 = new myContext();
IQueryable<DBItemList> query1 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "b");
IQueryable<DBItemList> query2 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "a");
IQueryable<DBItemList> v = query2.Intersect(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
myContext context1 = new myContext();
ObjectQuery<DBItemList> query1 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");
ObjectQuery<DBItemList> query2 = context1.CreateQuery<DBItemList>("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");
ObjectQuery<DBItemList> v = query2.Intersect(query1);
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); } |
SELECT [Intersect1].[AutoId] AS [C1], [Intersect1].[NameID] AS [C2], [Intersect1].[ItemID] AS [C3], [Intersect1].[ItemValue] AS [C4] FROM (SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent1] WHERE (N'c' = [Extent1].[ItemID]) OR (N'a' = [Extent1].[ItemID]) INTERSECT SELECT [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID], [Extent2].[ItemValue] AS [ItemValue] FROM [dbo].[DBItemList] AS [Extent2] WHERE (N'c' = [Extent2].[ItemID]) OR (N'b' = [Extent2].[ItemID])) AS [Intersect1] |
Select
射影 |
myContext context = new myContext();
var list = context.DBItemList.Select(p => new {a= p.ItemValue,p.NameID }); |
SELECT 1 AS [C1], [Extent1].[ItemValue] AS [ItemValue], [Extent1].[NameID] AS [NameID] FROM [dbo].[DBItemList] AS [Extent1] |
GroupBy
分组,该方法分组结果集合
System.Collections.Generic.IEnumerable<System.Linq.IGrouping<TKey,TElement>>
myContext context = new myContext(); var query = context.DBItemList.GroupBy(p => p.ItemID); foreach (var g in query) { Console.WriteLine(g.Key);
foreach (var r in g) { Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue); }
} /* a 23,a,n01,4 24,a,n01,5 25,a,n02,2 26,a,n02,3 27,a,n02,6 28,a,n03,3 b 11,b,n03,5 14,b,n01,2 16,b,n01,1 c 5,c,n01,4 7,c,n01,5 9,c,n02,2 10,c,n02,3 12,c,n02,6 17,c,n03,3 */ |
SELECT [Project2].[ItemID] AS [ItemID], [Project2].[C1] AS [C1], [Project2].[C2] AS [C2], [Project2].[AutoId] AS [AutoId], [Project2].[NameID] AS [NameID], [Project2].[ItemID1] AS [ItemID1], [Project2].[ItemValue] AS [ItemValue] FROM ( SELECT [Distinct1].[ItemID] AS [ItemID], 1 AS [C1], [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID], [Extent2].[ItemID] AS [ItemID1], [Extent2].[ItemValue] AS [ItemValue], CASE WHEN ([Extent2].[AutoId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2] FROM (SELECT DISTINCT [Extent1].[ItemID] AS [ItemID] FROM [dbo].[DBItemList] AS [Extent1] ) AS [Distinct1] LEFT OUTER JOIN [dbo].[DBItemList] AS [Extent2] ON ([Extent2].[ItemID] = [Distinct1].[ItemID]) OR (([Extent2].[ItemID] IS NULL) AND ([Distinct1].[ItemID] IS NULL)) ) AS [Project2] ORDER BY [Project2].[ItemID] ASC, [Project2].[C2] ASC
|
Join
联合查询 |
myContext context1 = new myContext();
ObjectQuery<DBItem> query1 = context1.DBItem;
ObjectQuery<DBItemList> query2 = context1.DBItemList;
var v = query1.Join(query2, temp1 => temp1.ItemID, temp2 => temp2.ItemID, (temp1, temp2) => new { temp1.ItemID, temp1.ItemMatter, temp2.ItemValue, temp2.AutoId, temp2.NameID });
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3},{4}", r.AutoId, r.ItemID, r.NameID, r.ItemValue,r.ItemMatter); }
|
myContext context1 = new myContext();
ObjectQuery<DBItem> query1 = context1.DBItem;
ObjectQuery<DBItemList> query2 = context1.DBItemList;
var v = from temp1 in query1 join temp2 in query2 on temp1.ItemID equals temp2.ItemID select new { temp1.ItemID, temp1.ItemMatter, temp2.ItemValue, temp2.AutoId,temp2.NameID };
foreach (var r in v) { Console.WriteLine("{0},{1},{2},{3},{4}", r.AutoId, r.ItemID, r.NameID, r.ItemValue,r.ItemMatter); } /* 5,c,n01,4,this is c 7,c,n01,5,this is c 9,c,n02,2,this is c 10,c,n02,3,this is c 11,b,n03,5,this is b 12,c,n02,6,this is c 14,b,n01,2,this is b 16,b,n01,1,this is b 17,c,n03,3,this is c 23,a,n01,4,this is a 24,a,n01,5,this is a 25,a,n02,2,this is a 26,a,n02,3,this is a 27,a,n02,6,this is a 28,a,n03,3,this is a */ |
SELECT 1 AS [C1], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemMatter] AS [ItemMatter], [Extent2].[ItemValue] AS [ItemValue], [Extent2].[AutoId] AS [AutoId], [Extent2].[NameID] AS [NameID] FROM [dbo].[DBItem] AS [Extent1] INNER JOIN [dbo].[DBItemList] AS [Extent2] ON ([Extent1].[ItemID] = [Extent2].[ItemID]) OR (([Extent1].[ItemID] IS NULL) AND ([Extent2].[ItemID] IS NULL)) |
GroupJoin
myContext context1 = new myContext();
ObjectQuery<DBItem> query1 = context1.DBItem;
ObjectQuery<DBItemList> query2 = context1.DBItemList;
var v = query1.GroupJoin(query2, temp1 => temp1.ItemID, temp2 => temp2.ItemID, (temp1, temp2) => new { temp1.ItemID, temp1.ItemMatter, 个数 = temp2.Count() });
foreach (var r in v) { Console.WriteLine("{0},{1},{2}", r.ItemID, r.ItemMatter,r.个数); }
|
myContext context1 = new myContext();
ObjectQuery<DBItem> query1 = context1.DBItem;
ObjectQuery<DBItemList> query2 = context1.DBItemList;
var v = from temp1 in query1 join temp2 in query2 on temp1.ItemID equals temp2.ItemID into newtab select new { temp1.ItemID, temp1.ItemMatter, 个数 = newtab.Count() };
foreach (var r in v) { Console.WriteLine("{0},{1},{2}", r.ItemID, r.ItemMatter,r.个数); } /* a,this is a,6 b,this is b,3 c,this is c,6 */ |
SELECT 1 AS [C1], [Project1].[ItemID] AS [ItemID], [Project1].[ItemMatter] AS [ItemMatter], [Project1].[C1] AS [C2] FROM ( SELECT [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemMatter] AS [ItemMatter], (SELECT COUNT(cast(1 as bit)) AS [A1] FROM [dbo].[DBItemList] AS [Extent2] WHERE ([Extent1].[ItemID] = [Extent2].[ItemID]) OR (([Extent1].[ItemID] IS NULL) AND ([Extent2].[ItemID] IS NULL))) AS [C1] FROM [dbo].[DBItem] AS [Extent1] ) AS [Project1]
|
无效的Linq方法
可将ObjectQuery<T>转换为List<T>后使用 List的对应方法
Aggregate
据输入的表达式获取一个聚合值 |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
DBItemList r = list.Aggregate((x, y) => new DBItemList() { ItemValue = x.ItemValue + y.ItemValue });
Console.WriteLine("ItemValue合计为:{0}", r.ItemValue); //print: ItemValue合计为:54 |
TakeWhile
条件第一次不成立就跳出循环 |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
IEnumerable<DBItemList> v = list.TakeWhile(p => p.ItemValue >= 2);
foreach (var r in v) {
Console.WriteLine(r.ItemValue); } /* 4 5 2 3 5 6 2 */ |
SkipWhile
条件第一次不成立就失效，将后面的数据全取 |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
IEnumerable<DBItemList> v = list.SkipWhile(p => p.ItemValue >= 2);
foreach (var r in v) {
Console.WriteLine(r.ItemValue); } /* 1 3 4 5 2 3 6 3 */ |
Reverse
顺序返转 |
myContext context = new myContext();
IEnumerable<DBItemList> list = context.DBItemList.AsEnumerable();
IEnumerable<DBItemList> v = list.Reverse(); |
Last,LastOrDefault
集合的最后一个元素,集合中没有会报错, 集合中的最后一个元素,没有则返回类型默认值,对象型默认值为null |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
DBItemList l1 = list.Last();
DBItemList li = list.Last(p=>p.ItemValue==5);
DBItemList lid = list.LastOrDefault(p => p.ItemValue == 15);
if (lid != null) { Console.WriteLine(lid.ItemValue); } |
Single,SingleOrDefault
集合中符合条件的唯一元素,集合中没有会报错,集合中有两个以上会报错 集合中符合条件的唯一元素,集合中有两个以上会报错,集合中没有则返回类型默认值,对象型默认值为null |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
DBItemList di = list.Single(p=>p.ItemValue==5);
DBItemList did = list.SingleOrDefault(p => p.ItemValue == 15);
if (did != null) { Console.WriteLine(did.ItemValue); } |
Contains
判断集合中是否包含有某一元素 |
myContext context = new myContext(); List<DBItemList> list = context.DBItemList.ToList();
DBItemList r = new DBItemList(); r.ItemValue = 3;
bool b = list.Contains(r,new c()); |
class c : IEqualityComparer<DBItemList> { public bool Equals(DBItemList x, DBItemList y) { if (x.ItemValue == y.ItemValue) { return true; } else { return false; } }
public int GetHashCode(DBItemList obj) { return 0; } } |
Distinct
过滤集合中的相同项 |
myContext context = new myContext(); List<DBItemList> list = context.DBItemList.ToList(); IEnumerable<DBItemList> v = list.Distinct(new c()); |
class c : IEqualityComparer<DBItemList> { public bool Equals(DBItemList x, DBItemList y) { if (x.ItemValue == y.ItemValue) { return true; } else { return false; } }
public int GetHashCode(DBItemList obj) { return 0; } } |
ElementAt,ElementAtOrDefault
集合中指定索引的元素 集合中指定索引的元素,没有则返回类型默认值,对象型默认值为null |
myContext context = new myContext();
List<DBItemList> list = context.DBItemList.ToList();
DBItemList r = list.ElementAt(5);
DBItemList rd = list.ElementAtOrDefault(50); |
DefaultIfEmpty
如果集合是的无素为空(count==0),就向集合中插入一个默认元素 |
DBItemList r=new DBItemList(); r.ItemValue=100;
List<DBItemList> list = new List<DBItemList>(); var pp = list.DefaultIfEmpty(r); |
SelectMany
myContext context = new myContext(); List<DBItemList> query = context.DBItemList.ToList();
IEnumerable<string> ls=query.SelectMany(p => p.NameID.Split('0')); foreach (string r in ls) { Console.WriteLine(r); }
/* n 1 n 1 n 2 n 2 n 3 n 2 n 1 n 1 n 3 n 1 n 1 n 2 n 2 n 2 n 3 */ | |
Lambda表达式 | List<string> ls = new List<string>() { "wxd/1", "lzm/2", "wxwinter/3" }; var li = ls.SelectMany(p => p.Split('/')); foreach (var s in li) { Console.WriteLine(s); }
|
对应Select效果 | var ll = ls.Select(p => p.Split('/')); foreach (var s in ll) { foreach (var ss in s) { Console.WriteLine(ss); } } |
SequenceEqual
myContext context = new myContext(); List<DBItemList> list1 = context.DBItemList.Where(p => p.ItemID == "a" ).ToList(); List<DBItemList> list2 = context.DBItemList.Where(p => p.ItemID == "a").ToList(); bool b = list1.SequenceEqual(list2);
Console.WriteLine(b); //print:True |
