Skip to content

Unexpected query translation with in-between projection and nullable foreign keys #38131

@edgar-tamm-uptime

Description

@edgar-tamm-uptime

Question

Hello,

This probably isn't a bug, but found a slightly unexpected query translation in the example code.

The produced query is:

SELECT t."Id" AS "ThingId", i."Amount" AS "ItemAmount", (
    SELECT COALESCE(sum(i0."Amount"), 0.0)
    FROM "Item2s" AS i0
    WHERE i0."ItemId" = i."Id" OR (i0."ItemId" IS NULL AND i."Id" IS NULL)) AS "Item2Amount"
FROM "Things" AS t
LEFT JOIN "Items" AS i ON t."Id" = i."ThingId"

The unexpected part is the additional null check OR (i0."ItemId" IS NULL AND i."Id" IS NULL) which includes unintended rows in the final value of Item2Amount.

The query I thought would be produced is the one produced by changing the left join into a regular (inner) join:

SELECT t."Id" AS "ThingId", i."Amount" AS "ItemAmount", (
    SELECT COALESCE(sum(i0."Amount"), 0.0)
    FROM "Item2s" AS i0
    WHERE i0."ItemId" = i."Id") AS "Item2Amount"
FROM "Things" AS t
INNER JOIN "Items" AS i ON t."Id" = i."ThingId"

I don't think there should be a difference whether using left or inner join, at least when the sum is calculated using a non-nullable property (maybe if I would join against a NULL column then one would expect the null addition), but maybe my intuition is wrong and this is the intended behaviour.

What I actually thought EF would produce is more like this:

SELECT t."Id" AS "ThingId", i."Amount" AS "ItemAmount", i."Item2Amount"
FROM "Things" AS t
LEFT JOIN (
     SELECT i."Id", i."ThingId", i."Amount", (SELECT sum(i0."Amount") FROM "Item2s" as I0 WHERE i0."ItemId" = i."Id") as "Item2Amount"
     FROM "Items" AS i
) AS i ON t."Id" = i."ThingId"

Thank you for any clarification. If this behaviour is intended, then I will just have to remember to add a null check even if in sql that check is unnecessary. At least I don't think there is any way to not have the additional OR condition (tried with y.ItemId!.Value == x.Id or even casting to int, but didn't remove the OR)?

I don't think this is related to Postgresql provider so created "issue" here.

Your code

using var db = new AppDbContext(contextOptions.Options);
db.Database.EnsureCreated();

var result = db.Things
    .LeftJoin(
        db.Items
            .Select(x => new
            {
                Item = x,
                Item2Total = (decimal?)db.Item2s
                    .Where(y => y.ItemId == x.Id)
                    .Sum(y => y.Amount),
            }),
        x => x.Id,
        y => y.Item.ThingId,
        (x, y) => new
        {
            ThingId = x.Id,
            ItemAmount = (decimal?)y!.Item.Amount,
            Item2Amount = y.Item2Total,
        })
    .ToList();

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions options) : base(options) { }

    public DbSet<Thing> Things { get; set; }
    public DbSet<Item> Items { get; set; }
    public DbSet<Item2> Item2s { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Thing>()
            .HasKey(x => x.Id);

        modelBuilder.Entity<Thing>()
            .HasMany<Item>()
            .WithOne()
            .HasForeignKey(x => x.ThingId);

        modelBuilder.Entity<Item>()
            .HasKey(x => x.Id);

        modelBuilder.Entity<Item>()
            .HasMany<Item2>()
            .WithOne()
            .HasForeignKey(x => x.ItemId);

        modelBuilder.Entity<Item2>()
            .HasKey(x => x.Id);

    }
}

public class Thing
{
    public int Id { get; set; }
}

public class Item
{
    public int Id { get; set; }
    public int ThingId { get; set; }
    public decimal Amount { get; set; }
}

public class Item2
{
    public int Id { get; set; }
    public int? ItemId { get; set; }
    public decimal Amount { get; set; }
}

Stack traces


Verbose output


EF Core version

10

Database provider

Npgsql.EntityFrameworkCore.PostgreSQL

Target framework

10

Operating system

No response

IDE

No response

Metadata

Metadata

Assignees

No one assigned

    Type

    No fields configured for Bug.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions