分享使用Entity Framework的一個坑：Include無效

来源:http://www.cnblogs.com/zuowj/archive/2016/01/05/5102548.html
-Advertisement-
Play Games

如果不想延遲載入，可以通過設置：context.Configuration.LazyLoadingEnabled = false;或查詢時加上AsNoTracking()方法即可。如果不想生成代理，可以通過設置：context.Configuration.ProxyCreationEnabled =...


如果不想延遲載入，可以通過設置：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語句是既簡潔又明瞭，符合我的意圖，從遇到的這個坑得到啟示，有時不要把問題複雜化，換個角度看問題或許能找到更好的解決辦法。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 微型 ORM 的第一篇 DapperLambda發佈
    引言:因為接觸過多個ORM，但使用的時候都遇到了各自的一些不夠理想的地方，從最早開始開始公司自己分裝的，到後面用EF,以及Dapper和DapperExtensions 到現在用的FluentData,就說說我自己的使用體驗,在這幾個相比之下，Dapper應該是最輕量級，而且性能也是最好的，但是相對...
  • 讀取枚舉特性小記(C#)
    讀取枚舉特性小記
  • 實現交換兩個變數值的第二種方法
  • mvc簡單execl導出
    直接上代碼：public static byte[] GetExecl(DataTable dt, List list) { var sbHtml = new StringBuilder(); sbHtml.Append(""); ...
  • 基於C#的MongoDB資料庫開發應用（1）--MongoDB資料庫的基礎知識和使用
    在花了不少時間研究學習了MongoDB資料庫的相關知識，以及利用C#對MongoDB資料庫的封裝、測試應用後，決定花一些時間來總結一下最近的研究心得，把這個資料庫的應用單獨作為一個系列來介紹，希望從各個方面來總結並記錄一下這個新型、看似神秘的資料庫使用過程。本文是這個系列的開篇，主要介紹一些Mong...
  • 解決.net定時器在iis7上不執行問題
    今天第一次在博客園發帖，以前一直在潛水，在這裡也是學了不少東西。感謝各位園友廢話不多說，這也是我工作中遇到的問題：protected void Application_Start(object sender, EventArgs e) { // 創建一個計時器，單...
  • WPF:DataGrid 自動生成行號
    下麵給大家分享一種通過 DataGridRowHeader 自動生成 DataGrid 數據行行號的方式。只需一個 ValueConverter 就能搞定。值轉換器 1 class AutoNumberValueConverter : IMultiValueConverter 2 ...
  • 坎坷路：ASP.NET 5 Identity 身份驗證（上集）
    之所以為上集，是因為我並沒有解決這個問題，寫這篇博文的目的是紀錄一下我所遇到的問題，以免自己忘記，其實已經忘了差不多了，寫的過程也是自己回顧的過程，並且之前收集有關 ASP.NET 5 身份驗證的書簽已經太多了，所以必須記錄下來。
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類