Entity Framework 6 bad SQL generation and performance while grouping and aggregating

Question

I have a case where I can't make Entity Framework build nice and fast sql for me.

My case is that I have three entities. Shop, Sale and SalesLine. Sale holds a transaction datetime, and the salesline holds quantity, prices etc.

I want to build a reporting showing some aggregated data per shop and day (stripping the time)

Here is what I do:

result = (
    from sale in ctx.Sales
    join salesline in ctx.SalesLines on sale.Id equals salesline.Sale_Id
    join shop in ctx.Shops on sale.Shop_Id equals shop.Id
    where sale.Shop_Id == shopId
    group sale by new
    {
        SalesDate = DbFunctions.TruncateTime(sale.TransactionDateTime),
        ShopName = sale.Shop.Name
    }
    into s
    let CountOfSales = s.Count()
    let CountOfSalesLines = s.Sum(x => x.SalesLines.Count())
    let SumOfQuantity = s.Sum(x => x.SalesLines.Sum(sl => sl.Quantity))
    let SumOfTotalPrice = s.Sum(x => x.SalesLines.Sum(sl => sl.TotalPrice))
    let SumOfTotalDiscount = s.Sum(x => x.SalesLines.Sum(sl => sl.TotalDiscount))
    let SumOfVAT = s.Sum(x => x.SalesLines.Sum(sl => sl.VAT))
    select new DailySalesDataModel
    {
        ShopNameWithLocation = s.Key.ShopName,
        SalesDate = s.Key.SalesDate.Value,
        CountOfSales = CountOfSales,
        CountOfSalesLines = CountOfSalesLines,
        SumOfQuantity = SumOfQuantity,
        SumOfTotalPrice = SumOfTotalPrice,
        SumOfTotalDiscount = SumOfTotalDiscount,
        SumOfVAT = SumOfVAT
    }
).OrderBy(nda => nda.SalesDate).AsNoTracking().ToList();

This results in following SQL which takes about 20 seconds to execute.

exec sp_executesql N'SELECT 
[Project11].[C2] AS [C1], 
[Project11].[Name] AS [Name], 
[Project11].[C1] AS [C2], 
[Project11].[C3] AS [C3], 
[Project11].[C4] AS [C4], 
[Project11].[C5] AS [C5], 
[Project11].[C6] AS [C6], 
[Project11].[C7] AS [C7], 
[Project11].[C8] AS [C8]
FROM ( SELECT 
    [Project10].[Name] AS [Name], 
    [Project10].[C2] AS [C1], 
    1 AS [C2], 
     CAST( [Project10].[C1] AS float) AS [C3], 
     CAST( [Project10].[C3] AS float) AS [C4], 
     CAST( [Project10].[C4] AS float) AS [C5], 
    [Project10].[C5] AS [C6], 
    [Project10].[C6] AS [C7], 
    [Project10].[C7] AS [C8]
    FROM ( SELECT 
        [Project9].[C1] AS [C1], 
        [Project9].[Name] AS [Name], 
        [Project9].[C2] AS [C2], 
        [Project9].[C3] AS [C3], 
        [Project9].[C4] AS [C4], 
        [Project9].[C5] AS [C5], 
        [Project9].[C6] AS [C6], 
        (SELECT 
            SUM([Filter10].[A1]) AS [A1]
            FROM ( SELECT 
                (SELECT 
                    SUM([Extent23].[VAT]) AS [A1]
                    FROM [dbo].[SalesLine] AS [Extent23]
                    WHERE [Extent20].[Id] = [Extent23].[Sale_Id]) AS [A1]
                FROM   [dbo].[Sale] AS [Extent20]
                INNER JOIN [dbo].[SalesLine] AS [Extent21] ON [Extent20].[Id] = [Extent21].[Sale_Id]
                INNER JOIN [dbo].[Shop] AS [Extent22] ON [Extent20].[Shop_Id] = [Extent22].[Id]
                WHERE ([Extent20].[Shop_Id] = @p__linq__0) AND (([Project9].[C2] = (convert (datetime2, convert(varchar(255), [Extent20].[TransactionDateTime], 102) ,  102))) OR (([Project9].[C2] IS NULL) AND (convert (datetime2, convert(varchar(255), [Extent20].[TransactionDateTime], 102) ,  102) IS NULL))) AND (([Project9].[Name] = [Extent22].[Name]) OR (1 = 0))
            )  AS [Filter10]) AS [C7]
        FROM ( SELECT 
            [Project8].[C1] AS [C1], 
            [Project8].[Name] AS [Name], 
            [Project8].[C2] AS [C2], 
            [Project8].[C3] AS [C3], 
            [Project8].[C4] AS [C4], 
            [Project8].[C5] AS [C5], 
            [Project8].[C6] AS [C6]
            FROM ( SELECT 
                [Project7].[C1] AS [C1], 
                [Project7].[Name] AS [Name], 
                [Project7].[C2] AS [C2], 
                [Project7].[C3] AS [C3], 
                [Project7].[C4] AS [C4], 
                [Project7].[C5] AS [C5], 
                (SELECT 
                    SUM([Filter8].[A1]) AS [A1]
                    FROM ( SELECT 
                        (SELECT 
                            SUM([Extent19].[TotalDiscount]) AS [A1]
                            FROM [dbo].[SalesLine] AS [Extent19]
                            WHERE [Extent16].[Id] = [Extent19].[Sale_Id]) AS [A1]
                        FROM   [dbo].[Sale] AS [Extent16]
                        INNER JOIN [dbo].[SalesLine] AS [Extent17] ON [Extent16].[Id] = [Extent17].[Sale_Id]
                        INNER JOIN [dbo].[Shop] AS [Extent18] ON [Extent16].[Shop_Id] = [Extent18].[Id]
                        WHERE ([Extent16].[Shop_Id] = @p__linq__0) AND (([Project7].[C2] = (convert (datetime2, convert(varchar(255), [Extent16].[TransactionDateTime], 102) ,  102))) OR (([Project7].[C2] IS NULL) AND (convert (datetime2, convert(varchar(255), [Extent16].[TransactionDateTime], 102) ,  102) IS NULL))) AND (([Project7].[Name] = [Extent18].[Name]) OR (1 = 0))
                    )  AS [Filter8]) AS [C6]
                FROM ( SELECT 
                    [Project6].[C1] AS [C1], 
                    [Project6].[Name] AS [Name], 
                    [Project6].[C2] AS [C2], 
                    [Project6].[C3] AS [C3], 
                    [Project6].[C4] AS [C4], 
                    [Project6].[C5] AS [C5]
                    FROM ( SELECT 
                        [Project5].[C1] AS [C1], 
                        [Project5].[Name] AS [Name], 
                        [Project5].[C2] AS [C2], 
                        [Project5].[C3] AS [C3], 
                        [Project5].[C4] AS [C4], 
                        (SELECT 
                            SUM([Filter6].[A1]) AS [A1]
                            FROM ( SELECT 
                                (SELECT 
                                    SUM([Extent15].[TotalPrice]) AS [A1]
                                    FROM [dbo].[SalesLine] AS [Extent15]
                                    WHERE [Extent12].[Id] = [Extent15].[Sale_Id]) AS [A1]
                                FROM   [dbo].[Sale] AS [Extent12]
                                INNER JOIN [dbo].[SalesLine] AS [Extent13] ON [Extent12].[Id] = [Extent13].[Sale_Id]
                                INNER JOIN [dbo].[Shop] AS [Extent14] ON [Extent12].[Shop_Id] = [Extent14].[Id]
                                WHERE ([Extent12].[Shop_Id] = @p__linq__0) AND (([Project5].[C2] = (convert (datetime2, convert(varchar(255), [Extent12].[TransactionDateTime], 102) ,  102))) OR (([Project5].[C2] IS NULL) AND (convert (datetime2, convert(varchar(255), [Extent12].[TransactionDateTime], 102) ,  102) IS NULL))) AND (([Project5].[Name] = [Extent14].[Name]) OR (1 = 0))
                            )  AS [Filter6]) AS [C5]
                        FROM ( SELECT 
                            [Project4].[C1] AS [C1], 
                            [Project4].[Name] AS [Name], 
                            [Project4].[C2] AS [C2], 
                            [Project4].[C3] AS [C3], 
                            [Project4].[C4] AS [C4]
                            FROM ( SELECT 
                                [Project3].[C1] AS [C1], 
                                [Project3].[Name] AS [Name], 
                                [Project3].[C2] AS [C2], 
                                [Project3].[C3] AS [C3], 
                                (SELECT 
                                    SUM([Filter4].[A1]) AS [A1]
                                    FROM ( SELECT 
                                        (SELECT 
                                            SUM([Extent11].[Quantity]) AS [A1]
                                            FROM [dbo].[SalesLine] AS [Extent11]
                                            WHERE [Extent8].[Id] = [Extent11].[Sale_Id]) AS [A1]
                                        FROM   [dbo].[Sale] AS [Extent8]
                                        INNER JOIN [dbo].[SalesLine] AS [Extent9] ON [Extent8].[Id] = [Extent9].[Sale_Id]
                                        INNER JOIN [dbo].[Shop] AS [Extent10] ON [Extent8].[Shop_Id] = [Extent10].[Id]
                                        WHERE ([Extent8].[Shop_Id] = @p__linq__0) AND (([Project3].[C2] = (convert (datetime2, convert(varchar(255), [Extent8].[TransactionDateTime], 102) ,  102))) OR (([Project3].[C2] IS NULL) AND (convert (datetime2, convert(varchar(255), [Extent8].[TransactionDateTime], 102) ,  102) IS NULL))) AND (([Project3].[Name] = [Extent10].[Name]) OR (1 = 0))
                                    )  AS [Filter4]) AS [C4]
                                FROM ( SELECT 
                                    [Project2].[C1] AS [C1], 
                                    [Project2].[Name] AS [Name], 
                                    [Project2].[C2] AS [C2], 
                                    [Project2].[C3] AS [C3]
                                    FROM ( SELECT 
                                        [Project1].[C1] AS [C1], 
                                        [Project1].[Name] AS [Name], 
                                        [Project1].[C2] AS [C2], 
                                        (SELECT 
                                            SUM([Filter2].[A1]) AS [A1]
                                            FROM ( SELECT 
                                                (SELECT 
                                                    COUNT(1) AS [A1]
                                                    FROM [dbo].[SalesLine] AS [Extent7]
                                                    WHERE [Extent4].[Id] = [Extent7].[Sale_Id]) AS [A1]
                                                FROM   [dbo].[Sale] AS [Extent4]
                                                INNER JOIN [dbo].[SalesLine] AS [Extent5] ON [Extent4].[Id] = [Extent5].[Sale_Id]
                                                INNER JOIN [dbo].[Shop] AS [Extent6] ON [Extent4].[Shop_Id] = [Extent6].[Id]
                                                WHERE ([Extent4].[Shop_Id] = @p__linq__0) AND (([Project1].[C2] = (convert (datetime2, convert(varchar(255), [Extent4].[TransactionDateTime], 102) ,  102))) OR (([Project1].[C2] IS NULL) AND (convert (datetime2, convert(varchar(255), [Extent4].[TransactionDateTime], 102) ,  102) IS NULL))) AND (([Project1].[Name] = [Extent6].[Name]) OR (1 = 0))
                                            )  AS [Filter2]) AS [C3]
                                        FROM ( SELECT 
                                            [GroupBy1].[A1] AS [C1], 
                                            [GroupBy1].[K1] AS [Name], 
                                            [GroupBy1].[K2] AS [C2]
                                            FROM ( SELECT 
                                                [Filter1].[K1] AS [K1], 
                                                [Filter1].[K2] AS [K2], 
                                                COUNT([Filter1].[A1]) AS [A1]
                                                FROM ( SELECT 
                                                    [Extent3].[Name] AS [K1], 
                                                    convert (datetime2, convert(varchar(255), [Extent1].[TransactionDateTime], 102) ,  102) AS [K2], 
                                                    1 AS [A1]
                                                    FROM   [dbo].[Sale] AS [Extent1]
                                                    INNER JOIN [dbo].[SalesLine] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Sale_Id]
                                                    INNER JOIN [dbo].[Shop] AS [Extent3] ON [Extent1].[Shop_Id] = [Extent3].[Id]
                                                    WHERE [Extent1].[Shop_Id] = @p__linq__0
                                                )  AS [Filter1]
                                                GROUP BY [K1], [K2]
                                            )  AS [GroupBy1]
                                        )  AS [Project1]
                                    )  AS [Project2]
                                )  AS [Project3]
                            )  AS [Project4]
                        )  AS [Project5]
                    )  AS [Project6]
                )  AS [Project7]
            )  AS [Project8]
        )  AS [Project9]
    )  AS [Project10]
)  AS [Project11]
ORDER BY [Project11].[C1] ASC',N'@p__linq__0 bigint',@p__linq__0=28

If I build the SQL in hand, I would do like this, and it executes in less than 1 second.

SELECT Shop.Name AS ShopName, convert (datetime2, convert(varchar(255), Sale.[TransactionDateTime], 102), 102) AS SaleDate, COUNT(DISTINCT Sale.Id) AS countOfSales, COUNT(DISTINCT SalesLine.Id) AS CountOfSalesLines, SUM(SalesLine.Quantity) AS SumOfQty, SUM(SalesLine.TotalPrice) AS SumPrice, SUM(SalesLine.TotalDiscount) AS SumDiscount, SUM(SalesLine.VAT) AS SumVat
FROM Sale INNER JOIN SalesLine ON Sale.Id = SalesLine.Sale_Id INNER JOIN Shop ON Sale.Shop_Id = Shop.Id
WHERE Shop.Id = 28
GROUP BY Shop.Name, convert (datetime2, convert(varchar(255), Sale.[TransactionDateTime], 102), 102)
ORDER BY convert (datetime2, convert(varchar(255), Sale.[TransactionDateTime], 102), 102)

My question is how I can design my Entity Framework query, in order to get a more clean and fast SQL generated.

I know that can query the database direct, but I would like to be type-safe and use the engine.

I have searched a lot online, but couldn't find a case matching this scenario.

I hope for help!

Answer 1

There are things that you need to consider when writing LINQ query. Always start with an entity that has foreign key. Stepping into the referenced entity will be faster because its primary key is already indexed. Check the solution below:

    var result = await ctx.SalesLines.Where(x => x.Sale.ShopId == 82).Select(x => new
    {
        ShopName = x.Sale.Shope.Name,
        SaleDate = x.TransactionDate,
        SaleID = x.Sale.ID,
        SalesLineID = x.ID,
        SalesQuantity = x.Quantity,
        SalesTotalPrice = x.TotalPrice,
        SalesDiscount = x.TotalDiscount,
        SalesVAT = x.VAT
    }).GroupBy(x => new { x.ShopName, x.SaleDate }).OrderBy(x => x.SaleDate).Select(p => new DailySalesDataModel
            {
                CountOfSales = p.Distinct(x => x.SaleID).Count(),
                CountOfSalesLines = p.ToList().Count(),
                SalesDate = p.Key.SaleDate,
                SumOfQuantity = p.Sum(x => x.SalesQuantity),
                SumOfTotalDiscount = p.Sum(x => x.SalesDiscount),
                SumOfTotalPrice = p.Sum(x => x.SalesTotalPrice),
                SumOfVAT = p.Sum(x => x.SalesVAT)
            }).ToListAsync();