分享使用Entity Framework的一个坑：Include无效

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

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

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

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

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

            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语句是既简洁又明了，符合我的意图，从遇到的这个坑得到启示，有时不要把问题复杂化，换个角度看问题或许能找到更好的解决办法。