Linq query With Sub Query, Count, Max, Sum, left join whith extended on

Question

I am trying to create a linq query.

My SQL query is the following:

select 
    count(*) as Customers, 
    sum(IsActive) ActiveCustomers, 
    Sum(DiscountAmount) DiscountAmount 
from (
    select 
        Customer.Oid,
        Max(Case when DocumentHeader.DocumentNumber>0 then 1 else 0 end) IsActive,
        Sum(DocumentHeader.PointsDiscountAmount) DiscountAmount
    from 
        Customer left join
        DocumentHeader on customer.Oid=DocumentHeader.Customer  and DocumentHeader.FinalizedDate>='20160101' and DocumentHeader.FinalizedDate<='20180131' 
    where isnull(customer.CardID, '')>''
    group by Customer.Oid
) xxx

until now i have tryed this but it does not give me the write response

var result = from customers in Customers 
             where !string.IsNullOrEmpty(customers.CardID)
             join documentHeaders in DocumentHeaders on  customers.Oid  equals documentHeaders.Customer.Oid
             where documentHeaders.FinalizedDate>=apo 
                   && documentHeaders.FinalizedDate<=eos 
                select new
                {
                     Oid = customers.Oid,
                     documentHeaders.DocumentNumber,
                     PromotionValue = documentHeaders.DocumentType.ValueFactor
                 };
return result.GroupBy(g=> new {g.Oid}).Select(s => new { groupid=1, Oid= s.Key.Oid, MaxDoc = s.Max(a => a.DocumentNumber)>0?1:0, PromotionValue = s.Max(a => a.PromotionValue) });

Thanks

Answer 1

The query should be:

DateTime startDate = new DateTime(2016, 01, 01);
DateTime endDate = new DateTime(2018, 01, 31);

var result1 = from c in db.Customers
              join d in db.DocumentHeaders.Where(x => x.FinalizedDate >= startDate && x.FinalizedDate <= endDate) on c.Oid equals d.Customer.Oid into dtemp
              from d in dtemp.DefaultIfEmpty()
              where !string.IsNullOrEmpty(c.CardID)
              group d by c.Oid into cd
              select new { Oid = cd.Key, IsActive = cd.Max(x => x.DocumentNumber > 1 ? 1 : 0), DiscountAmount = cd.Sum(x => x.PointsDiscountAmount) };

var result2 = (from x in result1
               group x by 1 into y // see https://stackoverflow.com/a/45144198/613130
               select new { Customers = y.Count(), ActiveCustomers = y.Sum(z => z.IsActive), DiscountAmount = y.Sum(z => z.DiscountAmount) }
               ).FirstOrDefault();

Note that I split it into two nested queries (that in truth will be unified by LINQ). See the comment about where I found the trick about group x by 1 into y.

Tests done show that EF Core still isn't good enough for this query: from the group by upward everything is done locally, so much data is moved between the SQL Server and the .NET:

SELECT [c2].[Oid], [c2].[CardID], [t2].[Oid], [t2].[CustomerOid], [t2].[DocumentNumber], [t2].[FinalizedDate], [t2].[PointsDiscountAmount]
FROM [Customers] AS [c2]
LEFT JOIN (
    SELECT [x2].[Oid], [x2].[CustomerOid], [x2].[DocumentNumber], [x2].[FinalizedDate], [x2].[PointsDiscountAmount]
    FROM [DocumentHeaders] AS [x2]
    WHERE ([x2].[FinalizedDate] >= @__startDate_0) AND ([x2].[FinalizedDate] <= @__endDate_1)
) AS [t2] ON [c2].[Oid] = [t2].[CustomerOid]
WHERE [c2].[CardID] IS NOT NULL AND (([c2].[CardID] <> N'') OR [c2].[CardID] IS NULL)
ORDER BY [c2].[Oid]

The EF "classic" instead can translate it:

SELECT 
    [Limit1].[C1] AS [C1], 
    [Limit1].[C2] AS [C2], 
    [Limit1].[C3] AS [C3], 
    [Limit1].[C4] AS [C4]
    FROM ( SELECT TOP (1) 
        [GroupBy2].[K1] AS [C1], 
        [GroupBy2].[A1] AS [C2], 
        [GroupBy2].[A2] AS [C3], 
        [GroupBy2].[A3] AS [C4]
        FROM ( SELECT 
            [Project1].[K1] AS [K1], 
            COUNT([Project1].[A1]) AS [A1], 
            SUM([Project1].[A2]) AS [A2], 
            SUM([Project1].[A3]) AS [A3]
            FROM ( SELECT 
                1 AS [K1], 
                1 AS [A1], 
                [Project1].[C1] AS [A2], 
                [Project1].[C2] AS [A3]
                FROM ( SELECT 
                    [GroupBy1].[A1] AS [C1], 
                    [GroupBy1].[A2] AS [C2]
                    FROM ( SELECT 
                        [Filter1].[K1] AS [K1], 
                        MAX([Filter1].[A1]) AS [A1], 
                        SUM([Filter1].[A2]) AS [A2]
                        FROM ( SELECT 
                            [Extent1].[Oid] AS [K1], 
                            CASE WHEN ([Extent2].[DocumentNumber] > 1) THEN 1 ELSE 0 END AS [A1], 
                            [Extent2].[PointsDiscountAmount] AS [A2]
                            FROM  [dbo].[Customers] AS [Extent1]
                            LEFT OUTER JOIN [dbo].[DocumentHeaders] AS [Extent2] ON ([Extent2].[FinalizedDate] >= @p__linq__0) AND ([Extent2].[FinalizedDate] <= @p__linq__1) AND ([Extent1].[Oid] = [Extent2].[CustomerOid])
                            WHERE  NOT (([Extent1].[CardID] IS NULL) OR (( CAST(LEN([Extent1].[CardID]) AS int)) = 0))
                        )  AS [Filter1]
                        GROUP BY [K1]
                    )  AS [GroupBy1]
                )  AS [Project1]
            )  AS [Project1]
            GROUP BY [K1]
        )  AS [GroupBy2]
    )  AS [Limit1]

Note that in EF "classic" the ON condition of the LEFT JOIN is translated as a single condition (both Oid and FinalizedDate checks), while in EF Core they are split.