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