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!

I'd first try to remove all `let` clauses (and instead do it directly in `select new DailySalesDataModel`). — Evk, Feb 13 '18 at 09:58
That was how I started. I did like this: select new DailySalesDataModel { ShopName = s.Key.ShopName, SumOfTotalPrice = s.Sum(x => x.SalesLines.Sum(sl => sl.TotalPrice)) } — MLNDK, Feb 13 '18 at 10:05
Yes. Same sql generated and slooow it is. I was hoping to be able to create a more classis sql-statement instead of a subselect per aggregate — MLNDK, Feb 13 '18 at 10:29
I feel that your query can be improved, but it's hard to say how without having database to test (I don't mean you need to include that in question of course). Anyway, when you have troubles with perfomance - using raw sql is fine, I do this all the time with complex queries. EF is often just not capable of converting complex queries to something fast (and EF Core is much worse than EF 6 in that). — Evk, Feb 13 '18 at 11:11
It might be the way out. It's just tricky if someone refactors something, and the hardcoded query breaks... — MLNDK, Feb 13 '18 at 11:27
Sure, but sometimes you have to sacrifice something for perfomance. Also to reduce possibility of breaking - use `nameof` where possible: $`"select * from {nameof(Sale)} inner join {nameof(SaleLine)} ..."` — Evk, Feb 13 '18 at 11:30

Stanley Okpala Nwosa · Answer 1 · 2018-02-13T11:34:58.797

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();

This might work, but as I read it, I would load every row, and then do the aggregation in memory, right? I am working with millions of records... — MLNDK, Feb 13 '18 at 11:30
@MLNDK This looks good to me and should generate single SQL. Have you actually tried it? — Ivan Stoev, Feb 14 '18 at 12:00

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

1 Answers1