Support for common table expressions (aka WITH) via special LINQ operator

[roji]

We could have a special queryable-returning function which we'd translate into a TableExpressionBase:

from foo in EF.With(ctx.Foos.Where(....))
from bar in EF.With(ctx.Bars.Where(....))
from baz in ctx.Bazes.Where(...)
...

... becomes:

WITH foo AS (
    SELECT * FROM Foos
    WHERE ...
), bar AS (
    SELECT * FROM Bars
    WHERE ...
),
SELECT * FROM Bazes WHERE ...

Do recursive CTEs as well.

Note: CTEs are also valid with WITH ... INSERT ... INTO, WITH ... DELETE, WITH ... UPDATE.

See also #29918 (embedded IQueryables to common table expressions)

Community implementations

• Linq to DB

[roji]

Note: all major databases support prefixing UPDATE/DELETE with CTEs (see #795 for bulk updates).

[roji]

Note: all databases - except SQL Server - support embedding CTEs within arbitrary subqueries inside the query;

SELECT * FROM (WITH t AS (SELECT 1) SELECT * FROM t) AS foo;

This effectively makes CTEs an optional prefix of SelectExpression.

In addition, SQL Server (but not other databases) requires that column names be explicitly specified when they're not named within the subquery:

WITH t AS (SELECT 1 AS q) SELECT * FROM t; -- Column name bubbles out of the subquery
WITH t (q) AS (SELECT 1) SELECT * FROM t; -- Column explicitly specified as part of the CTE syntax
WITH t AS (SELECT 1) SELECT * FROM t; -- Fails in SQL Server, works elsewhere (with unknown column name)

[mojtabakaviani]

@ajcvickers and @roji
I hope CTEs and window functions #12747 will be added to 10.0 milestone for boost performance of advanced queries with grouping and aggregation.

[Pepsi1x1]

+1 this would be great to see and would benefit us across a number of projects