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