EF Core - why Explicit Loading is terribly slow?

domain-driven-design entity-framework-core explicit loading

Question

In.Net Core, I'm working on microservices using a domain-driven architecture. The infrastructure layer uses the EF Core DbContext to access the database, and I have async methods for data retrieval in my repositories.

I have tried every method I could think of to substitute Include/ThenInclude since it does not enable filtering (at least not up to Ef Core 2.1). I also watched the Pluralsight videos about Ef Core, and I was really excited to see the Explicit Loading option because it could filter related objects. However, when I changed one of the methods to the Explicit version, the query that typically took a few milliseconds to complete increased to a few minutes!

I have set up all of the navigations and foreign keys in my entity setups, but I'm not sure whether explicit loading calls for further preparation or not. Please take notice that the Where clause is often lengthier before advising the use of global filters, therefore the sample below is only a condensed form of the real filtering!

Using TransferService as the aggregate, TransferServiceDetail and any other classes are merely entities inside the TransferService domain, and this is how my methods look:

public async Task<IEnumerable<TransferService>> GetAllAsync(
    TransferServiceFilter transferServiceFilter)
    {
        int? pageIndex = null;
        int? itemsPerPage = null;

        IEnumerable<TransferService> filteredList = DBContext.TransferServices.Where(
           ts => !ts.IsDeleted); //This one itself is quick.

        //This is just our filtering, it does not affect performance.
        if (transferServiceFilter != null)
        {
            pageIndex = transferServiceFilter.PageIndex;
            itemsPerPage = transferServiceFilter.ItemsPerPage;

            filteredList = filteredList.Where(f =>
                (transferServiceFilter.TransferSupplierId == null ||
                f.TransferSupplierId == transferServiceFilter.TransferSupplierId) &&
                (transferServiceFilter.TransferDestinationId == null || 
              f.TransferDestinationId == transferServiceFilter.TransferDestinationId) &&
                (transferServiceFilter.TransferSupplierId == null ||
              f.TransferSupplierId == transferServiceFilter.TransferSupplierId) &&
                (string.IsNullOrEmpty(transferServiceFilter.TransportHubRef) ||
              f.NormalizeReference(f.TransportHubRef) == 
                 f.NormalizeReference(transferServiceFilter.TransportHubRef)));
        }

        //This is just for paging and again, this is quick.
        return await FilterList(filteredList.AsQueryable(), pageIndex, itemsPerPage);
    }

    public async Task<IEnumerable<TransferService>> GetAllWithServiceDetailsAsync(
      TransferServiceFilter transferServiceFilter)
    {
        IEnumerable<TransferService> returnList = await GetAllAsync(
           transferServiceFilter);

        //This might be the problem as I need to iterate through my TransferServices
        //to be able to load all TransferServiceDetails that belong to each individual
        //Service.
        foreach (TransferService service in returnList)
        {
            await DBContext.Entry<TransferService>(service)
              .Collection(ts => ts.TransferServiceDetails.Where(
                 tsd => !tsd.IsDeleted)).LoadAsync();
        }

        return returnList;
    }

I also have additional methods in my repository that similarly reference an earlier GetAllXY... function (TransferServiceDetails have Rates, Rates have Periods, etc...).

My original plan was to just call GetAllAsync when I needed TransferService data alone (this method is incredibly fast), or GetAllWithServiceDetailsAsync when I also needed the Details of the selected Services, etc. However, the further down the parent-child hierarchy I go, the slower the execution becomes. I'm not just talking a few extra milliseconds, or in the worst case, seconds here; I'm talking about minutes.

Therefore, I'm asking the same issue once more: are my queries wrong or is there another parameter that I may have overlooked in the entity setups that explicit loading demands? Perhaps only when there is a single TransferService acting as the parent, rather than a list of TransferServices (50–100 in my case), and only a small number of entities that are related to it (in my case, I typically have 5–10 Details, each of which has 2-3 Rates, each of which has exactly one Period, etc.), is explicit loading beneficial?

1
0
8/17/2018 11:28:12 AM

Popular Answer

ZZZ_tmp
0
8/17/2018 1:13:28 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow