使用实体框架导航属性而不创建大量查询（避免N + 1） [英] Using Entity Framework navigation properties without creating lots of queries (avoiding N+1)

问题描述

我一直在使用Entity Framework Profiler来测试我在MVC项目中的数据访问，并且在几个页面上进行了比我需要的数据库查询要多得多，因为N + 1个问题。

这是一个简单的例子来显示我的问题：

  var club = this。 ActiveClub; // ActiveClub使用类似于context.Clubs.First（）的代码
 var members = club.Members.ToList（）; 
 return View（MembersWithAddress，成员）; 
  

视图循环遍历成员，然后跟随每个成员的导航属性，以显示其地址。每个地址请求都会产生一个额外的数据库查询。

解决这个问题的一个方法是使用Include来确保我需要的额外的表被查询。但是，我似乎只能在直接附加到上下文的Clubs的ObjectSet上执行此操作。在这种情况下，ActiveClub属性由许多控制器共享，我并不总是想查询会员和地址表。

我想要能够使用以下的东西：

  var members = club.Members.Include（Address）ToList（）; 
  

但是，成员是一个EntityCollection，并没有包含Include方法。 >

有没有办法强制加载成员EntityCollection，并要求EF也加载他们的地址？

或者，正是以这种方式在实体上使用EntityCollection导航属性，只是一个非常糟糕的主意;你应该知道你从上下文中得到什么？

解决方案

如果你的实体继承自 EntityObject 尝试使用：

  var members = club.Members.CreateSourceQuery（） 
 .Include（Address）
 .ToList（）; 
  

如果您使用带有延迟加载代理的POCO，请尝试使用：

  var members =（（EntityCollection< Club>）club.Members）.CreateSourceQuery（）
 .Include（Address）
。 ToList（）; 
  

显然第二个版本不是很好，因为POCO用于删除对EF的依赖，但现在您需要将集合转换为EF类。另一个问题是查询将被执行两次。懒惰加载将触发成员一次访问该属性，然后当您调用 ToList 时，将执行第二个查询。这可以通过关闭延迟加载在运行查询之前。

当您说 ActiveClub 是共享我相信这意味着像派生控制器中使用的基本控制器中的属性。在这种情况下，您仍然可以在不同的控制器中使用不同的代码填充属性。

I've been using Entity Framework Profiler to test my data access in an MVC project and have come accross several pages where I'm making far more db queries than I need to because of N+1 problems.

Here is a simple example to show my problem:

var club = this.ActiveClub; // ActiveClub uses code similar to context.Clubs.First() 
var members = club.Members.ToList();
return View("MembersWithAddress", members);

The view loops through Members and then follows a navigion property on each member to also show their address. Each of the address requests results in an extra db query.

One way to solve this would be to use Include to make sure the extra tables I need are queried up front. However, I only seem to be able to do this on the ObjectSet of Clubs attached directly to the context. In this case the ActiveClub property is shared by lots of controllers and I don't always want to query the Member and address table up front.

I'd like to be able to use something like:

var members = club.Members.Include("Address").ToList();

But, Members is an EntityCollection and that doesn't have the Include method on it.

Is there a way to force a load on the Members EntityCollection and ask EF to also load their Addresses?

Or, is using EntityCollection navigation properties on an entity in this way, just a really bad idea; and you should know what you're loading when you get it from the context?

解决方案

If your entities inherits from EntityObject try to use this:

var members = club.Members.CreateSourceQuery()
                          .Include("Address")
                          .ToList();

If you use POCOs with lazy loading proxies try to use this:

var members = ((EntityCollection<Club>)club.Members).CreateSourceQuery()
                                                    .Include("Address")
                                                    .ToList();

Obviously second version is not very nice because POCOs are used to remove dependency on EF but now you need to convert the collection to EF class. Another problem is that the query will be executed twice. Lazy loading will trigger for Members once to access the property and then second query will be executed when you call ToList. This can be solved by turning off lazy loading prior to running the query.

When you say ActiveClub is shared I believe it means something like it is property in base controller used in derived controllers. In such case you can still use different code in different controller to fill the property.

这篇关于使用实体框架导航属性而不创建大量查询（避免N + 1）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！