GroupBy with joined table produces sub optimal query

[domagojmedo]

When aggregating on values from a joined table EF produces what appears to be not optimal query. Repro code:

using Microsoft.EntityFrameworkCore;

var ctx = new BloggingContext();

var q1 = ctx.Posts
    .Where(x => x.Blog.Rating > 5)
    .GroupBy(x => x.CreatedDate.Date)
    .Select(x =>
        new
        {
            x.Key,
            PostRating = x.Sum(x => x.Rating)
        })
    .ToQueryString();

var q2 = ctx.Posts
    .Where(x => x.Blog.Rating > 5)
    .GroupBy(x => x.CreatedDate.Date)
    .Select(x =>
        new
        {
            x.Key,
            PostRating = x.Sum(x => x.Rating),
            BlogRating = x.Sum(x => x.Blog.Rating)
        })
    .ToQueryString();

Console.WriteLine("Hello, World!");

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql("Host=my_host;Database=my_db;Username=my_user;Password=my_pw");
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int Rating { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int Rating { get; set; }

    public DateTime CreatedDate { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

q1 is

SELECT t."Key", COALESCE(sum(t."Rating"), 0)::int AS "PostRating"
FROM (
    SELECT p."Rating", date_trunc('day', p."CreatedDate", 'UTC') AS "Key"
    FROM "Posts" AS p
    INNER JOIN "Blogs" AS b ON p."BlogId" = b."BlogId"
    WHERE b."Rating" > 5
) AS t
GROUP BY t."Key"

but q2 is

SELECT t."Key", COALESCE(sum(t."Rating"), 0)::int AS "PostRating", (
    SELECT COALESCE(sum(b0."Rating"), 0)::int
    FROM (
        SELECT p0."PostId", p0."BlogId", p0."Content", p0."CreatedDate", p0."Rating", p0."Title", b1."BlogId" AS "BlogId0", b1."Rating" AS "Rating0", b1."Url", date_trunc('day', p0."CreatedDate", 'UTC') AS "Key"
        FROM "Posts" AS p0
        INNER JOIN "Blogs" AS b1 ON p0."BlogId" = b1."BlogId"
        WHERE b1."Rating" > 5
    ) AS t0
    INNER JOIN "Blogs" AS b0 ON t0."BlogId" = b0."BlogId"
    WHERE t."Key" = t0."Key" OR (t."Key" IS NULL AND t0."Key" IS NULL)) AS "BlogRating"
FROM (
    SELECT p."Rating", date_trunc('day', p."CreatedDate", 'UTC') AS "Key"
    FROM "Posts" AS p
    INNER JOIN "Blogs" AS b ON p."BlogId" = b."BlogId"
    WHERE b."Rating" > 5
) AS t
GROUP BY t."Key"

Isn't filtering twice not optimal? Shouldn't q2 be something like

SELECT t."Key", COALESCE(sum(t."Rating"), 0)::int AS "PostRating", COALESCE(sum(t."BlogRating"), 0)::int AS "BlogRating"
FROM (
    SELECT p."Rating", b."Rating" AS "BlogRating", date_trunc('day', p."CreatedDate", 'UTC') AS "Key"
    FROM "Posts" AS p
    INNER JOIN "Blogs" AS b ON p."BlogId" = b."BlogId"
    WHERE b."Rating" > 5
) AS t
GROUP BY t."Key"

We ran into this issue with a bigger table (30m rows), ended up doing 2 queries where we group values from each table separately and merge it in code.

Copy of https://github.com/npgsql/efcore.pg/issues/3160 @roji pointed me to main repo

[jlunman]

I have this same problem. I need to aggregate multiple properties from related entities, and rather than do a single join in the FROM clause, it is generating a subquery for each aggregate.

[roji]

@jlunman are your query and SQL the same as the above? If not, please post them here so we can test that scenario as well.

[jlunman]

Yes. Similar result. Here's my (contrived) test case:

    public class User
    {
        [Key]
        public int UserId { get; set; }

       // ... other properties ...

        public string Department { get; set; }
        public bool IsActive { get; set; }

        public virtual UserRoles Roles { get; set; }
    }

    public class UserRoles
    {
        [Key]
        public int UserId { get; set; }

        public bool UserAdmin { get; set; }
        public bool DealAdmin { get; set; }
    }

    public class TestDbContext : DbContext
    {
        public virtual DbSet<User> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=DUMMY_SERVER;Database=DUMMY_DB;");
        }
    }

The relationship between User and UserRoles is one-to-one. I want to count the number of Active users, UserAdmins, and DealAdmins, grouped by Department.

        var dbContext = new TestDbContext();

        var query = from u in dbContext.Users
                    group u by u.Department into grp
                    select new
                    {
                        NumActiveUsers = grp.Sum(u => u.IsActive ? 1 : 0),
                        NumUserAdmins = grp.Sum(u => u.Roles.UserAdmin ? 1 : 0),
                        NumDealAdmins = grp.Sum(u => u.Roles.DealAdmin ? 1 : 0)
                    };

This query results in the following SQL:

SELECT COALESCE(SUM(CASE
    WHEN [u].[IsActive] = CAST(1 AS bit) THEN 1
    ELSE 0
END), 0) AS [NumActiveUsers], (
    SELECT COALESCE(SUM(CASE
        WHEN [u1].[UserAdmin] = CAST(1 AS bit) THEN 1
        ELSE 0
    END), 0)
    FROM [Users] AS [u0]
    LEFT JOIN [UserRoles] AS [u1] ON [u0].[UserId] = [u1].[UserId]
    WHERE [u].[Department] = [u0].[Department] OR ([u].[Department] IS NULL AND [u0].[Department] IS NULL)) AS [NumUserAdmins], (
    SELECT COALESCE(SUM(CASE
        WHEN [u3].[DealAdmin] = CAST(1 AS bit) THEN 1
        ELSE 0
    END), 0)
    FROM [Users] AS [u2]
    LEFT JOIN [UserRoles] AS [u3] ON [u2].[UserId] = [u3].[UserId]
    WHERE [u].[Department] = [u2].[Department] OR ([u].[Department] IS NULL AND [u2].[Department] IS NULL)) AS [NumDealAdmins]
FROM [Users] AS [u]
GROUP BY [u].[Department]

I would expect something structured more like the following, with the LEFT JOIN in the FROM clause:

Select
    u.Department,
    SUM(Case When u.IsActive = 1 Then 1 Else 0 End) As NumActiveUsers,
    SUM(Case When u2.UserAdmin = 1 Then 1 Else 0 End) As NumUserAdmins,
    SUM(Case When u2.DealAdmin = 1 Then 1 Else 0 End) As NumDealAdmins
From
    Users u 
    Left Join UserRoles u2 on u2.UserId = u.UserId
Group By u.Department

Note that if I eliminate the one-to-one relationship and combine all the properties into a single entity:

    public class User
    {
        [Key]
        public int UserId { get; set; }

        public string Department { get; set; }

        public bool IsActive { get; set; }
        public bool UserAdmin { get; set; }
        public bool DealAdmin { get; set; }
    }

Then this query:

        var query = from u in dbContext.Users
                    group u by u.Department into grp
                    select new
                    {
                        NumActiveUsers = grp.Sum(u => u.IsActive ? 1 : 0),
                        NumUserAdmins = grp.Sum(u => u.UserAdmin ? 1 : 0),
                        NumDealAdmins = grp.Sum(u => u.DealAdmin ? 1 : 0)
                    };

behaves as expected

SELECT COALESCE(SUM(CASE
    WHEN [u].[IsActive] = CAST(1 AS bit) THEN 1
    ELSE 0
END), 0) AS [NumActiveUsers], COALESCE(SUM(CASE
    WHEN [u].[UserAdmin] = CAST(1 AS bit) THEN 1
    ELSE 0
END), 0) AS [NumUserAdmins], COALESCE(SUM(CASE
    WHEN [u].[DealAdmin] = CAST(1 AS bit) THEN 1
    ELSE 0
END), 0) AS [NumDealAdmins]
FROM [Users] AS [u]
GROUP BY [u].[Department]

[roji]

Thanks - we'll likely do a GroupBy push for 10, hopefully this will make it in!