Window functions are implemented as extension methods for static Sql.Ext property. For extensions generation (e.g. partitioning or ordering) fluent syntax is used.
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.
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();
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]
The following table contains list of supported Window Functions and LINQ To DB representation of these functions.
Window Functions SupportIf 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
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4