ADO.NET Entity Framework学习笔记(4)ObjectQuery对象

ObjectQuery<T>数据加载方式

 

1. ObjectQuery<T> 提供了一个管理[实体对像]集合
2. ObjectQuery<T>继承System.Data.Objects.ObjectQuery, ObjectQueryObjectContext进行了封装,
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

 

ejiyuan
关注 - 0
粉丝 - 0

posted on 2010-04-26 11:52  l1b2q31  阅读(259)  评论(0编辑  收藏  举报

导航