Avoid automatic convert nested queries to joins

[mojtabakaviani]

EF Core automatic convert subquery to joins, for example PostgreSQL not support ungrouped column from outer query and not execute this generated query, I open issue in npgsql/efcore.pg#2236 and @roji new issue #27266.

some advanced scenarios same this need nested queries:

from s in (from i in db.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 = db.Payments.Where(p => p.History.Month == s.Month).Sum(p => p.Amount)
}

generated sql is:

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 is:

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

if need add other projection over this, shouldn't convert nested to joins

[roji]

@mojtabakaviani is this a duplicate of #27266? Do yuo have other scenarios in mind?

[mojtabakaviani]

approximately, but nested queries have many other scenarios that group by with complex key and subquery are under this. for calc stats aggregations need many nested queries for unrelated data.

[roji]

Sure, but unless I'm mistaken the only issue is with subqueries referring to ungrouped columns in a GROUP BY query, right? If so, that's what #27266 already tracks?

[mojtabakaviani]

lets me to more explain

a scenario use subquery in select same this:

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 = db.Payments.Where(p => p.History.Month == g.Key).Sum(p => p.Amount)
}

because use function in group by, PostgreSQL not not support ungrouped column from outer query. but run in SQL Server that cover in #27266.

and other scenario select over nested queries is same this:

from s in (from i in db.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,
	Dollar= db.Payments.Where(p => p.History.Month == s.Month and p.Currency == 1).Sum(p => p.Amount),
        Euro= db.Payments.Where(p => p.History.Month == s.Month and p.Currency == 2).Sum(p => p.Amount)
}

and others complex and multi level nested queries, EF core convert more nested queries to joins. if you think #27266 tracks nested queries, close this issue.

[smitpatel]

This is not converting nested query to joins. The subquery returns single scalar result and is placed directly into projection because a scalar result is projectable.