如何避免N + 1在EF生成的查询 [英] How to avoid N+1 in EF generated queries

问题描述

我曾经为POCO中的多对多关系或关系Ids生成所有的帮助表，如 UsergroupUsers ，但现在我希望EF来处理它们。现在我不认为这是一个好主意。

问题

当我尝试获得所有 UsergroupDynamicField 针对特定用户，它为每个用户组用户生成 N + 1 查询。

，您可以使用：

  var usergroupsQuery =（（EntityCollection< UserGroup>）this.Usergroups）。 CreateSourceQuery（）; 
 var fields = usergroupsQuery.SelectMany（x => x.UsergroupDynamicFields）; 
  

更新：正如评论中指出的，才会使用 EntityCollection< T> 属性 virtual ）。您可以以另一种方式创建查询：

  var usergroupsQuery = db.Entry（this）.Collection（u => u。群组）.Query（）; 
 var fields = usergroupsQuery.SelectMany（x => x.UsergroupDynamicFields）; 
  

请注意，这需要您可以访问 db 不知何故。

I used to generate all the helper tables like UsergroupUsers for many-to-many relations or relational Ids in POCO myself but now I want EF to take care of them. Now I don't think it's such a good idea after all.

Problem

When I try to get all UsergroupDynamicField for particular user it generates N+1 query for every usergroup user is in.

Here I overcommed this problem by simply stating that Usergroups will be IQUeriable instead of IEnumerable. Now I cannot do that because EF won't map it, it has to be ICollection.

Code

public class User
{
    ...
    public virtual ICollection<Usergroup> Usergroups { get; set; }
    public IEnumerable<UserField> Fields
    {
        get
        {
            var fields = this.Usergroups.SelectMany(x => x.UsergroupDynamicFields); // N + 1 for every Usergroup

            foreach (var field in fields)
            {
                yield return new UserField
                {
                    Name = field.Name
                };
            }
        }
    }
}

Database

解决方案

Here I overcommed this problem by simply stating that Usergroups will be IQUeriable instead of IEnumerable. Now I cannot do that because EF won't map it, it has to be ICollection.

But the class that ends up implementing ICollection is EntityCollection<T>. This collection has a CreateSourceQuery() function that you can use:

var usergroupsQuery = ((EntityCollection<UserGroup>)this.Usergroups).CreateSourceQuery();
var fields = usergroupsQuery.SelectMany(x => x.UsergroupDynamicFields);

Update: as pointed out in the comments, ICollection<T> will only be implemented using EntityCollection<T> when change tracking is possible and enabled (non-sealed classes, and all relevant properties virtual). You can create a query another way:

var usergroupsQuery = db.Entry(this).Collection(u => u.Usergroups).Query();
var fields = usergroupsQuery.SelectMany(x => x.UsergroupDynamicFields);

Note that this requires that you have access to db somehow.

这篇关于如何避免N + 1在EF生成的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！