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]:

[Tuple]
class SalesAmount : StaffSales
{
    public int Year { get; }
}

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

var query = DbContext.Set<StaffSales>()
                .Query((Products products) =>
                {
                    var salesAmounts = SubQuery((Orders orders, OrderItems items, Staffs staffs, SalesAmount alias) =>
                    {
                        var sum = SUM(items.Quantity * items.ListPrice * (1 - items.Discount));
                        var fullName = $"{staffs.FirstName} {staffs.LastName}";
                        var year = orders.OrderDate.Year;

                        var r = SELECT<SalesAmount>(fullName.@as(alias.Staff),
                            sum.@as(alias.Sales),
                            year.@as(alias.Year));
                        FROM(orders).JOIN(items).ON(items.Order == orders).JOIN(staffs).ON(staffs == orders.Staff);
                        GROUP(BY(fullName), BY(year));
                        return r;
                    });

                    WITH(salesAmounts);

                    var result = SELECT<StaffSales>(salesAmounts.Staff.@as(), salesAmounts.Sales.@as());
                    FROM(salesAmounts);
                    WHERE(salesAmounts.Year == 2018);

                    return result;
                });

foreach (var staffSales in query)
    Console.WriteLine((staffSales.Staff, staffSales.Sales));

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

var year = 2018;

var query = DbContext.Set<Scalar<int>>()
    .Query((Scalar<int> alias) =>
    {

        var cteSales = SubQuery((Orders order) =>
        {
            var count = SELECT<Scalar<int>>(COUNT().@as(alias.Value));
            FROM(order);
            WHERE(order.OrderDate.Year == year);
            GROUP(BY(order.StaffId));
            return count;
        });

        WITH(cteSales);

        var r = SELECT<Scalar<int>>(AVG(cteSales.Value).@as(alias.Value));
        FROM(cteSales);
        return r;
    })
    .AsEnumerable();

Console.WriteLine(query.Single().Value);

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

var query = DbContext.Set<CTECategoryCounts>()
                .Query(() =>
                {

                    var categoryCounts = SubQuery((Products p,
                        Categories cat,
                        CTECategoryCounts alias) =>
                    {

                        var r = SELECT<CTECategoryCounts>(cat.CategoryId.@as(alias.CategoryId),
                            cat.CategoryName.@as(alias.CategoryName),
                            COUNT().@as(alias.ProductCount));

                        FROM(p).JOIN(cat).ON(p.Category == cat);

                        GROUP(BY(cat.CategoryId), BY(cat.CategoryName));

                        return r;
                    });

                    var categorySales = SubQuery((OrderItems oi,
                        Products p,
                        Orders o,
                        CTECategorySales alias) =>
                    {

                        var catId = p.Category.CategoryId;
                        var sales = SUM(oi.Quantity * oi.ListPrice * (1 - oi.Discount));

                        var r = SELECT<CTECategorySales>(catId.@as(alias.CategoryId), sales.@as(alias.Sales));

                        FROM(oi).JOIN(p).ON(p == oi.Product).JOIN(o).ON(o == oi.Order);
                        WHERE(o.OrderStatus == 4); // completed
                        GROUP(BY(catId));

                        return r;
                    });

                    WITH(categoryCounts, categorySales);

                    var r1 = SELECT(categoryCounts, categorySales.Sales.@as());

                    FROM(categoryCounts)
                        .JOIN(categorySales)
                        .ON(categorySales.CategoryId == categoryCounts.CategoryId);

                    ORDER(BY(categoryCounts.CategoryName));

                    return r1;
                });

foreach (var categoryCounts in query)
    Console.WriteLine((categoryCounts.CategoryId, categoryCounts.CategoryName, categoryCounts.ProductCount, categoryCounts.Sales));

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:

var query = DbContext.Set<CategoryCounts>()
                .Query(() =>
                {

                    var categoryCounts = SubQuery((Products p,
                        Categories cat,
                        CategoryCounts alias) =>
                    {

                        var r = SELECT<CategoryCounts>(cat.CategoryId.@as(alias.Category.CategoryId),
                            COUNT().@as(alias.ProductCount));

                        FROM(p).JOIN(cat).ON(p.Category == cat);

                        GROUP(BY(cat.CategoryId));

                        return r;
                    });

                    var categorySales = SubQuery((OrderItems oi,
                        Products p,
                        Orders o,
                        CTECategorySales alias) =>
                    {

                        var catId = p.Category.CategoryId;
                        var sales = SUM(oi.Quantity * oi.ListPrice * (1 - oi.Discount));

                        var r = SELECT<CTECategorySales>(catId.@as(alias.CategoryId), sales.@as(alias.Sales));

                        FROM(oi).JOIN(p).ON(p == oi.Product).JOIN(o).ON(o == oi.Order);
                        WHERE(o.OrderStatus == 4); // completed
                        GROUP(BY(catId));

                        return r;
                    });

                    // WITH(categoryCounts, categorySales);

                    var r1 = SELECT(categoryCounts, categorySales.Sales.@as());

                    FROM(categoryCounts)
                        .JOIN(categorySales)
                        .ON(categorySales.CategoryId == categoryCounts.Category.CategoryId);

                    return r1;
                })
                .Include(cc => cc.Category)
                .OrderBy(cc => cc.Category.CategoryName);

foreach (var categoryCounts in query.Take(3))
    Console.WriteLine((categoryCounts.Category.CategoryId, categoryCounts.Category.CategoryName, categoryCounts.ProductCount,
        categoryCounts.Sales));

< BACK | HOME