Sharing a pitfall for using Entity Framework: Invalid Include, entityframework

Sharing a pitfall for using Entity Framework: Invalid Include, entityframework

If you do not want to delay loading, you can set context. Configuration. LazyLoadingEnabled = false; or add the AsNoTracking () method to the query.

If you do not want to generate a proxy, you can set context. Configuration. ProxyCreationEnabled = false;

Note: When context. Configuration. ProxyCreationEnabled is set to false, delayed loading does not take effect. The principle is very simple because there is no proxy.

When delayed loading is disabled, the associated attributes (navigation attributes) are not instantiated. to instantiate this attribute, you must use the Include method, for explicit loading (also known as hunger loading), see my previous article: http://www.cnblogs.com/zuowj/p/4514230.html.

With the above knowledge, it is easy to load all the content containing the value of the Association attribute at a time and do not generate a proxy object. The statements in my project are as follows:

            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());

The code briefly describes that TA_CWTransferRequestInfo has an association attribute TA_CWBankAccountInfo. I want to group and store the information based on TA_CWBankAccountInfo.bkcode. Finally, I need to use the token attribute information. I thought there was no problem, however, in actual use, an error is reported: the binding to the null reference cannot be performed during runtime. During DEBUG, TA_CWTransferRequestInfo.TA_CWBankAccountInfo = null is found, which is a bit confusing. Obviously, I used Include, why is it not loaded? The SQL statements generated by the above-mentioned LINQ statements are as follows:

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

I am also drunk when I see this SQL statement, which is totally different from my intention. From the preceding SQL statement, it can be seen that, although [TA_CWBankAccountInfo] is used in association, but at last, only the [TA_CWTransferRequestInfo] field is found. Of course, the associated TA_CWBankAccountInfo attribute cannot be instantiated. Finally, we can draw a conclusion,When GroupBy + ToDictionary is used, the Include method is invalid.

In view of the above conclusion, I made a slight adjustment to the above statement and successfully passed the test. The modified statement:

            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());

Are there any differences? I just added the ToList () method after the Include operation. The purpose is to first query the Qualified Data (including the associated data) from the database ), then perform the GroupBy operation locally. You can check the generated SQL statement:

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]

This SQL statement is concise and clear. It is in line with my intention and inspired by this pitfall. Sometimes we should not complicate the problem. from another perspective, we may find a better solution to the problem.