Table of Contents

Window (Analytic) Functions

Window functions are implemented as extension methods for static Sql.Ext property. For extensions generation (e.g. partitioning or ordering) fluent syntax is used.

Call Syntax

Sql.Ext.[Function]([Parameters])
	.Over()
	.[PartitionPart]
	.[OrderByPart]
	.[WindowingPart]
	.ToValue();

Last function in method chain must be function ToValue() - it is a mark that method chain is finished.

Example
var q = 
	from p in db.Parent
	join c in db.Child on p.ParentID equals c.ParentID 
	select new
	{
		Rank = Sql.Ext.Rank()
			.Over()
			.PartitionBy(p.Value1, c.ChildID)
			.OrderBy(p.Value1)
			.ThenBy(c.ChildID)
			.ThenBy(c.ParentID)
			.ToValue(),

		RowNumber = Sql.Ext.RowNumber()
			.Over()
			.PartitionBy(p.Value1, c.ChildID)
			.OrderByDesc(p.Value1)
			.ThenBy(c.ChildID)
			.ThenByDesc(c.ParentID)
			.ToValue(),

		DenseRank = Sql.Ext.DenseRank()
			.Over()
			.PartitionBy(p.Value1, c.ChildID)
			.OrderBy(p.Value1)
			.ToValue(),

		Sum = Sql.Ext.Sum(p.Value1)
			.Over()
			.PartitionBy(p.Value1, c.ChildID)
			.OrderBy(p.Value1)
			.ToValue(),

		Avg = Sql.Ext.Average<double>(p.Value1)
			.Over()
			.PartitionBy(p.Value1, c.ChildID)
			.OrderBy(p.Value1)
			.ToValue(),

		Count = Sql.Ext.Count(p.ParentID, Sql.AggregateModifier.All)
			.Over()
			.PartitionBy(p.Value1)
			.OrderBy(p.Value1)
			.Range.Between.UnboundedPreceding.And.CurrentRow
			.ToValue(),
	};

var res = q.ToArray();
Resulting SQL
SELECT
	RANK() OVER(PARTITION BY [p].[Value1], [c7].[ChildID] ORDER BY [p].[Value1], [c7].[ChildID], [c7].[ParentID]) as [c1],
	ROW_NUMBER() OVER(PARTITION BY [p].[Value1], [c7].[ChildID] ORDER BY [p].[Value1] DESC, [c7].[ChildID], [c7].[ParentID] DESC) as [c2],
	DENSE_RANK() OVER(PARTITION BY [p].[Value1], [c7].[ChildID] ORDER BY [p].[Value1]) as [c3],
	SUM([p].[Value1]) OVER(PARTITION BY [p].[Value1], [c7].[ChildID] ORDER BY [p].[Value1]) as [c4],
	AVG([p].[Value1]) OVER(PARTITION BY [p].[Value1], [c7].[ChildID] ORDER BY [p].[Value1]) as [c5],
	COUNT(ALL [p].[ParentID]) OVER(PARTITION BY [p].[Value1] ORDER BY [p].[Value1] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as [c6]
FROM
	[Parent] [p]
		INNER JOIN [Child] [c7] ON [p].[ParentID] = [c7].[ParentID]

Supported Functions (could be incomplete)

The following table contains list of supported Window Functions and LINQ To DB representation of these functions.

Function Linq To DB Extension
AVG Sql.Ext.Average()
CORR Sql.Ext.Corr()
COUNT Sql.Ext.Count()
COVAR_POP Sql.Ext.CovarPop()
COVAR_SAMP Sql.Ext.CovarSamp()
CUME_DIST Sql.Ext.CumeDist()
DENSE_RANK Sql.Ext.DenseRank()
FIRST Sql.Ext.[AggregateFunction].KeepFirst()
FIRST_VALUE Sql.Ext.FirstValue()
LAG Sql.Ext.Lag()
LAST Sql.Ext.[AggregateFunction].KeepLast()
LAST_VALUE Sql.Ext.LastValue()
LEAD Sql.Ext.Lead()
LISTAGG Sql.Ext.ListAgg()
MAX Sql.Ext.Max()
MEDIAN Sql.Ext.Median()
MIN Sql.Ext.Min()
NTH_VALUE Sql.Ext.NthValue()
NTILE Sql.Ext.NTile()
PERCENT_RANK Sql.Ext.PercentRank()
PERCENTILE_CONT Sql.Ext.PercentileCont()
PERCENTILE_DISC Sql.Ext.PercentileDisc()
RANK Sql.Ext.Rank()
RATIO_TO_REPORT Sql.Ext.RatioToReport()
REGR_ (Linear Regression) Functions
REGR_SLOPE Sql.Ext.RegrSlope()
REGR_INTERCEPT Sql.Ext.RegrIntercept()
REGR_COUNT Sql.Ext.RegrCount()
REGR_R2 Sql.Ext.RegrR2()
REGR_AVGX Sql.Ext.RegrAvgX()
REGR_AVGY Sql.Ext.RegrAvgY()
REGR_SXX Sql.Ext.RegrSXX()
REGR_SYY Sql.Ext.RegrSYY()
REGR_SXY Sql.Ext.RegrSXY()
ROW_NUMBER Sql.Ext.RowNumber()
STDDEV Sql.Ext.StdDev()
STDDEV_POP Sql.Ext.StdDevPop()
STDDEV_SAMP Sql.Ext.StdDevSamp()
SUM Sql.Ext.Sum()
VAR_POP Sql.Ext.VarPop()
VAR_SAMP Sql.Ext.VarSamp()
VARIANCE Sql.Ext.Variance()

If you have found that your database supports function that is not listed in table above, you can easily create your own extension (but it will be better to create feature request or PR). Code samples are located in Sql.Analytic.cs

Window Functions Support