Asp.net Core Linq query takes too much time

Question

I have a linq query which is takes 31 second. This is the first time that im getting that much late query and i dont know what to do. Let me show you to my query :

        public IEnumerable<WebFairField> WebFairFieldForFair(Guid ID)
        {
            return TradeTurkDBContext.WebFairField.Where(x => x.DataGuidID==ID)
             .Include(x => x.Category)
             //
             .Include(x=>x.FairSponsors)
             .ThenInclude(x=>x.Company)
             .ThenInclude(x=>x.FileRepos)
             //
             .Include(x=>x.WebFairHalls)
             .ThenInclude(x=>x.HallSeatingOrders)
             .ThenInclude(x=>x.Company)
             .ThenInclude(x=>x.FileRepos)
             //
             .Include(x=>x.HallExpertComments)
             .Include(x=>x.Products)
             .Include(x=>x.FairSponsors)
             .AsNoTracking().ToList();
        }

im sure about that this is a right query but i dont know why that query is tooking too much time.

Thanks for any help!!

Answer 1

It is called Cartesian Explosion. EF Core produces SQL which returns a lot of records which will be aggregated on the client side.

Schematically: FairSponsors * FairSponsor.Company.FileRepos * WebFairHalls * WebFairHall.HallSeatingOrders * WebFairHall.HallSeatingOrder.Company.FileRepos * HallExpertComments * Poducts * FairSponsors. Too much records, isn't?

EF Core has operator AsSplitQuery(). Try to apply to your query and probably it will speedup returning result, but not too much. Each collection requested in Include will produce additional query.

Also try to play with removing AsNoTracking() or adding AsNoTrackingWithIdentityResolution() - it is case when tracking may improve query speed.