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?

1
1
6/3/2018 8:20:47 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

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
    };
0
5/31/2018 1:10:48 PM

Related Questions

How to build several left join query in Entity Framework Core

Consider i have below entities:... public class Root { public long Id { get; set; } } public class School : Root { public long StudentId { get; set; } public Student Student { get; set; } public Teacher Teacher { get; set; } public long TeacherId { get; set; } } public class Stude...
c# entity-framework-core left-join
asked by dimitri

Entity Framework Core2 LINQ - each join runs as a separate query

I have something like this in my C# MVC controller:...from table1 in db.Table1.AsQueryable() join table2 in db.Table2.AsQueryable() on table1.Col1 equals table2.Col1 join table3 in db.table3.AsQueryable() on new { table2.Col2, table2.Col5 } equals new { table3.Col2, table3.Col5 } . . few more joins . . WHERE ...... select new {table1.Prop1, table2....
asp.net-core asp.net-mvc c# entity-framework-core
asked by curious

EF Core 2.1 Query - LEFT JOIN

I am trying to select the equivalent of...SELECT u.NodeId, u.Name, u.TierId, u.OrgCode, o.OrgName, gtu.GroupId as ParentGroupId FROM Unit u LEFT JOIN GroupToUnit gtu ON u.NodeId = gtu.NodeId JOIN Organisation o ON u.OrgCode = o.OrgCode ...My c# equivalent is...IList<OrgChartNode> unitNodes = _db.Units ...
c# ef-core-2.0 entity-framework-core
asked by Hecatonchires

How i can combine these Linq query and how improve query speed

I have this SQL script that I need to convert to Linq for use with EF Core:...SELECT A.*, B.* FROM [Orders] AS A JOIN (SELECT TOP 1 WITH TIES * FROM [OrderMeta] ORDER BY ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY PostDate DESC) ) AS B ON (b.OrderId = A.OrderId) ...Here is what I converted this to:...public async Ta...
c# entity-framework-core linq
asked by rikyotei

Linq Query With Multiple Joins Not Giving Correct Results

Using joins with LINQ method syntax is hard to read and error prone....Using joins with LINQ query syntax is better, but still error prone (you can join by the wrong key as you did) and does not give you information about join cardinality....The best for LINQ to Entities queries is to use navigation properties (as Gert Arnold suggested in the comme...
c# entity-framework entity-framework-6 linq
asked by Ivan Stoev

INNER Join and LEFT OUTER JOIN in EF-Core with Linq

I am trying to convert below SQL to Linq query in c# (.net core 2.1 and EF core 2.2)...SELECT TD.*, RD.Match FROM TransactionDetail TD INNER JOIN dbo.Measure M ON M.InternalID = TD.MetricCode LEFT OUTER JOIN ( SELECT tmp.ID, tmp.ReportingDate, 1 AS Match FROM tmp ...
.net-core c# entity-framework-core linq sql
asked by PUBG

How to translate this SQL query to a LINQ query in EF Core?

Of course there are no indicators with a value of TimeInsertedLocal that is larger than the largest value of TimeInsertedLocal of all Indicators....However, it might be that you have several indicators with a value equal to the largest value of TimeInsertedLocal....If that is the case, you need to make groups of Indicators with the same TimeInserte...
c# entity-framework-core linq sql
asked by Harald Coppoolse

One LINQ query to get counts from several entities when using entity framework core

Im working on the LINQ query which returns counts from several entities. I created this kind of query:...public async Task<(int UsersCount, int GuestUsersCount, int WorkItemsCount, int WorkGroupsCount)> GetSiteInfoAsync() { var counters = await (from user in Context.UserAccounts from guestUser in Context.GuestAccoun...
.net-core c# entity-framework-core linq
asked by GoldenAge

Create a left join query without navigation property

I would achieve this goal using EFCore method syntax...Given an user retrieve all roles and set IsInRole = 1 if the given user has the role, otherwise 0...in tsql is something like ...CREATE TABLE USER(USERNAME VARCHAR(200) PRIMARY KEY); CREATE TABLE ROLE(ROLENAME VARCHAR(200) PRIMARY KEY); CREATE TABLE USERROLE(USERNAME VARCHAR(200), ROLENAME VARC...
entity-framework entity-framework-core
asked by Leonardo Lurci

Linq inner query in join with entity framework, query error

I'm trying to mimic this SQL query for which I'm getting the expected results: ...In Linq query, but I'm getting some errors. This is my Linq translation: ... var customerAircraftVM = await ( from ca in _context.CustomerAircrafts join ac in _context.Aircrafts on ca.AircraftId equals ac.AircraftId into left...
entity-framework entity-framework-core linq linq-to-entities linq-to-sql
asked by Laziale

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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