Linq query with several left join generate few query

Question

Linq query with several left join generate few query

asp.net c# entity-framework-core left-join linq

Question

I try write query by linq on asp core 2 with Entity framework Core and all ok, query return right result, but very slowly.

var Query1 =
        from demand in _prabacontext.Demand
        join demandspec in _prabacontext.DemandSpec on demand.Id equals demandspec.DemandId into demandspecGroup
        join manager in _prabacontext.Manager on demand.ManagerId equals manager.Id into managerGroup
        from m in managerGroup.DefaultIfEmpty()
        join contractor in _prabacontext.Contractor on demand.ContractorId equals contractor.Id into contractorGroup
        from c in contractorGroup.DefaultIfEmpty()
        join company in _prabacontext.Company on c.CompanyId equals company.Id into companyGroup
        from cm in companyGroup.DefaultIfEmpty()
    where demand.RegDate >= Convert.ToDateTime("2018-01-01T00:00:00")
        select new
        {
            demand.Id,
            demand.Id1,
            RegDate = demand.RegDate.ToString("yyyy-MM-dd"),
            demand.IsLgot,
            demand.ContractorId,
            demand.DocNumber,
            demand.DocStatus,
            demand.ManagerId,
            demand.Remark,
            StrSum = demandspecGroup.Sum(x => (decimal?)(x.Price * x.Quantity)) ?? 0,
            ContractorName = cm.Name,
            ManagerName = m.Name1//+' '+m.Name2+' '+m.Name3
        };

In debugger log this linq query translate into this SQL statement:

SELECT 
    "demand"."_ID" AS "Id0", 
    "demand"."Contractor_ID" AS "ContractorId", 
    "demand"."DocNumber", 
    "demand"."DocStatus", 
    "demand"."ID" AS "Id1", 
    "demand"."Insert_Date", 
    "demand"."Insert_User", 
    "demand"."IsLgot", 
    "demand"."IsTender", 
    "demand"."last_updated", 
    "demand"."Manager_ID" AS "ManagerId", 
    "demand"."RegDate", 
    "demand"."Remark" AS "Remark0", 
    "demandspec"."_ID", 
    "demandspec"."Commerc_ID", 
    "demandspec"."DeliveryDate", 
    "demandspec"."_Demand_ID", 
    "demandspec"."Demand_ID", 
    "demandspec"."FinType_ID", 
    "demandspec"."Goods2_ID", 
    "demandspec"."Goods_ID", 
    "demandspec"."GoodsUnion_ID", 
    "demandspec"."ID", 
    "demandspec"."last_updated", 
    "demandspec"."Price", 
    "demandspec"."Quantity", 
    "demandspec"."Remainder", 
    "demandspec"."Remark", 
    "demandspec"."Reserv"
FROM
    "Demand" AS "demand"
LEFT JOIN 
    "Demand_Spec" AS "demandspec" ON "demand"."_ID" = "demandspec"."_Demand_ID"
ORDER BY 
    "Id0"

SELECT "manager"."id", "manager"."name1"
FROM "manager" AS "manager"

SELECT "contractor"."id", "contractor"."company_id"
FROM "maxim"."contractor" AS "contractor"

SELECT "company"."id", "company"."name"
FROM "maxim"."company" AS "company"`

It returns data from query and programmatically join output data. But this table is very large. I know I can write a SQL query directly, but I want use linq.

How I can write a Linq query what translate into a SQL query with a few left joins?

Added:

In debug log I find such strings:

The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally

For every tables in query. How I can rewrite my query without this construction?

Ð Ð°Ð´Ð¸Ðº Ð¥Ð°Ð¹Ñ€ÑƒÐ»Ð»Ð¸Ð½

View more on Stack Overflow

Amy B · Accepted Answer · 5/31/2018 1:10:48 PM

where demand.RegDate >= Convert.ToDateTime("2018-01-01T00:00:00")

This seems to be missing in the TSQL output, so it must be evaluated locally. If that's evaluated locally, then the whole demand table is being loaded, which is why the query runs slowly.

Also, if you have navigation properties set up, then you don't need to write those joins.

Here's how I would write the same query:

DateTime regDate = Convert.ToDateTime("2018-01-01T00:00:00");

var query1 =
    from demand in _prabacontext.Demand
    where regDate <= demand.RegDate
    let demandSpecs = demand.DemandSpecs
    let manager = demand.Manager
    let company = demand.Contractor.Company
    select new
    {
        demand.Id,
        demand.Id1,
        RegDate = demand.RegDate.ToString("yyyy-MM-dd"),
        demand.IsLgot,
        demand.ContractorId,
        demand.DocNumber,
        demand.DocStatus,
        demand.ManagerId,
        demand.Remark,
        StrSum = demandSpecs.Sum(x => (decimal?)(x.Price * x.Quantity)) ?? 0,
        ContractorName = company.Name,
        ManagerName = manager.Name1//+' '+manager.Name2+' '+manager.Name3
    };

Linq query with several left join generate few query

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related