实体框架核心选择导致太多查询 [英] Entity Framework core select causes too many queries

问题描述

我具有以下方法，该方法用于构建单个对象实例，该对象实例的属性通过递归调用相同的方法来构建：

I have the following method which is meant to build me up a single object instance, where its properties are built via recursively calling the same method:

public ChannelObjectModel GetChannelObject(Guid id, Guid crmId)
    {
        var result = (from channelObject in _channelObjectRepository.Get(x => x.Id == id)
                      select new ChannelObjectModel
                      {
                          Id = channelObject.Id,
                          Name = channelObject.Name,
                          ChannelId = channelObject.ChannelId,
                          ParentObjectId = channelObject.ParentObjectId,
                          TypeId = channelObject.TypeId,
                          ChannelObjectType = channelObject.ChannelObjectTypeId.HasValue ? GetChannelObject(channelObject.ChannelObjectTypeId.Value, crmId) : null,
                          ChannelObjectSearchType = channelObject.ChannelObjectSearchTypeId.HasValue ? GetChannelObject(channelObject.ChannelObjectSearchTypeId.Value, crmId) : null,
                          ChannelObjectSupportingObject = channelObject.ChannelObjectSupportingObjectId.HasValue ? GetChannelObject(channelObject.ChannelObjectSupportingObjectId.Value, crmId) : null,
                          Mapping = _channelObjectMappingRepository.Get().Where(mapping => mapping.ChannelObjectId == channelObject.Id && mapping.CrmId == crmId).Select(mapping => new ChannelObjectMappingModel
                          {
                              CrmObjectId = mapping.CrmObjectId
                          }).ToList(),
                          Fields = _channelObjectRepository.Get().Where(x => x.ParentObjectId == id).Select(field => GetChannelObject(field.Id, crmId)).ToList()
                      }
                     );
        return result.First();
    }

public class ChannelObjectModel
{
    public ChannelObjectModel()
    {
        Mapping = new List<ChannelObjectMappingModel>();
        Fields = new List<ChannelObjectModel>();
    }
    public Guid Id { get; set; }
    public Guid ChannelId { get; set; }
    public string Name { get; set; }
    public List<ChannelObjectMappingModel> Mapping { get; set; }
    public int TypeId { get; set; }
    public Guid? ParentObjectId { get; set; }
    public ChannelObjectModel ParentObject { get; set; }
    public List<ChannelObjectModel> Fields { get; set; }
    public Guid? ChannelObjectTypeId { get; set; }
    public ChannelObjectModel ChannelObjectType { get; set; }
    public Guid? ChannelObjectSearchTypeId { get; set; }
    public ChannelObjectModel ChannelObjectSearchType { get; set; }
    public Guid? ChannelObjectSupportingObjectId { get; set; }
    public ChannelObjectModel ChannelObjectSupportingObject { get; set; }
}

这是使用Entity Framework Core 2.1.1连接到SQL数据库的

this is connecting to a SQL database using Entity Framework Core 2.1.1

虽然从技术上讲它可以正常工作，但它会引起数据库查询的负载-我意识到这是因为 ToList（ ）和 First（）等调用。

Whilst it technically works, it causes loads of database queries to be made - I realise its because of the ToList() and First() etc. calls.

但是由于对象的性质，我可以制作一个巨大的 IQueryable< anonymous> 对象，并带有来自...的。选择新的{...} 并调用首先，但是代码超过了300行，仅在层次结构中深了5层，所以我试图用上面的代码代替它，这更加干净，尽管速度要慢得多。

However because of the nature of the object, I can make one huge IQueryable<anonymous> object with a from.... select new {...} and call First on it, but the code was over 300 lines long going just 5 tiers deep in the hierarchy, so I am trying to replace it with something like the code above, which is much cleaner, albeit much slower..

ChannelObjectType, ChannelObjectSearchType, ChannelObjectSupportingObject

都是 ChannelObjectModel 实例，而Fields是 ChannelObjectModel 实例。

Are all ChannelObjectModel instances and Fields is a list of ChannelObjectModel instances.

查询大约需要30秒当前执行的速度太慢了，它也位于一个小型的localhost数据库上，因此，只有大量的db记录才会变得更糟，并且在我运行它时会生成很多数据库调用。

The query takes about 30 seconds to execute currently, which is far too slow and it is on a small localhost database too, so it will only get worse with a larger number of db records, and generates a lot of database calls when I run it.

300行以上的代码生成的查询要少得多，并且速度相当快，但显然是可怕的，可怕的代码（我没有写过！）

The 300+ lines code generates a lot less queries and is reasonably quick, but is obviously horrible, horrible code (which I didn't write!)

有人可以建议一种与上述方法类似的递归构建对象的方法，但是可以大大减少数据库调用的次数，从而更快吗？

Can anyone suggest a way I can recursively build up an object in a similar way to the above method, but drastically cut the number of database calls so it's quicker?

推荐答案

我使用的是EF6，而不是Core，但据我所知，这里同样适用。

I work with EF6, not Core, but as far as I know, same things apply here.

首先首先，将此函数移到您的存储库中，以便所有调用共享DbContext实例。

First of all, move this function to your repository, so that all calls share the DbContext instance.

其次，使用在属性的DbSet上包含，以便于加载它们：

Secondly, use Include on your DbSet on properties to eager load them:

ctx.DbSet<ChannelObjectModel>()
     .Include(x => x.Fields)
     .Include(x => x.Mapping)
     .Include(x => x.ParentObject) 
     ...

好的做法是使它成为上下文（或扩展方法）的函数，例如

Good practice is to make this a function of context (or extension method) called for example BuildChannelObject() and it should return the IQueryable - just the includes.

然后您可以启动递归部分：

Then you can start the recursive part:

public ChannelObjectModel GetChannelObjectModel(Guid id)
{
    var set = ctx.BuildChannelObject(); // ctx is this

    var channelModel = set.FirstOrDefault(x => x.Id == id); // this loads the first level

    LoadRecursive(channelModel, set);

    return channelModel;
}

private void LoadRecursive(ChannelObjectModel c, IQueryable<ChannelObjectModel> set)
{
     if(c == null)
         return; // recursion end condition

     c.ParentObject = set.FirstOrDefault(x => x.Id == c?.ParentObject.Id);
    // all other properties

     LoadRecursive(c.ParentObject, set);
    // all other properties
}

如果所有这些代码都使用相同的DbContext的实例，应该很快。如果没有，则可以使用另一种技巧：

If all this code uses the same instance of DbContext, it should be quite fast. If not, you can use another trick:

ctx.DbSet<ChannelObjectModel>().BuildChannelObjectModel().Load();

这会将所有对象加载到DbContext的内存缓存中。不幸的是，它死于上下文实例，但是由于没有进行数据库行程，所以它使那些递归调用快得多。

This loads all objects to memory cache of your DbContext. Unfortunately, it dies with context instance, but it makes those recursive calls much faster, since no database trip is made.

如果这样做仍然很慢，则可以添加 AsNoTracking（）作为BuildChannelObjectModel（）的最后一条指令。

If this is still to slow, you can add AsNoTracking() as last instruction of BuildChannelObjectModel().

如果这仍然很慢，只需实现应用程序范围的内存缓存这些对象并使用它而不是每次都查询数据库-如果您的应用是可以长时间启动但又可以快速运行的服务，则效果很好。

If this is still to slow, just implement application wide memory cache of those objects and use that instead of querying database everytime - this works great if your app is a service that can have long startup, but then work fast.

方法是通过将导航属性标记为虚拟来启用延迟加载-但是请记住，返回的类型将是派生类型的匿名代理，而不是原始的ChannelObjectModel！此外，只有在您不处理上下文的情况下，属性才会加载-此后，您将获得异常。要用上下文加载所有属性然后返回完整的对象也有些棘手-最简单（但不是最好的方法！）的方法是在返回对象之前将对象序列化为JSON（记住关于循环引用）。

Whole another approach is to enable lazy loading by marking navigation properties as virtual - but remember that returned type will be derived type anonymous proxy, not your original ChannelObjectModel! Also, properties will load only as long you don't dispose the context - after that you get an exception. To load all properties with the context and then return complete object is also a little bit tricky - easiest (but not the best!) way to do it to serialize the object to JSON (remember about circural references) before returning it.

如果这不能满足您的要求，请切换到nHibernate，我听说默认情况下具有应用程序级缓存。

If that does not satisfy you, switch to nHibernate which I hear has application wide cache by default.

这篇关于实体框架核心选择导致太多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！