Entity Framework DbContext 延遲加載、貪婪加載、顯性加載

2014-12-17 16:26:00

用一個留言版的主表和明細表當例子，順便加入初始化資料來方便觀察資料載入方式的差異
DemoContext

namespace ConsoleApplication1.Models
{
    using System.Data.Entity;

    public class DemoContext : DbContext
    {
        public DbSet<Topic> Topic { get; set; }
        public DbSet<Reply> Reply { get; set; }
    }
}

留言版主表

namespace ConsoleApplication1.Models
{
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    [Table(“Topic”)]
    public class Topic
    {
        [Key]
        public int TopicId { get; set; }
        public string TopicTitle { get; set; }
        public virtual ICollection<Reply> Reply { get; set; }
    }
}

留言版明細表

namespace ConsoleApplication1.Models
{
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    [Table(“Reply”)]
    public class Reply
    {
        [Key]
        public int ReplyId { get; set; }
        [ForeignKey(“Topic”)]
        public int TopicId { get; set; }
        public string ReplyMessage { get; set; }
        public virtual Topic Topic { get; set; }
    }
}

資料初始化

namespace ConsoleApplication1.Models
{
    using System.Collections.Generic;
    using System.Data.Entity;

    public class MyInit : DropCreateDatabaseAlways<DemoContext>
    {
        protected override void Seed(DemoContext context)
        {
            context.Topic.AddRange(
                new List<Topic>
                {
                    new Topic
                    {
                        TopicTitle = “a”,
                        Reply = new List<Reply>
                        {
                            new Reply { ReplyMessage = “a1” },
                            new Reply { ReplyMessage = “a2” },
                            new Reply { ReplyMessage = “a3” },
                        }
                    },
                    new Topic
                    {
                        TopicTitle = “b”,
                        Reply = new List<Reply>
                        {
                            new Reply { ReplyMessage = “b1” },
                            new Reply { ReplyMessage = “b2” },
                            new Reply { ReplyMessage = “b3” },
                        }
                    },
                    new Topic
                    {
                        TopicTitle = “c”,
                        Reply = new List<Reply>
                        {
                            new Reply { ReplyMessage = “c1” },
                            new Reply { ReplyMessage = “c2” },
                            new Reply { ReplyMessage = “c3” },
                        }
                    },
                    new Topic
                    {
                        TopicTitle = “d”
                    }
                });
        }
    }
}

產生的資料

延遲加載利用遠端代理模式，必須在導覽屬性上使用virtual關鍵字，才會在存取到這個屬性時，動態去建立相對應的類別
這種方式比較明顯的缺點，是會分段產生SQL語法，還有就是在取資料的時後，雖然有加入過濾的條件，但是最後產生的SQL語法，還是會取回全部的資料，然後才在記憶體中過濾資料

namespace ConsoleApplication1
{
    using ConsoleApplication1.Models;
    using System;
    using System.Data.Entity;
    using System.Linq;

    class Program
    {
        static void Main(string[] args)
        {
            // 初始化資料庫
            Database.SetInitializer(new MyInit());
            using (DemoContext db = new DemoContext())
            {
                db.Database.Initialize(false);

                foreach (var topic in db.Topic)
                {
                    foreach (var reply in topic.Reply.Where(x=>x.ReplyMessage.Contains(“1”)))
                    {
                        Console.WriteLine(“title:{0}, message:{1}”, topic.TopicTitle, reply.ReplyMessage);
                    }
                }
            }
        }
    }
}

延遲加載產生的SQL語法

SELECT
    [Extent1].[TopicId] AS [TopicId],
    [Extent1].[TopicTitle] AS [TopicTitle]
    FROM [dbo].[Topic] AS [Extent1]
go
exec sp_executesql N’SELECT
    [Extent1].[ReplyId] AS [ReplyId],
    [Extent1].[TopicId] AS [TopicId],
    [Extent1].[ReplyMessage] AS [ReplyMessage]
    FROM [dbo].[Reply] AS [Extent1]
    WHERE [Extent1].[TopicId] = @EntityKeyValue1’,N‘@EntityKeyValue1 int’,@EntityKeyValue1=1
go
exec sp_executesql N’SELECT
    [Extent1].[ReplyId] AS [ReplyId],
    [Extent1].[TopicId] AS [TopicId],
    [Extent1].[ReplyMessage] AS [ReplyMessage]
    FROM [dbo].[Reply] AS [Extent1]
    WHERE [Extent1].[TopicId] = @EntityKeyValue1’,N‘@EntityKeyValue1 int’,@EntityKeyValue1=2
go
exec sp_executesql N’SELECT
    [Extent1].[ReplyId] AS [ReplyId],
    [Extent1].[TopicId] AS [TopicId],
    [Extent1].[ReplyMessage] AS [ReplyMessage]
    FROM [dbo].[Reply] AS [Extent1]
    WHERE [Extent1].[TopicId] = @EntityKeyValue1’,N‘@EntityKeyValue1 int’,@EntityKeyValue1=3
go
exec sp_executesql N’SELECT
    [Extent1].[ReplyId] AS [ReplyId],
    [Extent1].[TopicId] AS [TopicId],
    [Extent1].[ReplyMessage] AS [ReplyMessage]
    FROM [dbo].[Reply] AS [Extent1]
    WHERE [Extent1].[TopicId] = @EntityKeyValue1’,N‘@EntityKeyValue1 int’,@EntityKeyValue1=4
go

貪婪加載是產生Join的語法，在一次SQL查詢中就取回所有的資料

namespace ConsoleApplication1
{
    using ConsoleApplication1.Models;
    using System;
    using System.Data.Entity;
    using System.Linq;

    class Program
    {
        static void Main(string[] args)
        {
            // 初始化資料庫
            Database.SetInitializer(new MyInit());
            using (DemoContext db = new DemoContext())
            {
                db.Database.Initialize(false);

                foreach (var topic in db.Topic.Include(x => x.Reply))
                {
                    foreach (var reply in topic.Reply.Where(x => x.ReplyMessage.Contains(“1”)))
                    {
                        Console.WriteLine(“title:{0}, message:{1}”, topic.TopicTitle, reply.ReplyMessage);
                    }
                }
            }
        }
    }
}

產生的SQL語法

SELECT
    [Project1].[TopicId] AS [TopicId],
    [Project1].[TopicTitle] AS [TopicTitle],
    [Project1].[C1] AS [C1],
    [Project1].[ReplyId] AS [ReplyId],
    [Project1].[TopicId1] AS [TopicId1],
    [Project1].[ReplyMessage] AS [ReplyMessage]
    FROM ( SELECT
        [Extent1].[TopicId] AS [TopicId],
        [Extent1].[TopicTitle] AS [TopicTitle],
        [Extent2].[ReplyId] AS [ReplyId],
        [Extent2].[TopicId] AS [TopicId1],
        [Extent2].[ReplyMessage] AS [ReplyMessage],
        CASE WHEN ([Extent2].[ReplyId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
        FROM  [dbo].[Topic] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Reply] AS [Extent2] ON [Extent1].[TopicId] = [Extent2].[TopicId]
    )  AS [Project1]
    ORDER BY [Project1].[TopicId] ASC, [Project1].[C1] ASC

顯性加載也是分段式的語法，和延遲加載的差別在於，延遲加載必須先取回導覽屬性的全部資料，才能繼續後續的處理，而顯性加載則可以篩選要取回那些資料
透過Entry取得實體後，Collection是取集合對應的屬性，Reference是取實體對應的屬性
db.Configuration.LazyLoadingEnabled是用來指定是否要啟用延遲加載，預設是啟用的

namespace ConsoleApplication1
{
    using ConsoleApplication1.Models;
    using System;
    using System.Data.Entity;
    using System.Linq;

    class Program
    {
        static void Main(string[] args)
        {
            // 初始化資料庫
            Database.SetInitializer(new MyInit());
            using (DemoContext db = new DemoContext())
            {
                db.Database.Initialize(false);
                db.Configuration.LazyLoadingEnabled = false;

                foreach (var topic in db.Topic)
                {
                    db.Entry(topic).Collection(x => x.Reply)
                        .Query()
                        .Where(x => x.ReplyMessage.Contains(“1”))
                        .Load();

                    if (topic.Reply != null)
                    {
                        foreach (var reply in topic.Reply)
                        {
                            Console.WriteLine(“title:{0}, message:{1}”, topic.TopicTitle, reply.ReplyMessage);
                        }
                    }
                }
            }
        }
    }
}

產生的SQL語法

SELECT
    [Extent1].[TopicId] AS [TopicId],
    [Extent1].[TopicTitle] AS [TopicTitle]
    FROM [dbo].[Topic] AS [Extent1]
go
exec sp_executesql N’SELECT
    [Extent1].[ReplyId] AS [ReplyId],
    [Extent1].[TopicId] AS [TopicId],
    [Extent1].[ReplyMessage] AS [ReplyMessage]
    FROM [dbo].[Reply] AS [Extent1]
    WHERE ([Extent1].[TopicId] = @EntityKeyValue1) AND ([Extent1].[ReplyMessage] LIKE N’’%1%’’)’,N‘@EntityKeyValue1 int’,@EntityKeyValue1=1
go
exec sp_executesql N’SELECT
    [Extent1].[ReplyId] AS [ReplyId],
    [Extent1].[TopicId] AS [TopicId],
    [Extent1].[ReplyMessage] AS [ReplyMessage]
    FROM [dbo].[Reply] AS [Extent1]
    WHERE ([Extent1].[TopicId] = @EntityKeyValue1) AND ([Extent1].[ReplyMessage] LIKE N’’%1%’’)’,N‘@EntityKeyValue1 int’,@EntityKeyValue1=2
go
exec sp_executesql N’SELECT
    [Extent1].[ReplyId] AS [ReplyId],
    [Extent1].[TopicId] AS [TopicId],
    [Extent1].[ReplyMessage] AS [ReplyMessage]
    FROM [dbo].[Reply] AS [Extent1]
    WHERE ([Extent1].[TopicId] = @EntityKeyValue1) AND ([Extent1].[ReplyMessage] LIKE N’’%1%’’)’,N‘@EntityKeyValue1 int’,@EntityKeyValue1=3
go
exec sp_executesql N’SELECT
    [Extent1].[ReplyId] AS [ReplyId],
    [Extent1].[TopicId] AS [TopicId],
    [Extent1].[ReplyMessage] AS [ReplyMessage]
    FROM [dbo].[Reply] AS [Extent1]
    WHERE ([Extent1].[TopicId] = @EntityKeyValue1) AND ([Extent1].[ReplyMessage] LIKE N’’%1%’’)’,N‘@EntityKeyValue1 int’,@EntityKeyValue1=4
go

Entity Framework, DbContext