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<实体>

ADO.NET Entity Framework(4)ObjectQuery

访问方式

ADO.NET Entity Framework(4)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>对象

ADO.NET Entity Framework(4)ObjectQuery

类型转换 OfType(TResultType)

ObjectQuery<TResultType> OfType<TResultType>();

ADO.NET Entity Framework(4)ObjectQuery

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>>

ADO.NET Entity Framework(4)ObjectQuery

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

ADO.NET Entity Framework(4)ObjectQuery

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]

ADO.NET Entity Framework(4)ObjectQuery

无效的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);
}

ADO.NET Entity Framework(4)ObjectQuery

对应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

posted on 2010-04-26 10:49  l1b2q31  阅读(320)  评论(0编辑  收藏  举报

导航