First Name	Last Name	Job Title	Favorite Color	Wars or Trek?	Secret Alias	Date of Birth	Dream Vacation City	GPA	Arbitrary Data
James	Matman	Chief Sandwich Eater	Lettuce Green	Trek	Digby Green	January 13, 1979	Gotham City	3.1	RBX-12
The	Tick	Crimefighter Sorta	Blue	Wars	John Smith	July 19, 1968	Athens	N/A	Edlund, Ben (July 1996).
Jokey	Smurf	Giving Exploding Presents	Smurflow	Smurf	Smurflane Smurfmutt	Smurfuary Smurfteenth, 1945	New Smurf City	4.Smurf	One
Cindy	Beyler	Sales Representative	Red	Wars	Lori Quivey	July 5, 1956	Paris	3.4	3451
Captain	Cool	Tree Crusher	Blue	Wars	Steve 42nd	December 13, 1982	Las Vegas	1.9	Under the couch

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.