Home

These changes are about a week away, going to be extremely powerful. This is actual working code ...

"Over" methods are coming !!!

OrdersCollection coll = new OrdersQuery("q", out var q)
.Select
(
    q.Over.RowNumber().OrderBy(q.EmployeeID.Descending).As("RNUM1"),
    q.Over.RowNumber().PartitionBy(q.Freight.Sum() * 10).OrderBy(q.EmployeeID.Descending).As("RNUM2"),
    q.Over.Rank().OrderBy(q.EmployeeID.Descending).As("RNUM3"),
    q.Over.DenseRank().OrderBy(q.EmployeeID.Descending).As("RNUM4"),
    q.Over.Ntile(4).OrderBy(q.EmployeeID.Descending).As("RNUM5"),
    q.Over.Sum(q.Freight).PartitionBy(q.EmployeeID).OrderBy(q.EmployeeID.Descending).As("RNUM6")
)
.GroupBy(q.EmployeeID, q.Freight)
.ToCollection<OrdersCollection>();

SQL Generated

SELECT
    ROW_NUMBER() OVER(ORDER BY q.[EmployeeID] DESC) AS RNUM1,
    ROW_NUMBER() OVER(PARTITION BY (SUM(q.[Freight]) * 10) ORDER BY q.[EmployeeID] DESC) AS RNUM2,
    RANK() OVER(ORDER BY q.[EmployeeID] DESC) AS RNUM3,
    DENSE_RANK() OVER(ORDER BY q.[EmployeeID] DESC) AS RNUM4,
    NTILE(4) OVER(ORDER BY q.[EmployeeID] DESC) AS RNUM5,
    SUM (q.[Freight]) OVER(PARTITION BY q.[EmployeeID] ORDER BY q.[EmployeeID] DESC) AS RNUM6 
FROM [Orders] q 
GROUP BY q.[EmployeeID], q.[Freight] 

Actual Query

This is an actual query, notice the "out" parameter usage on the calls to alias and then how they are later used.

esQueryItem orderTotal = null;
esQueryItem rowNumber = null;

OrdersCollection coll = new OrdersQuery("o", out var o)
    .From<OrderDetailsQuery>(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<OrdersCollection>();

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

The SQL Generated

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)