How to force delete COALESCE function in query. C# EntityFramework 7.0.2 LINQ

Question

Ok, I have such SQL code:

DECLARE  @documentId INT = 8
SELECT 
    i.[status]      AS [status],
    i.[barcode]     AS [barcode],
    i.[containerId] AS [containerTypeId],
    (
        SUM(ISNULL(lp.[Weight], 0) * il.[factCount]) + 
        ISNULL(lpt.[Weight], 0)
    )               AS [weight],
    i.[comment]     AS [comment]
FROM [Items]                   AS i    WITH(NOLOCK)
LEFT JOIN [dbo].[ItemsLines]   AS il   WITH(NOLOCK) ON i.[id]          = il.[itemsId] 
LEFT JOIN [dbo].[LagerOptions] AS lp   WITH(NOLOCK) ON lp.[lagerId]    = il.[lagerId]
LEFT JOIN [dbo].[LagerOptions] AS lpt  WITH(NOLOCK) ON lpt.[lagerId]   = i.[containerId]
WHERE @documentId = i.[documentId] 
GROUP BY i.[status], i.[barcode], i.[containerId], i.[comment], lpt.[Weight]

The most similar code I've written in LINQ is:

var lots = await (

    from i in _dbContext.Items
    join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptTable 
    from lpt in lptTable.DefaultIfEmpty()

    // The sum of all lagers for current lot
    let lotWeight = (
        from il in _dbContext.ItemsLines
        join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpTable 
        from lp in lpTable.DefaultIfEmpty()
        where il.ItemsId == i.Id
        select (il.FactCount * lp.Weight.GetValueOrDefault(0))
    ).Sum()

    where i.DocumentsId == documentId
    select new
    {
        Status = i.Status,
        Barcode = i.Barcode,
        ContainerTypeId = i.ContainerId,
        // total weight with container Weight
        Weight = lotWeight + lpt.Weight.GetValueOrDefault(0),
        //
        Comment = i.Comment
    }
).ToListAsync();

For SQL query the results can contain empty lots or lots with null-weight lagers (problem lagers) that makes "Weight" field of lot is equal NULL that helps detect problem lots.

But when I check the C# LINQ code EntityFramework create a COALESCE() function over SUM() function and the query converted to SQL looks like this:

SELECT [t].[status] AS [Status], [t].[barcode] AS [Barcode], [t].[containerId] AS [ContainerTypeId], (
    SELECT COALESCE(SUM([t0].[factCount] * COALESCE([l0].[Brutto], 0.0)), 0.0)
    FROM [dbo].[ItemsLines] AS [t0]
    LEFT JOIN [dbo].[LagerOptions] AS [l0] ON [t0].[lagerId] = [l0].[lagerId]
    WHERE [t0].[itemsId] = [t].[id]) + COALESCE([l].[weight], 0.0) AS [Weight], [t].[comment] AS [Comment]
FROM [dbo].[Items] AS [t]
LEFT JOIN [dbo].[LagerOptions] AS [l] ON [t].[containerId] = [l].[lagerId]
WHERE [t].[documentId] = @__documentId_0

As a result, the weight of the problem lot will be equal to the container weight of this lot. I can solve the problem with crutch methods, but I'm sure that there is a simple solution, which, unfortunately, I did not find.

I tryed to add different checks on null and rewrite the query for different JOIN patterns, but the main problem - EntityFramework create a COALESCE() function over SUM() function. And I don't know how to fix it in root. I work with C# EF approximately 1 month. EF 7.0.2

Help me, please.

!!EDITED

Correct LINQ query looks like this (Thank, Svyatoslav Danyliv):

var query = 
    from i in _dbContext.Items
    join il in _dbContext.ItemsLines on i.Id equals il.ItemsId into ilj
    from il in ilj.DefaultIfEmpty()
    join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpj
    from lp in lpj.DefaultIfEmpty()
    join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptj
    from lpt in lptj.DefaultIfEmpty()
    where i.DocumentsId == documentId
    group new { lp, il, lpt } by new { i.Status, i.Barcode, i.ContainerId, i.Comment, lpt.Weight } into g
    select new 
    {
        g.Key.Status,
        g.Key.Barcode,
        ContainerTypeId = g.Key.ContainerId,
        Weight = g.Sum(x => ((decimal?)x.lp.Weight) ?? 0 * x.il.FactCount) + g.Key.Weight ?? 0
        g.Key.Comment,
    };

But the query converted to SQL looks like this:

SELECT 
    [t].[status] AS [Status], 
    [t].[barcode] AS [Barcode], 
    [t].[containerId] AS [ContainerTypeId], 
    COALESCE(SUM(COALESCE([l].[weight], 0.0) * [t0].[factCount])), 0.0) + 
    COALESCE([l0].[weight], 0.0) AS [Weight], 
    [t].[comment] AS [Comment]
FROM [dbo].[Items] AS [t]
LEFT JOIN [dbo].[ItemsLines] AS [t0] ON [t].[id] = [t0].[itemsId]
LEFT JOIN [dbo].[LagerOptions] AS [l] ON [t0].[lagerId] = [l].[lagerId]
LEFT JOIN [dbo].[LagerOptions] AS [l0] ON [t].[containerId] = [l0].[lagerId]
WHERE [t].[documentsId] = @__documentId_0
GROUP BY [t].[status], [t].[barcode], [t].[containerId], [t].[comment], [l0].[weight]

EntityFramework create a COALESCE() function over SUM() function. How to remove this?

Answer 1

This should equivalent LINQ query to your SQL. I do not understand why instead of grouping you have forced LINQ Translator to generate another query.

var query = 
    from i in _dbContext.Items
    join il in _dbContext.ItemsLines on i.Id equals il.ItemsId into ilj
    from il in ilj.DefaultIfEmpty()
    join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpj
    from lp in lpj.DefaultIfEmpty()
    join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptj
    from lpt in lptj.DefaultIfEmpty()
    where i.DocumentsId == documentId
    group new { lp, il, lpt } by new { i.Status, i.Barcode, i.ContainerId, i.Comment, lpt.Weight } into g
    select new 
    {
        g.Key.Status,
        g.Key.Barcode,
        ContainerTypeId = g.Key.ContainerId,
        Weight = g.Sum(x => ((double?)x.lp.Weight) ?? 0 * x.il.FactCount) + g.Key.Weight ?? 0
        g.Key.Comment,
    };