有时候我们需要对多张数据表进行一次性的Select操作。目前就我所知的操作方法有三种:1.直接写表达式;2.使用LoadOption选项;3.使用Join语句。那么,这三种语句用哪一种好呢?让我们来做一个小小的研究。。呱呱。。
1:1关系的多表操作
表结构如下
首先是测试取全记录的情况(也就是取所有字段)
直接写表达式
var user
=
context.Users.Where(p
=>
p.UserID
==
10300
).Select(p
=>
new
{p, p.UserData}
);
SELECT
[
t0
]
.
[
UserID
]
,
[
t0
]
.
[
Email
]
,
[
t0
]
.
[
NickName
]
,
[
t2
]
.
[
test
]
,
[
t2
]
.
[
UserID
]
AS
[
UserID2
]
,
[
t2
]
.
[
MyInfo
]
,
[
t2
]
.
[
MyFriends
]
FROM
[
dbo
]
.
[
Users
]
AS
[
t0
]
LEFT
OUTER
JOIN
(
SELECT
1
AS
[
test
]
,
[
t1
]
.
[
UserID
]
,
[
t1
]
.
[
MyInfo
]
,
[
t1
]
.
[
MyFriends
]
FROM
[
dbo
]
.
[
UserData
]
AS
[
t1
]
)
AS
[
t2
]
ON
[
t2
]
.
[
UserID
]
=
[
t0
]
.
[
UserID
]
WHERE
[
t0
]
.
[
UserID
]
=
10300
使用LoadOption选项
var option
=
new
DataLoadOptions();
option.LoadWith < User > (p => p.UserData);
context.LoadOptions = option;
var user = context.Users.Where(p => p.UserID == 10300 ).Single();
var userdata = user.UserData;
option.LoadWith < User > (p => p.UserData);
context.LoadOptions = option;
var user = context.Users.Where(p => p.UserID == 10300 ).Single();
var userdata = user.UserData;
虽然看上去取了2次数据,但是因为指定了LoadOption选项,所以也是一次性从数据库取出来的。
SELECT
[
t0
]
.
[
UserID
]
,
[
t0
]
.
[
Email
]
,
[
t0
]
.
[
NickName
]
,
[
t2
]
.
[
test
]
,
[
t2
]
.
[
UserID
]
AS
[
UserID2
]
,
[
t2
]
.
[
MyInfo
]
,
[
t2
]
.
[
MyFriends
]
FROM
[
dbo
]
.
[
Users
]
AS
[
t0
]
LEFT OUTER JOIN ( SELECT 1 AS [ test ] , [ t1 ] . [ UserID ] , [ t1 ] . [ MyInfo ] , [ t1 ] . [ MyFriends ] FROM [ dbo ] . [ UserData ] AS [ t1 ] ) AS [ t2 ] ON [ t2 ] . [ UserID ] = [ t0 ] . [ UserID ]
WHERE [ t0 ] . [ UserID ] = 10300
LEFT OUTER JOIN ( SELECT 1 AS [ test ] , [ t1 ] . [ UserID ] , [ t1 ] . [ MyInfo ] , [ t1 ] . [ MyFriends ] FROM [ dbo ] . [ UserData ] AS [ t1 ] ) AS [ t2 ] ON [ t2 ] . [ UserID ] = [ t0 ] . [ UserID ]
WHERE [ t0 ] . [ UserID ] = 10300
使用Join语句
var user
=
context.Users.Where(p
=>
p.UserID
==
10300
).Join(context.UserDatas, p
=>
p.UserID, o
=>
o.UserID, (o, p)
=>
new
{ o, p }
);
SELECT
[
t0
]
.
[
UserID
]
,
[
t0
]
.
[
Email
]
,
[
t0
]
.
[
NickName
]
,
[
t1
]
.
[
UserID
]
AS
[
UserID2
]
,
[
t1
]
.
[
MyInfo
]
,
[
t1
]
.
[
MyFriends
]
FROM
[
dbo
]
.
[
Users
]
AS
[
t0
]
INNER
JOIN
[
dbo
]
.
[
UserData
]
AS
[
t1
]
ON
[
t0
]
.
[
UserID
]
=
[
t1
]
.
[
UserID
]
WHERE
[
t0
]
.
[
UserID
]
=
10300
但是要注意的一点是,如果用了 .Select(p => new {p, p.UserData}) 这种代码,你在使用实体的时候会稍微麻烦一点,要采用 user.p.UserID 这种形式,非常不爽。可以这样改进一下,写成 .Select(p => new {User = p, p.UserData}) 这种形式,这样的话可以使用 user.User.UserID 来访问。如果想直接用 user.UserID 来访问的话,就必须在 new 的时候为每一个属性赋名称,如果数据表字段很多的话,那我只能恭喜你中奖啦。
如果要我评分的话,我会这样评
| SQL代码 | C# Select代码 | 返回实例的易用性 |
直接写表达式 | 低 | 高 | 中 |
使用LoadOption | 低 | 低 | 高 |
使用Join语句 | 高 | 中 | 中 |
如果只是要取部分字段的话,我们必须在Select的时候指定要取的字段,这样的话,在返回实例的易用性上,三种方式将是一样的效果。
| SQL代码 | C# Select代码 | 返回实例的易用性 |
直接写表达式 | 低 | 高 | 高 |
使用LoadOption | 低 | 低 | 高 |
使用Join语句 | 高 | 中 | 高 |
至于具体怎么用就看各位喜好了…………..当然,别忘了我们还有宇宙超级无敌的“视图”可以使用...........
To Be Continued….