I'm looking for a SQL Generator that can execute the new window aggregate functions that were released in SQL Server 2012. I've already looked at Subsonic and LINQ, but haven't yet taken a look at NHibernate and some others.
As an example, using the syntax in Subsonic, I'd like to do the following:
var query = new Select(Aggregate.Avg("Revenue", "MovingAverageRevenue")
.PartitionBy("Account_ID").OrderBy("Offset").RowsBetween(-5,-1)))
.From("Purchases")
.Where("MovingAverageRevenue")
.IsGreaterThan(500);
Which would translate to something like:
SELECT Aggregates.MovingAverageRevenue
FROM
(
SELECT AVG(Revenue) OVER
(PARTITION BY Account_ID
ORDER BY Offset
ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS MovingAverageRevenue
FROM Purchases
) Aggregates
WHERE Aggregates.MovingAverageRevenue > 500
A solution involving extending an existing ORM could also work, presumably.