Add .AsCte() an extension method on IQueryable<T> to generate named CTEs in EF Core SQL

[fiorantino]

What problem are you trying to solve?

Proposal: Add .AsCte() an extension method on IQueryable to generate named CTEs in EF Core SQL

Summary

This proposal introduces a new Extension for Entity Framework Core:

IQueryable<T>().AsCte(string? name = null)

Its purpose is to allow developers to explicitly mark a query source as a Common Table Expression (CTE), preserving the semantics and intention of the LINQ expression in the generated SQL.

AsCte() does not change EF Core’s semantics, tracking, or relational mapping.
It only affects SQL generation, replacing anonymous subqueries (t0, t1, t2, …) with readable, named CTEs.
##Summary
This proposal introduces a new LINQ operator for Entity Framework Core:

IQueryable<T>().AsCte(string? name = null)

Its purpose is to allow developers to explicitly mark a query source as a Common Table Expression (CTE), preserving the semantics and intention of the LINQ expression in the generated SQL.

AsCte() does not change EF Core’s semantics, tracking, or relational mapping.
It only affects SQL generation, replacing anonymous subqueries (t0, t1, t2, …) with readable, named CTEs.

Motivation

EF Core currently emits deeply nested subqueries with anonymous aliases such as:

SELECT …

FROM (SELECT … FROM EntityA) AS t0

LEFT JOIN (SELECT … FROM EntityB) AS t1 ON …

LEFT JOIN (SELECT … FROM EntityC) AS t2 ON …


…

LEFT JOIN (SELECT … FROM EntityZ) AS t27 ON …

in real-world systems, this makes EF-generated SQL extremely difficult to maintain, especially when the original developer is no longer available.

Anyone reading this SQL years later often asks:

“Who wrote this? Why is everything called t0, t1, t2…? What does any of this mean?”

This proposal solves that problem

Proposed Solution: IQueryable<T>().AsCte()

EF Core already knows everything needed to generate a CTE:

• the source entity
• the projection
• the filters
• the joins
• the cardinality
  the semantics of the LINQ expression

AsCte() simply tells EF Core:

“Emit this query source as a named CTE using the semantics you already have.”

Example
LINQ:

var q = db.Addresses
.Where(x => x.Active && !x.Disabled)
.Select(x => new { x.Id, x.Name })
.AsCte("ActiveAddresses");

Generated SQL:

WITH ActiveAddresses AS (
SELECT Id, Name
FROM tbAddress
WHERE Active = 1 AND Disabled = 0
)
SELECT *
FROM ActiveAddresses;

Generated SQL becomes:

WITH ActiveSites AS (…)

, Clients AS (…)

,Zones AS (…)

SELECT …

FROM ActiveSites

JOIN Clients ON …

JOIN Zones ON …

Anyone reading this SQL — even years later — immediately understands:

what each CTE represents

how the domain is structured

how the logic works

how to fix or optimize it

This is impossible with anonymous subqueries.

• This SQL is:
• readable
• semantic
• maintainable
• debuggable
• optimizable
  *self-documenting

Naming Behavior
If the developer provides a name
AsCte("ActiveAddresses")

EF uses:

WITH ActiveAddresses AS (...)

If no name is provided
AsCte()

EF generates:

WITH cte1 AS (…)

, cte2 AS (…)

, cte3 AS (…)

This mirrors EF’s existing aliasing behavior, but with CTEs instead of anonymous subqueries.

Benefits
Readability
Named CTEs expose the meaning of the query.
Aliases like ActiveAddresses, ValidCustomers, RecentOrders are self-explanatory.

Debuggability
Developers and DBAs can immediately understand:

what the CTE represents

what filters it applies

what projection it exposes

Maintainability
Future maintainers — even years later — can understand the intention behind the query.

Boost And Sql Optimization

CTEs are easier for SQL engines to optimize than deeply nested subqueries.
CTEs influence the execution plan differently than subqueries
A crucial difference between CTEs and subqueries is that CTEs participate in the optimizer as named relational sources, not as anonymous inline expressions.

This means the optimizer can:

• evaluate their cardinality independently
• estimate their cost separately
• reorder joins more aggressively
• choose different join strategies
• push predicates into the CTE
• or pull predicates out of the CTE

Why this matters

If a CTE is joined with a small table, the optimizer may:
choose a nested loop join instead of a hash join
reorder the join sequence
push filters into the CTE
treat the CTE as a “virtual table” with known statistics
generate a completely different execution plan than the equivalent subquery

Subqueries behave differently

Inline subqueries:are optimized in place
cannot be reordered as freely
often prevent predicate pushdown
hide cardinality information
restrict join reordering
may force the optimizer into a less efficient plan

Reusability
Multiple joins can reference the same named CTE.

Easy Implementation

Zero impact on EF semantics
AsCte() does not:
change LINQ semantics
It is purely a SQL generation hint.
EF Core Can Implement This Easily
AsCte() simply adds a flag to a query source:
“Emit this projection as a named CTE.”

The SQL generator already supports CTEs internally.
This feature requires only:

a new Extension
a new expression node
a small extension to the SQL generator
No architectural changes. No Mandatory , no Break

Simple Real-World Example

Consider a query with multiple joins:

db.Sites
.Where(x => x.Active)
.Select(x => new { x.Id, x.ClientId, x.ZoneId })
.AsCte("ActiveSites")
.Join(db.Clienti.AsCte("Clients"), c => c.ClientId, cl => cl.Id, (c, cl) => new { c, cl })
.Join(db.Zone.AsCte("Zones"), x => x.c.ZoneId, z => z.Id, (x, z) => new { x.c, x.cl, z });

Section: Referencing a CTE explicitly inside LINQ

A key part of this proposal is that a CTE created via AsCte() can be explicitly referenced in the rest of the LINQ expression.
This allows developers to treat the CTE as a named relational source, just like a table or view.

var q1 =
    (from a in db.xx
     where a.Active
     select new { a.Id, a.ClientId }
    ).AsCte("ActiveXX");   // CTE applied here
var q2 =
    from x in q1
    join c in db.cccc on x.ClientId equals c.Id
    select new { x, c };

WITH ActiveXX AS (
    SELECT Id, ClientId
    FROM xx
    WHERE Active = 1
)
SELECT …
FROM ActiveX
JOIN cccc ON ActiveX.ClientId = cccc.Id;

Conclusion

IQueryable().AsCte() is a small, safe, non-breaking addition that dramatically improves:

SQL readability
SQL optimization
debugging
maintainability
developer experience
DBA experience

It preserves developer intent in the generated SQL and eliminates the need to decipher dozens of anonymous subqueries.

This feature would be a valuable addition to EF Core

Author

Michele Fiorantino — Venezia, Italia
mail fiorantino@hotmail.com
linkedin.com/in/fiorantino

Describe the solution you'd like

No response

[MihuBot]

I'm a bot. Here is a possible related and/or duplicate issue (I may be wrong):

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