.Net Core 3.1 Entity Framework Slow Query Problem

Question

There was slowness in my .net core webapi application, I reviewed and redesigned my queries to resolve this issue. The code snippet below was written to bring in sales, payments for sales, and sales territories.

I first include the condition query and then the sub-entities in the query. Then I choose which columns to work with ".Select()".

Then I want to combine this table with the name information in other tables. For example, information such as Customer Name, Product Name is in the other table.

The first block of code I share below is a slow running linq query.

result.Results = 
    (from s in context.Sales
    join sa in context.SaleProductServiceAreas on s.SaleId equals sa.SaleId
    join sp in context.SalePayments on s.SaleId equals sp.SaleId into spleft
    from sp in spleft.DefaultIfEmpty()
    join c in context.Customers on s.CustomerId equals c.CustomerId
    join p in context.ProductServices on s.ProductServiceId equals p.ProductServiceId
    where s.Date >= firstDayOfMonth && s.Date <= lastDayOfMonth
    group s by
    new
    {
        s.SaleId,
        s.CustomerId,
        CustomerNameSurname = c.Name + ' ' + c.Surname,
        ProductServiceName = p.Name,
        s.Date,
        s.Total,
        s.Session,
        p.ProductServiceId
    } into grp
    select new SaleJoinDto()
    {
        SaleId = grp.Key.SaleId,
        CustomerNameSurname = grp.Key.CustomerNameSurname,
        ProductServiceName = grp.Key.ProductServiceName,
        Total = grp.Key.Total,
        Date = grp.Key.Date,
        Session = grp.Key.Session,
        CustomerId = grp.Key.CustomerId,
        ProductServiceId = grp.Key.ProductServiceId,
        SaleProductServiceAreas = (from sps in context.SaleProductServiceAreas
                                    join spa in context.ProductServiceAreas on sps.ProductServiceAreaId equals spa.ProductServiceAreaId
                                    where sps.SaleId == grp.Key.SaleId
                                    select new SaleProductServiceAreaJoinDto()
                                    {
                                        SaleProductServiceAreaId = sps.SaleProductServiceAreaId,
                                        ProductServiceAreaName = spa.Name,
                                        ProductServiceAreaId = sps.ProductServiceAreaId,
                                        SaleId = sps.SaleId
                                    }).ToList(),
        SalePayments = (from spp in context.SalePayments
                        where spp.SaleId == grp.Key.SaleId
                        select new SalePaymentDto()
                        {

                            SaleId = spp.SaleId,
                            SalePaymentId = spp.SalePaymentId,
                            Total = spp.Total,
                            PaymentMethod = spp.PaymentMethod,
                            Date = spp.Date
                        }).ToList()

    }).ToList();

["NEW"] This query I wrote is the query I rewrite as a result of the articles I have shared below.

Document of Microsoft Link for query

using (var context = new AppDbContext())
{
    var result = new PagedResult<SaleJoinDto>();
    result.CurrentPage = pageNumber;
    result.PageSize = pageSize;
    var pageCount = (double)result.RowCount / pageSize;
    result.PageCount = (int)Math.Ceiling(pageCount);
    var skip = (pageNumber - 1) * pageSize;


    var firstDayOfMonth = new DateTime(date.Year, date.Month, 1);
    var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);

    result.Results = await context.Sales
        .Where(x => x.Date >= firstDayOfMonth && x.Date <= lastDayOfMonth)
        .Include(x => x.SalePayments)
        .Include(x => x.SaleProductServiceAreas)
    .Select(s => new Sale()
    {
        SaleId = s.SaleId,
        CustomerId = s.CustomerId,
        Date = s.Date,
        Total = s.Total,
        Session = s.Session,
        SalePayments = s.SalePayments,
        SaleProductServiceAreas = s.SaleProductServiceAreas
    })
    .Join(context.Customers, sales => sales.CustomerId, customers => customers.CustomerId, (sales, customers) => new
    {
        sales,
        customers
    })
    .Join(context.ProductServices, combinedSaleAndCus => combinedSaleAndCus.sales.ProductServiceId, product => product.ProductServiceId, (combinedSaleAndCus, product) => new SaleJoinDto()
    {
        SaleId = combinedSaleAndCus.sales.SaleId,
        CustomerId = combinedSaleAndCus.sales.CustomerId,
        Date = combinedSaleAndCus.sales.Date,
        Total = combinedSaleAndCus.sales.Total,
        Session = combinedSaleAndCus.sales.Session,
        CustomerNameSurname = combinedSaleAndCus.customers.Name,
        SalePayments = combinedSaleAndCus.sales.SalePayments.Select(x => new SalePaymentDto()
        {
            Date = x.Date,
            PaymentMethod = x.PaymentMethod,
            SaleId = x.SaleId,
            SalePaymentId = x.SalePaymentId,
            Total = x.Total
        }).ToList(),
        SaleProductServiceAreas = combinedSaleAndCus.sales.SaleProductServiceAreas.Join(context.ProductServiceAreas, sp => sp.ProductServiceAreaId, psa => psa.ProductServiceAreaId, (sp, psa) => new SaleProductServiceAreaJoinDto()
        {
            SaleProductServiceAreaId = sp.SaleProductServiceAreaId,
            ProductServiceAreaName = psa.Name
        }).ToList()
    })
    .ToListAsync();

    result.RowCount = result.Results.Count();

    result.Results = result.Results.OrderByDescending(x => x.Date).Skip(skip).Take(pageSize).ToList();

    return result;
}

The problem is I do get the following error when I rewrite it according to this new query. Where am I making a mistake in the query I just wrote?

{"The LINQ expression 'DbSet\r\n .Where(s => s.Date >= __firstDayOfMonth_0 && s.Date <= __lastDayOfMonth_1)\r\n .Join(\r\n outer: DbSet, \r\n inner: s => s.CustomerId, \r\n
outerKeySelector: c => c.CustomerId, \r\n innerKeySelector: (s, c) => new TransparentIdentifier<Sale, Customer>(\r\n Outer = s, \r\n Inner = c\r\n ))\r\n .Join(\r\n outer: DbSet, \r\n inner: ti => new Sale{ \r\n SaleId = ti.Outer.SaleId, \r\n CustomerId = ti.Outer.CustomerId, \r\n Date = ti.Outer.Date, \r\n
Total = ti.Outer.Total, \r\n Session = ti.Outer.Session, \r\n SalePayments = (MaterializeCollectionNavigation(\r\n
navigation: Navigation: Sale.SalePayments,\r\n
subquery: DbSet\r\n .Where(s0 => EF.Property(ti.Outer, "SaleId") != null && EF.Property(ti.Outer, "SaleId") == EF.Property(s0, "SaleId"))), \r\n SaleProductServiceAreas = (MaterializeCollectionNavigation(\r\n navigation: Navigation: Sale.SaleProductServiceAreas,\r\n subquery: DbSet\r\n .Where(s1 => EF.Property(ti.Outer, "SaleId") != null && EF.Property(ti.Outer, "SaleId") == EF.Property(s1, "SaleId"))) \r\n }\r\n .ProductServiceId, \r\n
outerKeySelector: p => p.ProductServiceId, \r\n
innerKeySelector: (ti, p) => new TransparentIdentifier<TransparentIdentifier<Sale, Customer>, ProductService>(\r\n Outer = ti, \r\n Inner = p\r\n ))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."}

Answer 1

I fixed the slow responsiveness of the app. I was running the application on windows host. I changed my hosting to Centos 7 which is a linux distribution. Then when I run the application on Centos 7, the application accelerated perfectly and took flight.

My advice to all .net Core app developers, migrate your apps to linux. It is built to run on .net core linux distributions.

I share the problems I encountered while migrating the application to linux and how I solved it in the link below.

.Net Core 3.1 deploy on Centos 7

Answer 2

Removed not needed joins and grouping. It should be faster.

var query =
    from s in context.Sales
    join c in context.Customers on s.CustomerId equals c.CustomerId
    join p in context.ProductServices on s.ProductServiceId equals p.ProductServiceId
    where s.Date >= firstDayOfMonth && s.Date <= lastDayOfMonth
    select new SaleJoinDto()
    {
        SaleId = s.SaleId,
        CustomerNameSurname = c.Name + ' ' + c.Surname,
        ProductServiceName = p.ProductServiceName,
        Total = s.Total,
        Date = s.Date,
        Session = s.Session,
        CustomerId = s.CustomerId,
        ProductServiceId = p.ProductServiceId,
        SaleProductServiceAreas = (from sps in context.SaleProductServiceAreas
                                    join spa in context.ProductServiceAreas on sps.ProductServiceAreaId equals spa.ProductServiceAreaId
                                    where sps.SaleId == s.SaleId
                                    select new SaleProductServiceAreaJoinDto()
                                    {
                                        SaleProductServiceAreaId = sps.SaleProductServiceAreaId,
                                        ProductServiceAreaName = spa.Name,
                                        ProductServiceAreaId = sps.ProductServiceAreaId,
                                        SaleId = sps.SaleId
                                    }).ToList(),
        SalePayments = (from spp in context.SalePayments
                        where spp.SaleId == s.SaleId
                        select new SalePaymentDto()
                        {

                            SaleId = spp.SaleId,
                            SalePaymentId = spp.SalePaymentId,
                            Total = spp.Total,
                            PaymentMethod = spp.PaymentMethod,
                            Date = spp.Date
                        }).ToList()

    };

result.Results = query.ToList();

Anyway, even joins can be simplified if you have navigation properties which are not shown in original question.