现在的位置: 首页 > 综合 > 正文

ADO.NET Entity Framework(4)ObjectQuery

2013年02月19日 ⁄ 综合 ⁄ 共 7599字 ⁄ 字号 小 中 大 ⁄ 评论关闭

目录

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

返回
【上篇】
【下篇】

作者:

抱歉!评论已关闭.

返回首页

Copyright © 2013-2018 学步园  保留所有权利.
软文销售 QQ客服：2265327166 点击这里给我发消息（其他合作也可洽谈）