分享使用Entity Framework的一个坑：Include无效-白红宇

如果不想延迟加载，可以通过设置：context.Configuration.LazyLoadingEnabled = false;或查询时加上AsNoTracking()方法即可。

如果不想生成代理，可以通过设置：context.Configuration.ProxyCreationEnabled = false;

注意当context.Configuration.ProxyCreationEnabled = false;时延迟加载也就不生效，原理很简单，因为没有代理。

当禁用延迟加载后，关联属性（导航属性）不会被实例化，这时如果需要实例化该属性，则需要通过Include方法，意为显式加载（也有人称为饥饿加载），具体的用法也可参见我之前的文章：

好了有了上面知识的了解，我们想实现一次性加载所有内容包含关联属性的值，且不要生成代理对象，就很简单了，我项目中的语句如下：

                            
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语句如下：

                            
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方法无效。

鉴于上述结论，我将上述语句稍微作了一下调整，就成功通过测试了，更改后的语句：

                            
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语句：

                            
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 ，如需转载请自行联系原作者