Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
148 lines (132 sloc) 10.3 KB

Support of Window Functions also known as Analytic Functions in LINQ To DB is based on Oracle's Documentation and all mentioned functions are supported.

Window functions are implemented as extension methods for static Sql.Ext property. For defining Partitioning and Ordering fluent syntax is used and it is closest as possible to original SQL syntax.

C# 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 and provides correct DataType for resulting columns.

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]

Note There is no limitation in window functions usage. LINQ To DB will create SQL and run query, if function is not supported or some part of function is limited in particular Database - error will be thrown on database side.

Functions mapping

The following table contains list of supported Window Functions and LINQ To DB representation of these functions. Some functions have overloads for supporting full Window Functions syntax.

SQL Function Name Linq2db Function Name
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. Code samples are located in Sql.Analytic.cs

Engines that support Window Functions

You can’t perform that action at this time.