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
Question
Hello,
This probably isn't a bug, but found a slightly unexpected query translation in the example code.
The produced query is:
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 ofItem2Amount.The query I thought would be produced is the one produced by changing the left join into a regular (inner) join:
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:
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.Idor 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
Stack traces
Verbose output
EF Core version
10
Database provider
Npgsql.EntityFrameworkCore.PostgreSQL
Target framework
10
Operating system
No response
IDE
No response