快捷搜索:

ADO.NET Entity Framework(4)ObjectQuery

目录

1  ObjectQuery数据加载要领  1

1.1  造访要领  1

1.2  Context.CreateQuery()  1

1.3  ObjectQuery  2

1.4  ObjectQuery  3

2  Execute措施与ObjectResult  3

2.1  Execute措施  3

2.2  ObjectResult 结果集  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数据加载要领

1. ObjectQuery 供给了一个治理[实体对像]聚拢

2. ObjectQuery承袭System.Data.Objects.ObjectQuery, ObjectQuery对ObjectContext进行了封装,

3.可以经由过程ObjectContext.CreateQuery("esql")的要领创建ObjectQuery

4.可以经由过程new ObjectQuery(ObjectContext,"esql")的要领创建ObjectQuery,跟据SQL字串的不合,会获得详细的ObjectQuery,或ObjectQuery或ObjectQuery

造访要领

Context.CreateQuery()

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 queryTab = context.CreateQuery("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 queryTab = context.CreateQuery("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

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 queryTab = new ObjectQuery("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 queryTab = new ObjectQuery("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 queryTab = context.CreateQuery("DBItem");

ObjectResult resultTab = queryTab.Execute(MergeOption.NoTracking);

foreach (var r in resultTab)

{

System.Console.WriteLine("{0},{1}",r.ItemID,r.ItemMatter);

}

ObjectResult 结果集

ObjectQuery.Execute()措施返回ObjectResult工具

类型转换 OfType(TResultType)

ObjectQuery

OfType

();

myContext context = new myContext();

ObjectQuery v = context.DBItem.OfType();

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 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 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 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 Distinct()

myContext context = new myContext();

ObjectQuery list = context.DBItemList.Select("it.ItemValue");

ObjectQuery 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 list = context.DBItemList.OrderBy(p=>p.ItemValue);

IQueryable 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 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 list1 = context1.DBItemList.Where(p => p.ItemValue == 1);

IQueryable list2 = context1.DBItemList.Where(p => p.ItemValue == 2);

IQueryable 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 Union(ObjectQuery query)

IQueryable Union( IQueryable query)

IQueryable Uniont( IQueryable query,IEqualityComparer)

myContext context1 = new myContext();

IQueryable query1 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "b");

IQueryable query2 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "a");

IQueryable 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 query1 = context1.CreateQuery("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");

ObjectQuery query2 = context1.CreateQuery("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");

ObjectQuery 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 UnionAll(ObjectQuery query);

myContext context1 = new myContext();

ObjectQuery query1 = context1.CreateQuery("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");

ObjectQuery query2 = context1.CreateQuery("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");

ObjectQuery 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 Except(ObjectQuery query)

IQueryable Except( IQueryable query)

IQueryable Except( IQueryable query,IEqualityComparer)

myContext context1 = new myContext();

IQueryable query1 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "b");

IQueryable query2 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "a");

IQueryable 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 query1 = context1.CreateQuery("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");

ObjectQuery query2 = context1.CreateQuery("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");

ObjectQuery 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 Intersect(ObjectQuery query)

IQueryable Intersect( IQueryable query)

IQueryable Intersect( IQueryable query,IEqualityComparer)

myContext context1 = new myContext();

IQueryable query1 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "b");

IQueryable query2 = context1.DBItemList.Where(p => p.ItemID == "c" || p.ItemID == "a");

IQueryable 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 query1 = context1.CreateQuery("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'b' ");

ObjectQuery query2 = context1.CreateQuery("select value it from myContext.DBItemList as it where it.ItemID == 'c' || it.ItemID == 'a' ");

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

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 query1 = context1.DBItem;

ObjectQuery 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 query1 = context1.DBItem;

ObjectQuery 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 query1 = context1.DBItem;

ObjectQuery 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 query1 = context1.DBItem;

ObjectQuery 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转换为List后应用 List的对应措施

Aggregate

据输入的表达式获取一个聚合值

myContext context = new myContext();

List 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 list = context.DBItemList.ToList();

IEnumerable 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 list = context.DBItemList.ToList();

IEnumerable 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 list = context.DBItemList.AsEnumerable();

IEnumerable v = list.Reverse();

Last,LastOrDefault

聚拢的着末一个元素,聚拢中没有会报错,

聚拢中的着末一个元素,没有则返回类型默认值,工具型默认值为null

myContext context = new myContext();

List 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 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 list = context.DBItemList.ToList();

DBItemList r = new DBItemList();

r.ItemValue = 3;

bool b = list.Contains(r,new c());

class c : IEqualityComparer

{

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 list = context.DBItemList.ToList();

IEnumerable v = list.Distinct(new c());

class c : IEqualityComparer

{

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 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 list = new List();

var pp = list.DefaultIfEmpty(r);

SelectMany

myContext context = new myContext();

List query = context.DBItemList.ToList();

IEnumerable 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 ls = new List() { "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 list1 = context.DBItemList.Where(p => p.ItemID == "a" ).ToList();

List list2 = context.DBItemList.Where(p => p.ItemID == "a").ToList();

bool b = list1.SequenceEqual(list2);

Console.WriteLine(b);

//print:True

您可能还会对下面的文章感兴趣: