EF Core 6, Invalid Generated SQL (JOIN Statement) (Big wrong result)

[mohsentaei]

First Check Generated SQL Script:

SELECT COALESCE(SUM(CASE
    WHEN ([u].[UserType] <> 2) AND ([c].[SettlementType] = 2) THEN [b].[Amount]
END), 0.0) AS [CashPayment], COALESCE(SUM(CASE
    WHEN [c0].[SettlementType] = 1 THEN [b].[Amount]
END), 0.0) AS [CreditPayment]
FROM [Books] AS [b]
INNER JOIN [User] AS [u] ON [b].[CreatorUserId] = [u].[Id]
INNER JOIN [User] AS [u0] ON [b].[OwnerUserId] = [u0].[Id]
LEFT JOIN [Corporation] AS [c] ON [u0].[CorporationId] = [c].[Id]
LEFT JOIN [Corporation] AS [c0] ON [u].[CorporationId] = [c].[Id]
GROUP BY [b].[Kind]

Check the last join ([c0] not used in any 'ON' part):

LEFT JOIN [Corporation] AS [c0] ON [u].[CorporationId] = [c].[Id]

[c] used instead!!!
Also I think second left join is useless totally in this query.

So the query result will be wrong (The result will multiply by [Corporation] counts)

I'm trying to migrate a project to EF core. but it is a silent error that can not found without check the query result manually.
So its blocked my way.

The Models

public class Book
    {
        public Guid Id { get; set; }
        public decimal Amount { get; set; }
        public byte Kind { get; set; }
        public int OwnerUserId { get; set; }
        public int CreatorUserId { get; set; }
        public virtual User OwnerUser { get; set; }
        public virtual User CreatorUser { get; set; }
    }

    public class User
    {
        public int Id { get; set; }
        public int UserType { get; set; }
        public int? CorporationId { get; set; }
        public virtual Corporation Corporation { get; set; }
    }

    public class Corporation
    {
        public int Id { get; set; }
        public int SettlementType { get; set; }
    }

The DbContext

    public class XContext : DbContext
    {
        public XContext(DbContextOptions<XContext> options)
        : base(options)
        {
        }
        public virtual DbSet<Book> Books { get; set; }
        public virtual DbSet<User> User { get; set; }
        public virtual DbSet<Corporation> Corporation { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Book>(entity =>
            {
                entity.HasOne(d => d.OwnerUser)
                    .WithMany()
                    .HasForeignKey(d => d.OwnerUserId);

                entity.HasOne(d => d.CreatorUser)
                   .WithMany()
                   .HasForeignKey(d => d.CreatorUserId);
            });

            modelBuilder.Entity<User>(entity =>
            {
                entity.HasOne(d => d.Corporation)
                    .WithMany()
                    .HasForeignKey(d => d.CorporationId);

            });
        }
    }

The Query

  var qry = context.Books.GroupBy(x => x.Kind).Select(k => new
            {
                CashPayment = k.Where(x => x.CreatorUser.UserType != 2 && x.OwnerUser.Corporation.SettlementType == 2).Sum(x => x.Amount),
                CreditPayment = k.Where(x => x.OwnerUser.Corporation.SettlementType == 1).Sum(x => x.Amount),
            });

Expected Query

SELECT COALESCE(SUM(CASE
    WHEN ([u].[UserType] <> 2) AND ([c].[SettlementType] = 2) THEN [b].[Amount]
END), 0.0) AS [CashPayment], COALESCE(SUM(CASE
    WHEN [c].[SettlementType] = 1 THEN [b].[Amount]
END), 0.0) AS [CreditPayment]
FROM [Books] AS [b]
INNER JOIN [User] AS [u] ON [b].[CreatorUserId] = [u].[Id]
INNER JOIN [User] AS [u0] ON [b].[OwnerUserId] = [u0].[Id]
LEFT JOIN [Corporation] AS [c] ON [u0].[CorporationId] = [c].[Id]
GROUP BY [b].[Kind]

Include provider and version information

EF Core version: 6.0.1
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET 6.0)
Operating system: Windows 10
IDE: Visual Studio 2022

[smitpatel]

Duplicate of #27083