Major EntitySpaces Enhancements Coming

The SQL "Over" syntax is being added to EntitySpaces along with the ability to truly take advantage of aliased columns.

Aliased Column Enhancements

When you use the SQL "Over" syntax you need to give the derived column an alias in order to be able to use it later in your query. This is now supported by EntitySpaces. Notice the two esQueryItem variables at the beginning the source code below. These are provided to the .As() method as "out" parameters and used later throughout the query. For instance, notice how "rowNumber" is created in the nested query and then used in the outer Select() and the outer Where() clause as well.

SQL 'Over' Syntax Now Supported

Notice the Over.RowNumber() column in the nested query's Select() clause. We need to use this derived column later in the outer Select() and Where() clauses. Thus, we use the 'out' parameter in the .As() so we can use the aliased column later in our query.

C# Query Example

esQueryItem orderTotal = null;
esQueryItem rowNumber = null;

OrdersCollection coll = new OrdersQuery("o", out var o)
    .From(out var od, () =>
    {
        // Nested Query
        return new OrderDetailsQuery("od", out var subQuery)
        .Select
        (
            subQuery.OrderID,
            (subQuery.UnitPrice * subQuery.Quantity).Sum().As("OrderTotal", out orderTotal),
            subQuery.Over.RowNumber().OrderBy(subQuery.OrderID.Descending).As("RowNumber", out rowNumber)
        )
        .GroupBy(subQuery.OrderID);
    }).As("sub")
    .InnerJoin(o).On(o.OrderID == od.OrderID)
    .Select(o.CustomerID, o.OrderDate, orderTotal, rowNumber)
    .Where(orderTotal > 42 && rowNumber > 500)
    .ToCollection();

if (coll.Count > 0)
{
    // Then we loaded at least one record
}

The SQL Generated by the Query

Notice that the SQL generated by EntitySpaces is exactly what you'd expect. The columns alias 'sub' is used where needed only.

Resulting SQL Query

SELECT o.[customerid], 
       o.[orderdate], 
       sub.[ordertotal] AS 'OrderTotal', 
       sub.[rownumber]  AS 'RowNumber' 
FROM   
(
     SELECT 
        od.[orderid], 
        Sum(od.[unitprice] * od.[quantity]) AS 'OrderTotal', 
        Row_number() OVER(ORDER BY od.[orderid] DESC) AS RowNumber 
     FROM   [order details] od 
     GROUP  BY od.[orderid]
) AS sub 
INNER JOIN [orders] o  ON o.[orderid] = sub.[orderid] 
WHERE ( sub.[ordertotal] > @Expr1 AND sub.[rownumber] > @Expr2) 

List of Supported SQL Over Functions

These are the SQL Over functions supported by EntitySpaces.
See this link for more information => SELECT - OVER Clause (Transact-SQL).
These enhancements are initially coming for Microsoft SQL Server but will be added to the rest of the providers shortly.

Ranking Over Functions

• DENSE_RANK() OVER
• NTILE() OVER
• RANK() OVER
• ROW_NUMBER() OVER

Aggregate Over Functions

• COUNT() OVER
• COUNT_BIG() OVER
• AVG() OVER
• MIN() OVER
• MAX() OVER
• SUM() OVER
• STDDEV() OVER
• STDDEVP() OVER
• VAR() OVER
• VARP() OVER

Analytical Over Functions

• LAG() OVER
• LEAD() OVER
• CUM_DIST() OVER
• FIRST_VALUE() OVER
• LAST_VALUE() OVER
• PERCENTILE_CONT() OVER
• PERCENTILE_DISC() OVER

This query, although not realistic, demonstrates the syntax in a real live working query ...

OrdersCollection coll = new OrdersQuery("q", out var q)
.Select
(
    q.Over.RowNumber().OrderBy(q.EmployeeID.Descending).As("Alias1"),
    q.Over.RowNumber().PartitionBy(q.Freight.Sum() * 10).OrderBy(q.EmployeeID.Descending).As("Alias2"),
    q.Over.Rank().OrderBy(q.EmployeeID.Descending).As("Alias3"),
    q.Over.DenseRank().OrderBy(q.EmployeeID.Descending).As("Alias4"),
    q.Over.Ntile(4).OrderBy(q.EmployeeID.Descending).As("Alias5"),
    q.Over.Sum(q.Freight).PartitionBy(q.EmployeeID).OrderBy(q.EmployeeID.Descending).As("Alias6")
)
.GroupBy(q.EmployeeID, q.Freight)
.ToCollection();

This functionality will be released in the next 10 days or so. Stay tuned. See EntitySpaces for more information.