CTE.md

SQL Server CTE

A) Simple SQL Server CTE example

For CTE we often need a dedicated Type. Since it's used "inside" ELINQ it's not required to register it in DbContext. It's enough to attribute it with [Tuple]:

CTE is a subquery. After creation we need to pass it to the WITH() function and then use as usual:

Note, that C# string interpolation works correctly in ELINQ. Run the query to see the results.

B) Using a common table expression to make report averages based on counts

C) Using multiple SQL Server CTE in a single query example

SQL Server CTE has a limitation - it cannot be used inside a subquery. As a result we cannot Include() related entities with the results or use Skip() or Take(). Fortunately, unless the CTE is recursive, it can be re-written with a subquery. In ELINQ this is as simple as not calling the WITH() function:

---

< BACK | HOME