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

}

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

}

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

}

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

}

ObjectQuery<TResultType> OfType<TResultType>();

?

myContext context = new myContext();

ObjectQuery<DBItemEx> v = context.DBItem.OfType<DBItemEx>();

判斷集合中是否所有元素都滿足某一條件

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]

判斷集合中是否有元素滿足某一條件

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]

獲取集合的前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]

跳過集合的前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

集合的第一個元素,集合中沒有會報錯,

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]

集合中的第一個元素,沒有則返回類型默認值,對象型默認值為null

myContext context = new myContext();

?

DBItemList fi = context.DBItemList.FirstOrDefault(p => p.ItemValue ==5);

?

if (fi != null)

{

Console.WriteLine(fi.ItemValue);

}

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

過濾集合中的相同項

?

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]

排序升,排序降

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 方法必須跟在 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

平均值,求和

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

集合最大值，最小值

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

集合中的元素個數

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

連接不同集合，不會自動過濾相同項,兩個集合可以不是同一個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]

連接不同集合，自動過濾相同項,兩個集合要是同一個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]

兩個集合的相同項都會返回,兩個集合要是同一個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]

從某集合中刪除其與另一個集合中相同的項,兩個集合要是同一個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]

獲取不同集合的相同項（交集）,兩個集合要是同一個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]

射影

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]

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

?

聯合查詢

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

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]

據輸入的表達式獲取一個聚合值

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

條件第一次不成立就跳出循環

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

*/

條件第一次不成立就失效，將后面的數據全取

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

*/

順序返轉

myContext context = new myContext();

?

IEnumerable<DBItemList> list = context.DBItemList.AsEnumerable();

?

IEnumerable<DBItemList> v = list.Reverse();

集合的最后一個元素,集合中沒有會報錯,

集合中的最后一個元素,沒有則返回類型默認值,對象型默認值為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);

}

集合中符合條件的唯一元素,集合中沒有會報錯,集合中有兩個以上會報錯

集合中符合條件的唯一元素,集合中有兩個以上會報錯,集合中沒有則返回類型默認值,對象型默認值為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);

}

判斷集合中是否包含有某一元素

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;

}

}

過濾集合中的相同項

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;

}

}

集合中指定索引的元素

集合中指定索引的元素,沒有則返回類型默認值,對象型默認值為null

myContext context = new myContext();

?

List<DBItemList> list = context.DBItemList.ToList();

?

DBItemList r = list.ElementAt(5);

?

DBItemList rd = list.ElementAtOrDefault(50);

如果集合是的無素為空(count==0),就向集合中插入一個默認元素

DBItemList r=new DBItemList();

r.ItemValue=100;

?

List<DBItemList> list = new List<DBItemList>();

var pp = list.DefaultIfEmpty(r);

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

}

}

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