EF Core 3.1 Query Optimization

c# ef-core-3.1 entity-framework-core postgresql sql


I hear every now and then, that EF Core 3.1 is now capable of optimizing grouping and several aggregate functions like sum to execute on database. Why does my C# code sends this heavily unoptimized SQL code to the database server?

using var dbContext = new DatabaseContext();
var someTableData = await dbContext.SomeTable
    .GroupBy(x => x.Foobar)
    .Select(x => new { Foobar = x.Key, Quantity = x.Sum(y => y.Quantity) })
    .OrderByDescending(x => x.Quantity)

SQL (copied from Entity Framework log):

SELECT [...] FROM public."SomeTable"

As you can see: The query does not contain any ordering, grouping, summarizing, ...

I solved my problem by creating a view, since the original ef core query increased RAM usage of application by 2GB (in 2 second) since there were a lot of table entries. But my question remains: What did I do wrong? Is it because I am using PostgreSQL (Npgsql.EntityFrameworkCore.PostgreSQL v3.1.0)?

2/3/2020 8:37:08 AM

Popular Answer

I popped your query into a quick test harness (see project definition with all nuget package versions below):

public class SomeTable
    public int Id { get; set; }
    public int Foobar { get; set; }
    public int Quantity { get; set; }

class MyDbContext : DbContext
    public DbSet<SomeTable> SomeTables { get; set; }
    public static readonly LoggerFactory DbCommandConsoleLoggerFactory
        = new LoggerFactory(new[] {
            new ConsoleLoggerProvider ((category, level) =>
                category == DbLoggerCategory.Database.Command.Name &&
                level == LogLevel.Trace, true)
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        optionsBuilder.UseNpgsql("Server=...;Port=5432;Database=test;User Id=...;Password=...;")

class Program
    static void Main(string[] args)
        var context = new MyDbContext();
        var someTableData = context.SomeTables
                .GroupBy(x => x.Foobar)
                .Select(x => new { Foobar = x.Key, Quantity = x.Sum(y => y.Quantity) })
                .OrderByDescending(x => x.Quantity)
/*csproj file contents below:
<Project Sdk="Microsoft.NET.Sdk">


    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="3.1.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.1.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.1" />
    <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="1.1.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.1" />


I ended up getting the following SQL for respective providers:

SELECT TOP(@__p_0) [s].[Foobar], SUM([s].[Quantity]) AS [Quantity]
FROM [SomeTables] AS [s]
GROUP BY [s].[Foobar]
ORDER BY SUM([s].[Quantity]) DESC

SELECT s."Foobar", SUM(s."Quantity")::INT AS "Quantity"
FROM "SomeTables" AS s
GROUP BY s."Foobar"
ORDER BY SUM(s."Quantity")::INT DESC
LIMIT @__p_0

This makes me wonder if it may be your provider/EF specific versions that give you that result? Although looking at the diff between two npgsql releases I can't see anything relaetd to the issue, but I would suggest you try upgrading all EF-related packages to 3.1.1 and repeat your test.

2/3/2020 10:09:25 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow