Subquery uses ungrouped column from outer query

[mojtabakaviani]

Some scenarios need group by with complex key or calc with functions PostgreSQL not support ungrouped column from outer query and must use subquery:

from i in db.Invoices
group i by i.History.Month into g
select new
{
	Month = g.Key,
	Total = g.Sum(p => p.Amount),
	Payment = Payments.Where(p => p.History.Month == g.Key).Sum(p => p.Amount)
}

generated sql:

SELECT date_part('month', i.history)::INT AS "Month", COALESCE(SUM(i.amount), 0.0) AS "Total", (
    SELECT COALESCE(SUM(p.amount), 0.0)
    FROM payments AS p
    WHERE date_part('month', p.history)::INT = date_part('month', i.history)::INT) AS "Payment"
FROM invoices AS i
GROUP BY date_part('month', i.history)::INT

correct sql:

SELECT i0."Month", i0."Total", (SELECT COALESCE(SUM(p.amount), 0.0) FROM payments AS p
    WHERE date_part('month', p.history)::INT = i0."Month"::INT) AS "Payment"
FROM (SELECT date_part('month', i.history)::INT AS "Month", COALESCE(SUM(i.amount), 0.0) AS "Total"
	  FROM invoices AS i
      GROUP BY date_part('month', i.history)::INT) AS i0

error: subquery uses ungrouped column "i.history" from outer query

EF Core version:
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL 6.0.0
Target framework: .NET 6.0
Operating system: Windows 11
IDE: VSCode

[roji]

Please reopen this in the EF Core repo - it doesn't seem related to Npgsql.

[mojtabakaviani]

With SQL Server provider work properly, but PostgreSQL not support ungrouped column from outer query. EF Core convert subquery to joins, if avoid this convert, can write query same this:

from s in (from i in Invoices
		   group i by i.History.Month into g
		   select new
		   {
			   Month = g.Key,
			   Total = g.Sum(p => p.Amount)
		   })
select new
{
	s.Month,
	s.Total,
	Payment = Payments.Where(p => p.History.Month == s.Month).Sum(p => p.Amount)
}

sql:

SELECT date_part('month', i.history)::INT AS "Month", COALESCE(SUM(i.amount), 0.0) AS "Total", (
    SELECT COALESCE(SUM(p.amount), 0.0)
    FROM payments AS p
    WHERE date_part('month', p.history)::INT = date_part('month', i.history)::INT) AS "Payment"
FROM invoices AS i
GROUP BY date_part('month', i.history)::INT

if want open issue in EF Core repo must open with new title.
or commented for issues dotnet/efcore#27133 or dotnet/efcore#16409

[roji]

Thanks @mojtabakaviani, I can see this happening as you said - note that the query works fine on SQL Server, SQLite and MariaDB.

I've opened dotnet/efcore#27266 to discuss this with the EF team, we'll see what can be done about it.

[roji]

@mojtabakaviani closing as this is planned to be fixed on the EF Core side for 7.0 - thanks again for flagging this!

[roji]

Duplicate of dotnet/efcore#27266