如果不想延迟加载,可以通过设置:context.Configuration.LazyLoadingEnabled = false;或查询时加上AsNoTracking()方法即可。
如果不想生成代理,可以通过设置:context.Configuration.ProxyCreationEnabled = false;
注意当context.Configuration.ProxyCreationEnabled = false;时延迟加载也就不生效,原理很简单,因为没有代理。
当禁用延迟加载后,关联属性(导航属性)不会被实例化,这时如果需要实例化该属性,则需要通过Include方法,意为显式加载(也有人称为饥饿加载),具体的用法也可参见我之前的文章:http://www.cnblogs.com/zuowj/p/4514230.html
好了有了上面知识的了解,我们想实现一次性加载所有内容包含关联属性的值,且不要生成代理对象,就很简单了,我项目中的语句如下:
1
2
3
4
5
|
var
context =
new
LocalDbEntities();
context.Configuration.LazyLoadingEnabled =
false
;
context.Configuration.ProxyCreationEnabled =
false
;
result=context.Set<TA_CWTransferRequestInfo>().Where(t =>
true
).Include(t => t.TA_CWBankAccountInfo).GroupBy(t => t.TA_CWBankAccountInfo.bkcode)
.ToDictionary(gp => gp.Key, gp => gp.ToList());
|
代码简单说明一下,TA_CWTransferRequestInfo有一个关联属性TA_CWBankAccountInfo,我想实现依据TA_CWBankAccountInfo.bkcode来分组并存入Dictionary中,最后我需要用到TA_CWTransferRequestInfo.TA_CWBankAccountInfo属性的信息,原本以为没有问题,但实际使用时,却报错:无法对 null 引用执行运行时绑定,经DEBUG时发现TA_CWTransferRequestInfo.TA_CWBankAccountInfo=null,这就有点不解了,明明我使用了Include,为何没有加载呢?不解之余查看了一下上述LINQ生成的SQL语句如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
SELECT
[Project2].[C1]
AS
[C1],
[Project2].[bkcode]
AS
[bkcode],
[Project2].[C2]
AS
[C2],
[Project2].[id]
AS
[id],
[Project2].[fromactacn]
AS
[fromactacn],
[Project2].[toactacn]
AS
[toactacn],
[Project2].[toibkn]
AS
[toibkn],
[Project2].[toname]
AS
[toname],
[Project2].[toaddr]
AS
[toaddr],
[Project2].[tobknm]
AS
[tobknm],
[Project2].[tobkcode]
AS
[tobkcode],
[Project2].[trnamt]
AS
[trnamt],
[Project2].[trncur]
AS
[trncur],
[Project2].[priolv]
AS
[priolv],
[Project2].[furinfo]
AS
[furinfo],
[Project2].[trfdate]
AS
[trfdate],
[Project2].[trftime]
AS
[trftime],
[Project2].[comacn]
AS
[comacn],
[Project2].[field1]
AS
[field1],
[Project2].[field2]
AS
[field2],
[Project2].[field3]
AS
[field3],
[Project2].[field4]
AS
[field4],
[Project2].[field5]
AS
[field5],
[Project2].[field6]
AS
[field6],
[Project2].[field7]
AS
[field7],
[Project2].[field8]
AS
[field8],
[Project2].[processing]
AS
[processing],
[Project2].[transtype]
AS
[transtype],
[Project2].[trfmode]
AS
[trfmode],
[Project2].[createdt]
AS
[createdt],
[Project2].[lastupdatedt]
AS
[lastupdatedt],
[Project2].[lastrspid]
AS
[lastrspid],
[Project2].[rowversion]
AS
[rowversion],
[Project2].[lyd_guid]
AS
[lyd_guid]
FROM
(
SELECT
[Distinct1].[bkcode]
AS
[bkcode],
1
AS
[C1],
[Join2].[id]
AS
[id],
[Join2].[fromactacn]
AS
[fromactacn],
[Join2].[toactacn]
AS
[toactacn],
[Join2].[toibkn]
AS
[toibkn],
[Join2].[toname]
AS
[toname],
[Join2].[toaddr]
AS
[toaddr],
[Join2].[tobknm]
AS
[tobknm],
[Join2].[tobkcode]
AS
[tobkcode],
[Join2].[trnamt]
AS
[trnamt],
[Join2].[trncur1]
AS
[trncur],
[Join2].[priolv]
AS
[priolv],
[Join2].[furinfo]
AS
[furinfo],
[Join2].[trfdate]
AS
[trfdate],
[Join2].[trftime]
AS
[trftime],
[Join2].[comacn]
AS
[comacn],
[Join2].[field11]
AS
[field1],
[Join2].[field21]
AS
[field2],
[Join2].[field31]
AS
[field3],
[Join2].[field41]
AS
[field4],
[Join2].[field51]
AS
[field5],
[Join2].[field6]
AS
[field6],
[Join2].[field7]
AS
[field7],
[Join2].[field8]
AS
[field8],
[Join2].[processing]
AS
[processing],
[Join2].[transtype]
AS
[transtype],
[Join2].[trfmode]
AS
[trfmode],
[Join2].[createdt]
AS
[createdt],
[Join2].[lastupdatedt1]
AS
[lastupdatedt],
[Join2].[lastrspid]
AS
[lastrspid],
[Join2].[rowversion1]
AS
[rowversion],
[Join2].[lyd_guid]
AS
[lyd_guid],
CASE
WHEN
([Join2].[priolv]
IS
NULL
)
THEN
CAST
(
NULL
AS
int
)
ELSE
1
END
AS
[C2]
FROM
(
SELECT
DISTINCT
[Extent2].[bkcode]
AS
[bkcode]
FROM
[dbo].[TA_CWTransferRequestInfo]
AS
[Extent1]
INNER
JOIN
[dbo].[TA_CWBankAccountInfo]
AS
[Extent2]
ON
[Extent1].[fromactacn] = [Extent2].[actacn] )
AS
[Distinct1]
LEFT
OUTER
JOIN
(
SELECT
[Extent3].[id]
AS
[id], [Extent3].[fromactacn]
AS
[fromactacn], [Extent3].[toactacn]
AS
[toactacn], [Extent3].[toibkn]
AS
[toibkn], [Extent3].[toname]
AS
[toname], [Extent3].[toaddr]
AS
[toaddr], [Extent3].[tobknm]
AS
[tobknm], [Extent3].[tobkcode]
AS
[tobkcode], [Extent3].[trnamt]
AS
[trnamt], [Extent3].[trncur]
AS
[trncur1], [Extent3].[priolv]
AS
[priolv], [Extent3].[furinfo]
AS
[furinfo], [Extent3].[trfdate]
AS
[trfdate], [Extent3].[trftime]
AS
[trftime], [Extent3].[comacn]
AS
[comacn], [Extent3].[field1]
AS
[field11], [Extent3].[field2]
AS
[field21], [Extent3].[field3]
AS
[field31], [Extent3].[field4]
AS
[field41], [Extent3].[field5]
AS
[field51], [Extent3].[field6]
AS
[field6], [Extent3].[field7]
AS
[field7], [Extent3].[field8]
AS
[field8], [Extent3].[processing]
AS
[processing], [Extent3].[transtype]
AS
[transtype], [Extent3].[trfmode]
AS
[trfmode], [Extent3].[createdt]
AS
[createdt], [Extent3].[lastupdatedt]
AS
[lastupdatedt1], [Extent3].[lastrspid]
AS
[lastrspid], [Extent3].[rowversion]
AS
[rowversion1], [Extent3].[lyd_guid]
AS
[lyd_guid], [Extent4].[bkcode]
AS
[bkcode]
FROM
[dbo].[TA_CWTransferRequestInfo]
AS
[Extent3]
INNER
JOIN
[dbo].[TA_CWBankAccountInfo]
AS
[Extent4]
ON
[Extent3].[fromactacn] = [Extent4].[actacn] )
AS
[Join2]
ON
([Distinct1].[bkcode] = [Join2].[bkcode])
OR
(1 = 0)
)
AS
[Project2]
ORDER
BY
[Project2].[bkcode]
ASC
, [Project2].[C2]
ASC
|
看到这个SQL语句我也是醉了,与我的本意完全不同,从上面的SQL语句可以看出来:它虽然关联时有用到[TA_CWBankAccountInfo],但最后只查出[TA_CWTransferRequestInfo]的字段,当然也就无法实例化关联的TA_CWBankAccountInfo属性了,最后得出结论,当使用GroupBy+ToDictionary时,Include方法无效。
鉴于上述结论,我将上述语句稍微作了一下调整,就成功通过测试了,更改后的语句:
1
2
3
4
5
|
var
context =
new
LocalDbEntities();
context.Configuration.LazyLoadingEnabled =
false
;
context.Configuration.ProxyCreationEnabled =
false
;
return
context.Set<TA_CWTransferRequestInfo>().Where(t =>
true
).Include(t => t.TA_CWBankAccountInfo).ToList().GroupBy(t => t.TA_CWBankAccountInfo.bkcode)
.ToDictionary(gp => gp.Key, gp => gp.ToList());
|
发现区别了没有?我只是在Include后加了一个ToList()方法就可以了,目的是先从数据库查询出符合条件的数据(包含关联的数据),然后再在本地进行GroupBy操作,可以看一下生成的SQL语句:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
SELECT
[Extent1].[priolv]
AS
[priolv],
[Extent1].[id]
AS
[id],
[Extent1].[fromactacn]
AS
[fromactacn],
[Extent1].[toactacn]
AS
[toactacn],
[Extent1].[toibkn]
AS
[toibkn],
[Extent1].[toname]
AS
[toname],
[Extent1].[toaddr]
AS
[toaddr],
[Extent1].[tobknm]
AS
[tobknm],
[Extent1].[tobkcode]
AS
[tobkcode],
[Extent1].[trnamt]
AS
[trnamt],
[Extent1].[trncur]
AS
[trncur],
[Extent1].[furinfo]
AS
[furinfo],
[Extent1].[trfdate]
AS
[trfdate],
[Extent1].[trftime]
AS
[trftime],
[Extent1].[comacn]
AS
[comacn],
[Extent1].[field1]
AS
[field1],
[Extent1].[field2]
AS
[field2],
[Extent1].[field3]
AS
[field3],
[Extent1].[field4]
AS
[field4],
[Extent1].[field5]
AS
[field5],
[Extent1].[field6]
AS
[field6],
[Extent1].[field7]
AS
[field7],
[Extent1].[field8]
AS
[field8],
[Extent1].[processing]
AS
[processing],
[Extent1].[transtype]
AS
[transtype],
[Extent1].[trfmode]
AS
[trfmode],
[Extent1].[createdt]
AS
[createdt],
[Extent1].[lastupdatedt]
AS
[lastupdatedt],
[Extent1].[lastrspid]
AS
[lastrspid],
[Extent1].[rowversion]
AS
[rowversion],
[Extent1].[lyd_guid]
AS
[lyd_guid],
[Extent2].[actacn]
AS
[actacn],
[Extent2].[ibknum]
AS
[ibknum],
[Extent2].[actnam]
AS
[actnam],
[Extent2].[bknm]
AS
[bknm],
[Extent2].[bkcode]
AS
[bkcode],
[Extent2].[addr]
AS
[addr],
[Extent2].[actacnas]
AS
[actacnas],
[Extent2].[trncur]
AS
[trncur1],
[Extent2].[field1]
AS
[field11],
[Extent2].[field2]
AS
[field21],
[Extent2].[field3]
AS
[field31],
[Extent2].[field4]
AS
[field41],
[Extent2].[field5]
AS
[field51],
[Extent2].[lastupdatedt]
AS
[lastupdatedt1],
[Extent2].[rowversion]
AS
[rowversion1]
FROM
[dbo].[TA_CWTransferRequestInfo]
AS
[Extent1]
INNER
JOIN
[dbo].[TA_CWBankAccountInfo]
AS
[Extent2]
ON
[Extent1].[fromactacn] = [Extent2].[actacn]
|
这个SQL语句是既简洁又明了,符合我的意图,从遇到的这个坑得到启示,有时不要把问题复杂化,换个角度看问题或许能找到更好的解决办法。
本文转自 梦在旅途 博客园博客,原文链接:http://www.cnblogs.com/zuowj/p/5102548.html ,如需转载请自行联系原作者