ADO.NET Entity Framework(4)ObjectQuery
目录
1 ObjectQuery<T>数据加载方式 1
1.1 访问方式 1
1.2 Context.CreateQuery<T>() 1
1.3 ObjectQuery<DbDataRecord> 2
1.4 ObjectQuery<简单类型> 3
2 Execute方法与ObjectResult 3
2.1 Execute方法 3
2.2 ObjectResult<T> 结果集 4
3 类型转换 4
3.1 OfType(TResultType) 4
4 Linq方法 5
4.1 All 5
4.2 Any 5
4.3 Take 5
4.4 Skip 5
4.5 First 6
4.6 FirstOrDefault 6
4.7 Where 6
4.8 Distinct 6
4.9 OrderBy,OrderByDescending 7
4.10 ThenBy,ThenByDescending 7
4.11 Average,Sum 7
4.12 Max,Min 7
4.13 Count,LongCount 8
4.14 Concat 8
4.15 Union 8
4.16 UnionAll 9
4.17 Except 10
4.18 Intersect 11
4.19 Select 11
4.20 GroupBy 11
4.21 Join 13
4.22 GroupJoin 14
5 无效的Linq方法 15
5.1 Aggregate 15
5.2 TakeWhile 15
5.3 SkipWhile 15
5.4 Reverse 16
5.5 Last,LastOrDefault 16
5.6 Single,SingleOrDefault 16
5.7 Contains 17
5.8 Distinct 17
5.9 ElementAt,ElementAtOrDefault 17
5.10 DefaultIfEmpty 17
5.11 SelectMany 18
5.12 SequenceEqual 19
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);
}
//print
/*
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);
}
//print
/*
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
本文作者:
«
»