SQL Server Recursive CTE

A) Simple SQL Server recursive CTE example

Weekday declaration:

[Tuple]
class Weekday
{
    public int N { get; }
    public String Name { get; }
}

Recursive CTE behaves like an iterator. ELINQ provides Current() method to access the "recursive member".

var query = DbContext.Set<Scalar<String>>()
                .Query((Scalar<String> alias) =>
                {
                    var weekday = SubQuery((Weekday alias) =>
                    {
                        var r = SELECT<Weekday>(0.@as(alias.N), DATENAME(DatePart.WEEKDAY, 0).@as(alias.Name));

                        UNION_ALL();

                        // Like in Enumerator, get the "current" object
                        var current = r.Current();
                        var n = current.N;

                        SELECT((n + 1, DATENAME(DatePart.WEEKDAY, n + 1)));
                        FROM(current);
                        WHERE(n < 6);

                        return r;
                    });

                    WITH(weekday);

                    var result = SELECT<Scalar<String>>(weekday.Name.@as(alias.Value));
                    FROM(weekday);

                    return result;
                });

foreach (var weekday in query)
    Console.WriteLine(weekday.Value);

B) Using a SQL Server recursive CTE to query hierarchical data

int? managerId = null; //or specific "highest" manager id

var query = DbContext.Staffs.Query(() =>
{
    var org = SubQuery((Staffs managers, Staffs employees) =>
    {
        var r = SELECT(managers);
        FROM(managers);
        WHERE(managers.ManagerId == managerId);

        UNION_ALL();

        var manager = r.Current();

        SELECT(employees);
        FROM(employees).JOIN(manager).ON(employees.Manager == manager);

        return r;
    });

    WITH(org);

    // At this point org "table" contains the employees we need
    // We can SELECT, JOIN, or filter it as any other table.

    var result = SELECT(org);
    FROM(org);

    return result;
});

foreach (var staff in query)
    Console.WriteLine((staff.StaffId, staff.FirstName, staff.ManagerId?.ToString() ?? "NULL"));

< BACK | HOME