Linq2db: [Feature] Common Table Expressions (CTE) support

Created on 25 Oct 2017 · 3Comments · Source: linq2db/linq2db

Originally feature were introduced in #534

For start, here is the great basis article: SQL Server CTE Basics
Also syntax and limitations can be found here: WITH common_table_expression (Transact-SQL)
Supported SQL Engines can be found here: Hierarchical and recursive queries - Common table expression

I have prepared proposal syntax how to define CTE in linq2db and it needs your review and suggestions.

CTE from IQueryable

It means that IQueryable can be converted to CTE later

var cteParent =
(
    from p in db.Parent
    where p.ParentID > 0
    select p
).AsCTE();

IQueryable converted to CTE that uses other CTE

var cteChildren =
(
    from c in db.Child
    join p in cteParent on c.ParentID equals p.ParentID
    where p.Value1 > 10
    group c by c.ParentID
    into g
    select new
    {
        ParentID = g.Key,
        Count    = g.Count()
    }
).AsCTE();

Query with CTE usage

Nothing special here, just use as IQueryable

var query = from p in cteParent
    join c in cteChildren on p.ParentID equals c.ParentID
    select new
    {
        p.ParentID,
        p.Value1,
        c.Count
    };

CRUD operations

Also no visual changes

cteParent.Insert(db.Parent, p => p);

var data = cteParent.ToArray();

cteParent
    .Set(p => p.Value1, p => p.Value1 + 1)
    .Update();

cteParent.Delete();

Updating CTE?

I think we do not need updating CTE it may confuse developers.

// this is not new CTE, it's query that uses CTE
var cteParent    = cteParent.Where(p => p.Parent < 100)

// this is new CTE that references previous CTE
var cteParentNew   = cteParent.Where(p => p.Parent < 100).AsCTE()

Recursive CTE

Tricky part, here we need to define class that will be mapped to CTE

public class CTEClass
{
    public int ChildID;
    public int Count;
}
...

var cteRecursive = db.GetCTE<CTEClass>(cte =>
    from c in db.Child
    join ac in cteChildren on c.ParentID equals ac.ParentID
    join ct in cte on c.ChildID equals ct.ChildID
    select new CTEClass
    {
        ChildID = ct.ChildID + 1,
        Count   = ct.Count + ac.Count
    }
);

New extension methods

public static class CTEExtensions
{
    public static IQueryable<T> AsCTE<T>(this IQueryable<T> source, string alias = null)
    {
        throw new NotImplementedException();
    }

    public static IQueryable<T> GetCTE<T>(this IDataContext db,
        [InstantHandle] Expression<Func<IQueryable<T>, IQueryable<T>>> cteExpression)
    {
        throw new NotImplementedException();
    }
}

What should be generated

WITH statement with CTE parts before original SQL.
CTE parts will be sorted in order of usage. One CTE can reference only preceding CTEs.
Circular references will be handled at run-time

feature

Source