Entity Framework core select causes too many queries

Question

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; }
}

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

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

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

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

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.

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?

Answer 1

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

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

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
}

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();

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.

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.

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.

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