Entity Framework 6 Recipes 2nd Edition（13-9）译 -> 避免Include

问题

你想不用Include()方法,立即加载一下相关的集合,并想通过EF的CodeFirst方式实现.

 

解决方案

假设你有一个如Figure 13-14所示的模型:

 

Figure 13-14. A model for a Customer, their CreditCards, and Transactions

 

首先本例通过EF的CodeFrist方式实现,代码Listing 13-23,我们创建Customer,CreditCard和Transaction实体类.

Listing 13-23. The Reservation Entity Object

    public class Customer

    {

        public Customer()

        {

            CreditCards = new HashSet<CreditCard>();

        }

        public int CustomerId { get; set; }

        public string Name { get; set; }

        public string City { get; set; }

        public virtual ICollection<CreditCard> CreditCards { get; set; }

    }

    public class CreditCard

    {

        public CreditCard()

        {

            Transactions = new HashSet<Transaction>();

        }

        public string CardNumber { get; set; }

        public string Type { get; set; }

        public System.DateTime ExpirationDate { get; set; }

        public int CustomerId { get; set; }

        public virtual Customer Customer { get; set; }

        public virtual ICollection<Transaction> Transactions { get; set; }

    }

 

    public class Transaction

    {

        public int TransactionId { get; set; }

        public string CardNumber { get; set; }

        public decimal Amount { get; set; }

        public virtual CreditCard CreditCard { get; set; }

}

接下来,代码Listing 13-24,我们创建DbObject对象,通过它访问EF的功能.

Listing 13-24. DbContext Object

    public class Recipe9Context : DbContext

    {

        public Recipe9Context()

            : base("Recipe9ConnectionString")

        {

            // Disable Entity Framework Model Compatibility

            Database.SetInitializer<Recipe10Context>(null);

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)

        {

            // explicilty specify primary key for CreditCard

            modelBuilder.Entity<CreditCard>().HasKey(x => x.CardNumber);

            modelBuilder.Entity<Customer>().ToTable("Chapter13.Customer");

            modelBuilder.Entity<CreditCard>().ToTable("Chapter13.CreditCard");

            modelBuilder.Entity<Transaction>().ToTable("Chapter13.Transaction");

        }

        public DbSet<CreditCard> CreditCards { get; set; }

        public DbSet<Customer> Customers { get; set; }

        public DbSet<Transaction> Transactions { get; set; }

    }

 

接下来在项目中添加App.config,并把代码Listing13-25 添加到ConnectionStrings节下.

Listing 13-25. Connection String

<connectionStrings>

<add name="Recipe9ConnectionString"

connectionString="Data Source=.;

Initial Catalog=EFRecipes;

Integrated Security=True;

MultipleActiveResultSets=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

 

想通过指定的City把Customers和相关的CreditCards以及Transactions都加载,但不用Include()方法.式加载实体和让EF维护关联,如代码Listing13-26所示

Listing 13-26. Loading Related Entities without Using Include()

            using (var context = new Recipe9Context())

            {

                var cust1 = new Customer { Name = "Robin Rosen", City = "Raytown" };

                var card1 = new CreditCard

                {

                    CardNumber = "41949494338899",

                    ExpirationDate = DateTime.Parse("12/2010"),

                    Type = "Visa"

                };

                var trans1 = new Transaction { Amount = 29.95M };

                card1.Transactions.Add(trans1);

                cust1.CreditCards.Add(card1);

                var cust2 = new Customer { Name = "Bill Meyers", City = "Raytown" };

                var card2 = new CreditCard

                {

                    CardNumber = "41238389484448",

                    ExpirationDate = DateTime.Parse("12/2013"),

                    Type = "Visa"

                };

                var trans2 = new Transaction { Amount = 83.39M };

                card2.Transactions.Add(trans2);

                cust2.CreditCards.Add(card2);

                context.Customers.Add(cust1);

                context.Customers.Add(cust2);

                context.SaveChanges();

            }

            using (var context = new Recipe9Context())

            {

                var customers = context.Customers.Where(c => c.City == "Raytown");

                var creditCards = customers.SelectMany(c => c.CreditCards);

                var transactions = creditCards.SelectMany(cr => cr.Transactions);

                // 执行查询,EF会维护关联

                customers.ToList();

                creditCards.ToList();

                transactions.ToList();

                foreach (var customer in customers)

                {

                    Console.WriteLine("Customer: {0} in {1}", customer.Name, customer.City);

                    foreach (var creditCard in customer.CreditCards)

                    {

                        Console.WriteLine("\tCard: {0} expires on {1}",

                        creditCard.CardNumber, creditCard.ExpirationDate.ToShortDateString());

                        foreach (var trans in creditCard.Transactions)

                        {

                            Console.WriteLine("\t\tTransaction: {0}", trans.Amount.ToString("C"));

                        }

                    }

                }

        }

输出结果如下:

Customer: Robin Rosen in Raytown

Card: 41949494338899 expires on 12/1/2010

Transaction: $29.95

Customer: Bill Meyers in Raytown

Card: 41238389484448 expires on 12/1/2013

Transaction: $83.39

 

它是如何工作

Inclue()方法是一个强大的和通常有效的急切加载相关的实体的工具.然而Include()确实存在一些性能缺陷.虽然使用Include()能一次性从数据库返回结果,但如代码Listing 13-26用三个查询来代码,因为用一个查询可能会使查询很复杂,在某些情况下,一个查询不一定能像三个简单查询那样,执行得那么好.另外,单个的复杂的查询返回的结果集包含更多的列,增加通过线缆传送数据的负担.规则是:在你的查询中越多的Include,就得付出越高的性能代价.

         另一方面,不用Include()方法或迭代超过一定数量的Customers,我们创建太多的小查询,结果也会导致大量的性能损失.第5单讨论了获取明细的平衡做法.