Creating an ordinary table is usually not an option in real app since there might be a collision if queries are executed in parallel. The common technique is creating a temporary table per session. This is exactly what we do here:
const String SALES_SUMMARY = "#sales_summary";
DbContext.Database.Execute((OrderItems i, Products p, Brands b, Categories c) =>
{
var salesSummary = ToTable<SalesSummaryByYear>(SALES_SUMMARY);
SELECT<SalesSummaryByYear>(b.BrandName.@as(salesSummary.Brand),
c.CategoryName.@as(salesSummary.Category),
p.ModelYear.@as(salesSummary.ModelYear),
ROUND(SUM(i.Quantity * i.ListPrice * (1 - i.Discount)), 0).@as(salesSummary.Sales))
.INTO(salesSummary);
FROM(i).JOIN(p).ON(i.Product == p).JOIN(b).ON(p.Brand == b).JOIN(c).ON(p.Category == c);
GROUP(BY(b.BrandName), BY(c.CategoryName), BY(p.ModelYear));
});
var query = DbContext.Set<SalesSummaryByYear>()
.Query(() =>
{
var salesSummary = ToTable<SalesSummaryByYear>(SALES_SUMMARY);
var result = SELECT(salesSummary);
FROM(salesSummary);
return result;
})
.OrderBy(c => c.Brand)
.ThenBy(c => c.Category)
.ThenBy(c => c.ModelYear);
foreach (var salesSummary in query.Take(3))
Console.WriteLine((salesSummary.Brand, salesSummary.Category, salesSummary.ModelYear, salesSummary.Sales));
Run the example above and see that 2 separate queries were executed. The first creates the
#sales_summarytemporary table and the second uses it.
Temporary table is potentially complex. The same result can be achieved with a subquery. Also, if the same subquery is going to be used several times, it can be refactored to a separate function. Run the following and see same results as above:
private static SalesSummaryByYear GetSalesSummaryTable()
{
return SubQuery((OrderItems i, Products p, Brands b, Categories c, SalesSummaryByYear alias) =>
{
var r = SELECT<SalesSummaryByYear>(b.BrandName.@as(alias.Brand),
c.CategoryName.@as(alias.Category),
p.ModelYear.@as(alias.ModelYear),
ROUND(SUM(i.Quantity * i.ListPrice * (1 - i.Discount)), 0).@as(alias.Sales));
FROM(i).JOIN(p).ON(i.Product == p).JOIN(b).ON(p.Brand == b).JOIN(c).ON(p.Category == c);
GROUP(BY(b.BrandName), BY(c.CategoryName), BY(p.ModelYear));
return r;
});
}
// Don't change method name
public void SetupSalesSummaryTest()
{
var query = DbContext.Set<SalesSummaryByYear>()
.Query(() => SelectAll(GetSalesSummaryTable()))
.OrderBy(ss => ss.Brand)
.ThenBy(ss => ss.Category)
.ThenBy(ss => ss.ModelYear);
foreach (var salesSummary in query.Take(3))
Console.WriteLine((salesSummary.Brand, salesSummary.Category, salesSummary.ModelYear, salesSummary.Sales));
}
GROUPING functionAll the features in one example:
var query = DbContext.Set<SalesGrouping>()
.Query((SalesGrouping alias) =>
{
var salesSummary = GetSalesSummaryTable();
var result = SELECT<SalesGrouping>(GROUPING(salesSummary.Brand).@as(alias.GroupingBrand),
GROUPING(salesSummary.Category).@as(alias.GroupingCategory),
salesSummary.Brand.@as(alias.Brand),
salesSummary.Category.@as(alias.Category),
SUM(salesSummary.Sales).@as(alias.Sales));
FROM(salesSummary);
GROUP(BY(GROUPING_SETS(SET(salesSummary.Brand, salesSummary.Category),
SET(salesSummary.Brand),
SET(salesSummary.Category),
SET())));
return result;
})
.OrderBy(ss => ss.Brand)
.ThenBy(ss => ss.Category);
foreach (var salesSummary in query.Take(3))
Console.WriteLine((salesSummary.GroupingBrand, salesSummary.GroupingCategory, salesSummary.Brand ?? "NULL", salesSummary.Category ?? "NULL",
salesSummary.Sales));