Entity Framework Code First實體關聯資料載入

libingql發表於2013-10-21
Framework

　　在專案過程中，兩個實體資料之間在往往並非完全獨立的，而是存在一定的關聯關係，如一對一、一對多及多對多等關聯。存在關聯關係的實體，經常根據一個實體的例項來查詢獲取與之關聯的另外實體的例項。

　　Entity Framework常用處理資料關聯載入的方式有3種：延遲載入（Lazy Loading）、貪婪載入（Eager Loading）以及顯示載入（Explicit Loading）。

　　1、延遲載入（Lazy Loading）

　　延遲載入是專案應用中常見的方式，Entity Framework在需要時可以自動為一個實體的例項獲取關聯的資料。

　　Entity Framework自動延遲載入需要滿足的條件：

　　1>、POCO類必須是public而非sealed；

　　2>、集合屬性必須的Virtual修飾的，這樣Entity Framework才能Override以包含延遲載入的邏輯。

　　示例：

　　檔案類Province.cs：

using System;
using System.Collections.Generic;

namespace Portal.Models
{
    public class Province
    {
        public Province()
        {
            this.Cities = new List<City>();
        }

        public int ProvinceID { get; set; }
        public string ProvinceNo { get; set; }
        public string ProvinceName { get; set; }
        public virtual ICollection<City> Cities { get; set; }
    }
}
View Code

　　檔案類City.cs：

using System;
using System.Collections.Generic;

namespace Portal.Models
{
    public class City
    {
        public int CityID { get; set; }
        public Nullable<int> ProvinceID { get; set; }
        public string CityNo { get; set; }
        public string CityName { get; set; }
        public virtual Province Province { get; set; }
    }
}
View Code

　　檔案類Program.cs：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Entity;

using Portal.Models;

namespace Portal
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new PortalContext())
            {
                var province = ctx.Provinces.Find(3);

                foreach (var city in province.Cities)
                {
                    Console.WriteLine(city.CityName);
                }
            }
        }
    }
}
View Code

　　以上程式碼在執行之後，執行了兩條SQL語句，分別用於讀取單條Province記錄及與該條記錄相關聯的City記錄。

exec sp_executesql N'SELECT 
[Limit1].[ProvinceID] AS [ProvinceID], 
[Limit1].[ProvinceNo] AS [ProvinceNo], 
[Limit1].[ProvinceName] AS [ProvinceName]
FROM ( SELECT TOP (2) 
    [Extent1].[ProvinceID] AS [ProvinceID], 
    [Extent1].[ProvinceNo] AS [ProvinceNo], 
    [Extent1].[ProvinceName] AS [ProvinceName]
    FROM [dbo].[Province] AS [Extent1]
    WHERE [Extent1].[ProvinceID] = @p0
)  AS [Limit1]',N'@p0 int',@p0=3
exec sp_executesql N'SELECT 
[Extent1].[CityID] AS [CityID], 
[Extent1].[ProvinceID] AS [ProvinceID], 
[Extent1].[CityNo] AS [CityNo], 
[Extent1].[CityName] AS [CityName]
FROM [dbo].[City] AS [Extent1]
WHERE [Extent1].[ProvinceID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=3

　　延遲載入的不足：

　　延遲載入使用簡單，應用程式不需要真正知道資料已經被從資料庫中載入出來，但只要將可能導致大量的SQL查詢被髮送到資料庫中執行，資料庫進行了不必要的查詢。

　　2、貪婪載入（Eager Loading）

　　貪婪載入：使用Include載入關聯的資料，在Entity Framework進行查詢時，即同時載入出關聯的資料。Entity Framework貪婪載入將使用一條JOIN的SQL語句進行查詢。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Entity;

using Portal.Models;

namespace Portal
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new PortalContext())
            {
                var provinces = ctx.Provinces
                    .Include(p => p.Cities);

                foreach (var province in provinces)
                {
                    foreach (var city in province.Cities)
                    {
                        Console.WriteLine("{0}-{1}", province.ProvinceName, city.CityName);
                    }
                }
            }
        }
    }
}
View Code

　　執行程式碼所執行的SQL語句：

SELECT 
[Project1].[ProvinceID] AS [ProvinceID], 
[Project1].[ProvinceNo] AS [ProvinceNo], 
[Project1].[ProvinceName] AS [ProvinceName], 
[Project1].[C1] AS [C1], 
[Project1].[CityID] AS [CityID], 
[Project1].[ProvinceID1] AS [ProvinceID1], 
[Project1].[CityNo] AS [CityNo], 
[Project1].[CityName] AS [CityName]
FROM ( SELECT 
    [Extent1].[ProvinceID] AS [ProvinceID], 
    [Extent1].[ProvinceNo] AS [ProvinceNo], 
    [Extent1].[ProvinceName] AS [ProvinceName], 
    [Extent2].[CityID] AS [CityID], 
    [Extent2].[ProvinceID] AS [ProvinceID1], 
    [Extent2].[CityNo] AS [CityNo], 
    [Extent2].[CityName] AS [CityName], 
    CASE WHEN ([Extent2].[CityID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM  [dbo].[Province] AS [Extent1]
    LEFT OUTER JOIN [dbo].[City] AS [Extent2] ON [Extent1].[ProvinceID] = [Extent2].[ProvinceID]
)  AS [Project1]
ORDER BY [Project1].[ProvinceID] ASC, [Project1].[C1] ASC

　　Include語句可以在一次查詢中使用多次。

ctx.Categories
    .Include(c => c.Products)
    .Include(c => c.News);

　　貪婪載入的不足：

　　貪婪載入的優勢在於僅執行1次SQL查詢即返回所需要的結果。但使用JOIN查詢在資料庫記錄條數較多時，多條簡單的SQL查詢往往比一條複雜的JOIN查詢效率要好。

 

　　使用Include的LINQ查詢

var provinces = ctx.Provinces
    .Include(p => p.Cities)
    .Where(p => p.ProvinceID > 10);
var provinces = from p in ctx.Provinces.Include(p => p.Cities)
                where p.ProvinceID > 10
                select p;
var expr = from p in ctx.Provinces
           where p.ProvinceID > 10
           select p;
var provinces = expr.Include(p => p.Cities);

　　3、顯示載入（Explicit Loading）

　　顯示載入與延遲載入一樣，採用主資料與關聯資料獨立分開載入。顯示載入與延遲載入的區別在於顯示載入不會自動的載入關聯資料，需要呼叫方法去載入。

　　顯示載入是使用DbContext.Entry方法來實現的，Entry方法可以獲取DbContext中的實體資訊。在使用Entry獲取實體資訊之後，可以使用Collection或Reference方法獲取和操作實體關聯的集合屬性。如使用Load方法查詢集合屬性。

　　示例1：顯示載入，使用Collection獲取集合屬性

using (var ctx = new PortalContext())
{
    var province = ctx.Provinces.Find(3);
    ctx.Entry(province)
        .Collection(p => p.Cities)
        .Query()
        .Load();

    foreach (var city in province.Cities)
    {
        Console.WriteLine("{0}-{1}", province.ProvinceName, city.CityName);
    }
}

　　上面的程式碼執行之後，執行的SQL語句：

exec sp_executesql N'SELECT 
[Limit1].[ProvinceID] AS [ProvinceID], 
[Limit1].[ProvinceNo] AS [ProvinceNo], 
[Limit1].[ProvinceName] AS [ProvinceName]
FROM ( SELECT TOP (2) 
    [Extent1].[ProvinceID] AS [ProvinceID], 
    [Extent1].[ProvinceNo] AS [ProvinceNo], 
    [Extent1].[ProvinceName] AS [ProvinceName]
    FROM [dbo].[Province] AS [Extent1]
    WHERE [Extent1].[ProvinceID] = @p0
)  AS [Limit1]',N'@p0 int',@p0=3
exec sp_executesql N'SELECT 
[Extent1].[CityID] AS [CityID], 
[Extent1].[ProvinceID] AS [ProvinceID], 
[Extent1].[CityNo] AS [CityNo], 
[Extent1].[CityName] AS [CityName]
FROM [dbo].[City] AS [Extent1]
WHERE [Extent1].[ProvinceID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=3

　　從程式碼執行所執行的SQL語句可以看出，其查詢資料庫的方式與延遲載入是相同的。

　　示例2：顯示載入，使用Reference方法獲取引用屬性

using (var ctx = new PortalContext())
{
    var city = ctx.Cities.Find(10);
    ctx.Entry(city).Reference(c => c.Province);
    Console.WriteLine("{0}-{1}", city.Province.ProvinceName, city.CityName);
}

　　上面的程式碼執行之後執行的SQL語句：

exec sp_executesql N'SELECT 
[Limit1].[CityID] AS [CityID], 
[Limit1].[ProvinceID] AS [ProvinceID], 
[Limit1].[CityNo] AS [CityNo], 
[Limit1].[CityName] AS [CityName]
FROM ( SELECT TOP (2) 
    [Extent1].[CityID] AS [CityID], 
    [Extent1].[ProvinceID] AS [ProvinceID], 
    [Extent1].[CityNo] AS [CityNo], 
    [Extent1].[CityName] AS [CityName]
    FROM [dbo].[City] AS [Extent1]
    WHERE [Extent1].[CityID] = @p0
)  AS [Limit1]',N'@p0 int',@p0=10
exec sp_executesql N'SELECT 
[Extent1].[ProvinceID] AS [ProvinceID], 
[Extent1].[ProvinceNo] AS [ProvinceNo], 
[Extent1].[ProvinceName] AS [ProvinceName]
FROM [dbo].[Province] AS [Extent1]
WHERE [Extent1].[ProvinceID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=3

　　檢查集合屬性是否已經載入：

using (var ctx = new PortalContext())
{
    var province = ctx.Provinces.Find(3);
    Console.WriteLine("Before load：{0}", ctx.Entry(province).Collection(p => p.Cities).IsLoaded);

    ctx.Entry(province)
        .Collection(p => p.Cities)
        .Load();

    Console.WriteLine("After load：{0}", ctx.Entry(province).Collection(p => p.Cities).IsLoaded);
}

　　4、集合屬性查詢

　　在使用Entry和Collection方法獲取到實體集合屬性之後，可以使用Query方法對集合屬性進行查詢。

　　示例：從記憶體中查詢集合屬性

using (var ctx = new PortalContext())
{
    var province = ctx.Provinces.Find(5);
    var cities = from c in province.Cities
                 where c.CityID > 30
                 select c;
    foreach (var city in cities)
    {
        Console.WriteLine("{0}-{1}", city.CityID, city.CityName);
    }
}

　　程式碼執行之後執行的SQL語句：

exec sp_executesql N'SELECT 
[Limit1].[ProvinceID] AS [ProvinceID], 
[Limit1].[ProvinceNo] AS [ProvinceNo], 
[Limit1].[ProvinceName] AS [ProvinceName]
FROM ( SELECT TOP (2) 
    [Extent1].[ProvinceID] AS [ProvinceID], 
    [Extent1].[ProvinceNo] AS [ProvinceNo], 
    [Extent1].[ProvinceName] AS [ProvinceName]
    FROM [dbo].[Province] AS [Extent1]
    WHERE [Extent1].[ProvinceID] = @p0
)  AS [Limit1]',N'@p0 int',@p0=5
exec sp_executesql N'SELECT 
[Extent1].[CityID] AS [CityID], 
[Extent1].[ProvinceID] AS [ProvinceID], 
[Extent1].[CityNo] AS [CityNo], 
[Extent1].[CityName] AS [CityName]
FROM [dbo].[City] AS [Extent1]
WHERE [Extent1].[ProvinceID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=5

　　從對City表執行的SQL語句可以看出，其並對加入查詢條件，僅只是對之前通過延長載入方式將Province實體的Cities集合屬性載人到記憶體中，然後通過對記憶體中的Cities資料進行記憶體查詢，並未生成新的包含查詢條件的SQL語句。

　　示例：在資料庫中查詢集合屬性

using (var ctx = new PortalContext())
{
    var province = ctx.Provinces.Find(5);
    var expr = ctx.Entry(province)
        .Collection(p => p.Cities)
        .Query();
    var cities = from c in expr
                 where c.CityID > 30
                 select c;
    foreach (var city in cities)
    {
        Console.WriteLine("{0}-{1}", city.CityID, city.CityName);
    }
}

　　程式碼執行之後執行的SQL語句：

exec sp_executesql N'SELECT 
[Limit1].[ProvinceID] AS [ProvinceID], 
[Limit1].[ProvinceNo] AS [ProvinceNo], 
[Limit1].[ProvinceName] AS [ProvinceName]
FROM ( SELECT TOP (2) 
    [Extent1].[ProvinceID] AS [ProvinceID], 
    [Extent1].[ProvinceNo] AS [ProvinceNo], 
    [Extent1].[ProvinceName] AS [ProvinceName]
    FROM [dbo].[Province] AS [Extent1]
    WHERE [Extent1].[ProvinceID] = @p0
)  AS [Limit1]',N'@p0 int',@p0=5
exec sp_executesql N'SELECT 
[Extent1].[CityID] AS [CityID], 
[Extent1].[ProvinceID] AS [ProvinceID], 
[Extent1].[CityNo] AS [CityNo], 
[Extent1].[CityName] AS [CityName]
FROM [dbo].[City] AS [Extent1]
WHERE ([Extent1].[ProvinceID] = @EntityKeyValue1) AND ([Extent1].[CityID] > 30)',N'@EntityKeyValue1 int',@EntityKeyValue1=5

　　集合屬性Count查詢

using (var ctx = new PortalContext())
{
    var province = ctx.Provinces.Find(5);
    var expr = ctx.Entry(province)
        .Collection(p => p.Cities)
        .Query();
    Console.WriteLine(expr.Count());
}

　　程式碼執行生成的SQL語句：

exec sp_executesql N'SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[City] AS [Extent1]
    WHERE [Extent1].[ProvinceID] = @EntityKeyValue1
)  AS [GroupBy1]',N'@EntityKeyValue1 int',@EntityKeyValue1=5

　　顯示載入集合屬性的子集：

using (var ctx = new PortalContext())
{
    var province = ctx.Provinces.Find(5);
    ctx.Entry(province)
        .Collection(p => p.Cities)
        .Query()
        .Where(c => c.CityNo.Contains("3"))
        .Load();
}

　　程式碼執行後生成的SQL語句：

exec sp_executesql N'SELECT 
[Extent1].[CityID] AS [CityID], 
[Extent1].[ProvinceID] AS [ProvinceID], 
[Extent1].[CityNo] AS [CityNo], 
[Extent1].[CityName] AS [CityName]
FROM [dbo].[City] AS [Extent1]
WHERE ([Extent1].[ProvinceID] = @EntityKeyValue1) AND ([Extent1].[CityNo] LIKE N''%3%'')',N'@EntityKeyValue1 int',@EntityKeyValue1=5

相關文章