EntitySpaces 2020.2.1 Released

In this release of EntitySpaces the "esAlias" class is introduced and the OVER Clause is officially released for Microsoft SQL Server. An instance of the esAlias class can be obtained when aliasing a column via the .As() syntax. We are going to introduce these new features by demonstrating how they come to the rescue in query we need to write.

The Problem

We need to obtain a result set to see the sales amount for each company per year. This part is easy, we can use a simply GroupBy() to obtain the sales amount per each company per year. However, we also need a running cumulative amount per company and the total amount of sales per the the entire company, and for this we need to use the new OVER clause syntax. Notice below how the Cumulative Amount is adding the Amount per each year of the company, while the Total Amount.

                                              Total            Cumulative   Total
CompanyName                          Period   Items  Amount    Amount       Amount 
------------------------------------ -------  ------ --------  -----------  -------
Alfreds Futterkiste                  1997     6      2022.50   2022.50      4273.00
Alfreds Futterkiste                  1998     6      2250.50   4273.00      4273.00
Ana Trujillo Emparedados y helados   1996     2        88.80     88.80      1402.95
Ana Trujillo Emparedados y helados   1997     4       799.75    888.55      1402.95
Ana Trujillo Emparedados y helados   1998     4       514.40   1402.95      1402.95
Antonio Moreno Taquería              1996     1       403.20    403.20      7023.97
Antonio Moreno Taquería              1997     14     5960.77   6363.97      7023.97
Antonio Moreno Taquería              1998     2       660.00   7023.97      7023.97

In fact, we need a nested query to get the entire result set. So we'll attack this query in two steps.

Step 1

Let's create what will become our nested query. We need to obtain the CompanyName, Period, TotalItems, and Amount grouped by CompanyName and the YEAR. We start from the Orders table and join with the Customers table to get the CompanyName and the Order Details table to get the line item information for each order.

OrdersCollection coll = new OrdersQuery("o", out var o)
.InnerJoin<CustomersQuery>("c", out var c).On(c.CustomerID == o.CustomerID)
.InnerJoin<OrderDetailsQuery>("od", out var od).On(od.OrderID == o.OrderID)
.Select
(
    // We're going to grab the aliased columns here for re-use in the outer query later
    c.CompanyName.As("CompanyName"),
    o.OrderDate.DatePart("year").As("Period"),
    o.Count().As("TotalItems"),
    ((1.00M - od.Discount) * od.UnitPrice * od.Quantity).Cast(esCastType.Decimal, 19, 2)
        .Sum().Round(2).As("Amount")
)
.GroupBy(c.CompanyName, o.OrderDate.DatePart("year"))
.OrderBy(c.CompanyName.Ascending, o.OrderDate.DatePart("year").Ascending)
.ToCollection<OrdersCollection>();

The SQL generated by the above query is as follows:

SELECT
   c.[CompanyName] AS 'CompanyName',
   DATEPART(year, o.[OrderDate]) AS 'Period',
   COUNT(*) AS 'TotalItems',
   CAST(SUM(ROUND((((1.00 - od.[Discount]) * od.[UnitPrice]) * od.[Quantity]), 2)) 
      AS decimal(19, 2)) AS 'Amount' 
FROM
   [Orders] o 
   INNER JOIN [Customers] c ON c.[CustomerID] = o.[CustomerID] 
   INNER JOIN [Order Details] od ON od.[OrderID] = o.[OrderID] 
GROUP BY c.[CompanyName], DATEPART(year, o.[OrderDate]) 
ORDER BY c.[CompanyName] ASC, DATEPART(year, o.[OrderDate]) ASC

The result set is as follows:

                                                     Total
CompanyName                              Period      Items       Amount
---------------------------------------- ----------- ----------- -----------
Alfreds Futterkiste                      1997        6           2022.50
Alfreds Futterkiste                      1998        6           2250.50
Ana Trujillo Emparedados y helados       1996        2             88.80
Ana Trujillo Emparedados y helados       1997        4            799.75
Ana Trujillo Emparedados y helados       1998        4            514.40
Antonio Moreno Taquería                  1996        1            403.20
Antonio Moreno Taquería                  1997        14          5960.77
Antonio Moreno Taquería                  1998        2            660.00

Step 2

Okay, so step one is complete. But now we need to obtain a cumulative running total sales amount for the company as each year progresses and we need the total amount of sales for the entire company. These two values are not "per" year so we are going to nest the query above into yet another query and use the OVER clause syntax to get the two additional values. Let's see how that is done.

Don't let this query overwhelm you. If you could collapse the nested query all you would see is the Select() and the OrderBy() on the nested queries columns. Take a close look at how we obtain the "CumulativeAmount" and "TotalAmount" values. Also notice the use of esAlias and that it's knows what alias to use, in this case "sub".

Our full query.

// We grab these aliases in the nested query via "out" parameters
esAlias aliasCompany = null, aliasPeriod = null, aliasAmount = null, aliasItemCount = null;

OrdersCollection coll = new OrdersQuery("q", out var q)
.From<OrdersQuery>(out var sub, () => // mimic a CTE
{
    // Nested Query from Step 1 above
    return new OrdersQuery("o", out var o)
    .InnerJoin<CustomersQuery>("c", out var c).On(c.CustomerID == o.CustomerID)
    .InnerJoin<OrderDetailsQuery>("od", out var od).On(od.OrderID == o.OrderID)
    .Select
    (
        // We're going to grab the aliased columns here for re-use in the outer query later
        c.CompanyName.As("CompanyName", out aliasCompany),
        o.OrderDate.DatePart("year").As("Period", out aliasPeriod),
        o.Count().As("TotalItems", out aliasItemCount),
        ((1.00M - od.Discount) * od.UnitPrice * od.Quantity).Cast(esCastType.Decimal, 19, 2)
            .Sum().Round(2).As("Amount", out aliasAmount)
    )
    .GroupBy(c.CompanyName, o.OrderDate.DatePart("year"));
}).As("sub")
// Now act on "sub" query columns using the OVER syntax
.Select(
   aliasCompany(), aliasPeriod(), aliasAmount(), aliasItemCount(),  
   q.Over.Sum(aliasAmount()).PartitionBy(aliasCompany()).OrderBy(aliasPeriod().Ascending).Rows
      .UnBoundedPreceding.As("CumulativeAmount"),
   q.Over.Sum(aliasAmount()).PartitionBy(aliasCompany()).As("TotalAmount")
)
.OrderBy(aliasCompany().Ascending, aliasPeriod().Ascending)
.ToCollection<OrdersCollection>();

if(coll.Count > 0)
{
    // we loaded data
}

The SQL generated by the above query is as follows:

SELECT
   sub.[CompanyName],
   sub.[Period],
   sub.[Amount],
   sub.[TotalItems],
   -- Notice the OVER() clause usage below
   SUM([Amount]) OVER( PARTITION BY [CompanyName] ORDER BY sub.[Period] ASC ROWS UNBOUNDED PRECEDING )
       AS 'CumulativeAmount',
   SUM([Amount]) OVER( PARTITION BY [CompanyName] ) AS 'TotalAmount' 
FROM
   (
      SELECT
         c.[CompanyName] AS 'CompanyName',
         DATEPART(year, o.[OrderDate]) AS 'Period',
         COUNT(*) AS 'TotalItems',
         CAST(SUM(ROUND((((1.00 - od.[Discount]) * od.[UnitPrice]) * od.[Quantity]), 2)) AS decimal(19, 2))
             AS 'Amount' 
      FROM [Orders] o 
      INNER JOIN [Customers] c ON c.[CustomerID] = o.[CustomerID] 
      INNER JOIN [Order Details] od ON od.[OrderID] = o.[OrderID] 
      GROUP BY c.[CompanyName], DATEPART(year, o.[OrderDate])
   )
   AS sub 
ORDER BY sub.[CompanyName] ASC, sub.[Period] ASC

Our final result set is as follows:

                                              Total            Cumulative   Total
CompanyName                          Period   Items  Amount    Amount       Amount 
------------------------------------ -------  ------ --------  -----------  -------
Alfreds Futterkiste                  1997     6      2022.50   2022.50      4273.00
Alfreds Futterkiste                  1998     6      2250.50   4273.00      4273.00
Ana Trujillo Emparedados y helados   1996     2        88.80     88.80      1402.95
Ana Trujillo Emparedados y helados   1997     4       799.75    888.55      1402.95
Ana Trujillo Emparedados y helados   1998     4       514.40   1402.95      1402.95
Antonio Moreno Taquería              1996     1       403.20    403.20      7023.97
Antonio Moreno Taquería              1997     14     5960.77   6363.97      7023.97
Antonio Moreno Taquería              1998     2       660.00   7023.97      7023.97

That is some pretty powerful functionality that EntitySpaces is packing. Both the full SQL Over Clause syntax along with the full SQL Window Frame syntax are supported.