Class WindowFunctionBuilder

Namespace: LinqToDB

Assembly: linq2db.dll

Provides SQL window function support via a fluent lambda-based API accessed through Window.

public static class WindowFunctionBuilder

Inheritance: object

WindowFunctionBuilder

Remarks

Not all window functions and clauses are supported by every database provider. An exception with a descriptive message will be thrown at query translation time if the current provider does not support the requested function or clause.

The FILTER (WHERE ...) clause is natively supported by PostgreSQL and DuckDB. For other providers, it is automatically emulated using CASE WHEN.

NULLS FIRST / NULLS LAST ordering is natively supported by PostgreSQL, Oracle, and Firebird 3+. For other providers, it is automatically emulated.

Methods

Average(IWindowFunction, byte, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static double Average(this Sql.IWindowFunction window, byte argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument byte
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, decimal, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static decimal Average(this Sql.IWindowFunction window, decimal argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument decimal
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

decimal

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, double, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static double Average(this Sql.IWindowFunction window, double argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument double
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, short, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static double Average(this Sql.IWindowFunction window, short argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument short
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, int, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static double Average(this Sql.IWindowFunction window, int argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument int
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

Use .Distinct() for AVG(DISTINCT x) OVER (...). DISTINCT in a window aggregate is not supported by most providers; where unsupported it throws a descriptive exception at query-translation time.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
        Avg4 = Sql.Window.Average(t.Salary, f => f.Distinct().PartitionBy(t.Dept)),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW),
  AVG(DISTINCT t.Salary) OVER (PARTITION BY t.Dept)
FROM Table t

Average(IWindowFunction, long, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static double Average(this Sql.IWindowFunction window, long argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument long
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, byte?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static double? Average(this Sql.IWindowFunction window, byte? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument byte?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, decimal?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static decimal? Average(this Sql.IWindowFunction window, decimal? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument decimal?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

decimal?

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, double?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static double? Average(this Sql.IWindowFunction window, double? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument double?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, short?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static double? Average(this Sql.IWindowFunction window, short? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument short?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, int?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static double? Average(this Sql.IWindowFunction window, int? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument int?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, long?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static double? Average(this Sql.IWindowFunction window, long? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument long?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, float?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static float? Average(this Sql.IWindowFunction window, float? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument float?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

float?

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Average(IWindowFunction, float, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL AVG() window function. Computes the average within the window frame.

public static float Average(this Sql.IWindowFunction window, float argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument float
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

float

Remarks

Syntax: Sql.Window.Average(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Avg1 = Sql.Window.Average(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Avg2 = Sql.Window.Average(t.Salary, f => f.Filter(t.IsActive).OrderBy(t.Date)),
        Avg3 = Sql.Window.Average(t.Salary, f => f.OrderBy(t.Date).RowsBetween.ValuePreceding(3).And.CurrentRow),
    };

Generated SQL:

SELECT
  AVG(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  AVG(CASE WHEN t.IsActive THEN t.Salary ELSE NULL END) OVER (ORDER BY t.Date),
  AVG(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
FROM Table t

Corr<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL CORR() window function — the correlation coefficient of the two value pairs within the window.

public static double? Corr<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.Corr(expr1, expr2, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: CORR(expr1, expr2) OVER (...)

Count(IWindowFunction, Func<IOFilterOPartitionOOrderOFrameFinal, IDefinedFunction>)

Generates SQL COUNT(*) window function. Use the Count(expr, ...) overload for COUNT(expr).

public static int Count(this Sql.IWindowFunction window, Func<WindowFunctionBuilder.IOFilterOPartitionOOrderOFrameFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
func Func<WindowFunctionBuilder.IOFilterOPartitionOOrderOFrameFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

int

Remarks

Syntax: Sql.Window.Count(f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Total   = Sql.Window.Count(f => f.PartitionBy(t.Dept).OrderBy(t.Id)),
        Running = Sql.Window.Count(f => f.OrderBy(t.Id).RowsBetween.Unbounded.And.CurrentRow),
        Filt    = Sql.Window.Count(f => f.Filter(t.Value > 10).PartitionBy(t.Dept).OrderBy(t.Id)),
    };

Generated SQL (PostgreSQL):

SELECT
  COUNT(*) OVER (PARTITION BY t.Dept ORDER BY t.Id),
  COUNT(*) OVER (ORDER BY t.Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  COUNT(*) FILTER (WHERE t.Value > 10) OVER (PARTITION BY t.Dept ORDER BY t.Id)
FROM Table t

Count(IWindowFunction, object?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL COUNT(expr) window function. Use .Distinct() for COUNT(DISTINCT expr).

public static int Count(this Sql.IWindowFunction window, object? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument object
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

int

Remarks

Syntax: Sql.Window.Count(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

DISTINCT in a window aggregate is not supported by most providers; where unsupported it throws a descriptive exception at query-translation time.

C# usage:

var query =
    from t in db.Table
    select new
    {
        NonNull  = Sql.Window.Count(t.NullableValue, f => f.PartitionBy(t.Dept).OrderBy(t.Id)),
        Distinct = Sql.Window.Count(t.Value, f => f.Distinct().PartitionBy(t.Dept)),
    };

Generated SQL:

SELECT
  COUNT(t.NullableValue) OVER (PARTITION BY t.Dept ORDER BY t.Id),
  COUNT(DISTINCT t.Value) OVER (PARTITION BY t.Dept)
FROM Table t

CovarPop<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL COVAR_POP() window function — the population covariance of the two value pairs within the window.

public static double? CovarPop<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.CovarPop(expr1, expr2, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: COVAR_POP(expr1, expr2) OVER (...)

CovarSamp<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL COVAR_SAMP() window function — the sample covariance of the two value pairs within the window.

public static double? CovarSamp<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.CovarSamp(expr1, expr2, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: COVAR_SAMP(expr1, expr2) OVER (...)

CumeDist(IWindowFunction, Func<IOPartitionROrderFinal, IDefinedFunction>)

Generates SQL CUME_DIST() window function. Returns the cumulative distribution (0 to 1).

public static double CumeDist(this Sql.IWindowFunction window, Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
func Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double

Remarks

Syntax: Sql.Window.CumeDist(f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers (e.g. ClickHouse).

C# usage:

var query =
    from t in db.Table
    select new
    {
        CD = Sql.Window.CumeDist(f => f.PartitionBy(t.Dept).OrderBy(t.Salary)),
    };

Generated SQL:

SELECT CUME_DIST() OVER (PARTITION BY t.Dept ORDER BY t.Salary)
FROM Table t

CumeDist<TElement, TValue>(IEnumerable<TElement>, object?, Func<TElement, IOnlyOrderByPart, IDefinedFunction<TValue>>)

Generates the SQL hypothetical-set CUME_DIST() aggregate — the cumulative distribution, a value in (0, 1] giving the fraction of rows ordering at or before the given value, it would have in the ordered group.

public static double CumeDist<TElement, TValue>(this IEnumerable<TElement> source, object? value, Func<TElement, WindowFunctionBuilder.IOnlyOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

source IEnumerable<TElement>
value object
func Func<TElement, WindowFunctionBuilder.IOnlyOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

double

Type Parameters

TElement
TValue

Remarks

Syntax: source.CumeDist(value, (e, f) => f.OrderBy(e.Column)[.Filter(...)])

Native on Oracle and PostgreSQL; throws a descriptive exception at query-translation time on other providers.

C# usage:

var query =
    from t in db.Table
    group t by t.Dept into g
    select new
    {
        g.Key,
        CumeDist = g.CumeDist(1000, (e, f) => f.OrderBy(e.Salary)),
    };

Generated SQL:

SELECT t.Dept, CUME_DIST(1000) WITHIN GROUP (ORDER BY t.Salary)
FROM Table t
GROUP BY t.Dept

CumeDist<TElement, TValue>(IEnumerable<TElement>, object?, object?, Func<TElement, IMultipleOrderByPart, IDefinedFunction<TValue>>)

Generates the SQL hypothetical-set CUME_DIST() aggregate over two ordering keys — the cumulative distribution (a value in (0, 1]) the given values would have in the doubly-ordered group.

public static double CumeDist<TElement, TValue>(this IEnumerable<TElement> source, object? value1, object? value2, Func<TElement, WindowFunctionBuilder.IMultipleOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

source IEnumerable<TElement>
value1 object
value2 object
func Func<TElement, WindowFunctionBuilder.IMultipleOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

double

Type Parameters

TElement
TValue

Remarks

Syntax: source.CumeDist(value1, value2, (e, f) => f.OrderBy(e.Key1).ThenBy(e.Key2)[.Filter(...)])

Native on Oracle and PostgreSQL; throws a descriptive exception at query-translation time on other providers.

C# usage:

var query =
    from t in db.Table
    group t by t.Dept into g
    select new
    {
        g.Key,
        CumeDist = g.CumeDist(1000, 2000, (e, f) => f.OrderBy(e.Salary).ThenBy(e.Bonus)),
    };

Generated SQL:

SELECT t.Dept, CUME_DIST(1000, 2000) WITHIN GROUP (ORDER BY t.Salary, t.Bonus)
FROM Table t
GROUP BY t.Dept

DefineWindow(IWindowFunction, Func<IWindowBuilder, IDefinedFunction>)

Defines a reusable window specification that can be shared across multiple window function calls via UseWindow.

public static WindowFunctionBuilder.IDefinedWindow DefineWindow(this Sql.IWindowFunction window, Func<WindowFunctionBuilder.IWindowBuilder, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
func Func<WindowFunctionBuilder.IWindowBuilder, WindowFunctionBuilder.IDefinedFunction>

Returns

WindowFunctionBuilder.IDefinedWindow

Remarks

May not be supported by all database providers.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        RN  = Sql.Window.RowNumber(f => f.UseWindow(wnd)),
        Sum = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
        Lag = Sql.Window.Lag(t.Salary, f => f.UseWindow(wnd)),
    };

DenseRank(IWindowFunction, Func<IOPartitionROrderFinal, IDefinedFunction>)

Generates SQL DENSE_RANK() window function. Returns the rank without gaps for ties.

public static long DenseRank(this Sql.IWindowFunction window, Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
func Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long

Remarks

Syntax: Sql.Window.DenseRank(f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers.

C# usage:

var query =
    from t in db.Table
    select new
    {
        DR = Sql.Window.DenseRank(f => f.PartitionBy(t.Dept).OrderBy(t.Salary)),
    };

Generated SQL:

SELECT DENSE_RANK() OVER (PARTITION BY t.Dept ORDER BY t.Salary)
FROM Table t

DenseRank<TElement, TValue>(IEnumerable<TElement>, object?, Func<TElement, IOnlyOrderByPart, IDefinedFunction<TValue>>)

Generates the SQL hypothetical-set DENSE_RANK() aggregate — the rank the given value would have (no gaps after ties) if inserted into the ordered group.

public static long DenseRank<TElement, TValue>(this IEnumerable<TElement> source, object? value, Func<TElement, WindowFunctionBuilder.IOnlyOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

source IEnumerable<TElement>
value object
func Func<TElement, WindowFunctionBuilder.IOnlyOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

long

Type Parameters

TElement
TValue

Remarks

Syntax: source.DenseRank(value, (e, f) => f.OrderBy(e.Column)[.Filter(...)])

Native on Oracle and PostgreSQL; throws a descriptive exception at query-translation time on other providers.

C# usage:

var query =
    from t in db.Table
    group t by t.Dept into g
    select new
    {
        g.Key,
        DenseRank = g.DenseRank(1000, (e, f) => f.OrderBy(e.Salary)),
    };

Generated SQL:

SELECT t.Dept, DENSE_RANK(1000) WITHIN GROUP (ORDER BY t.Salary)
FROM Table t
GROUP BY t.Dept

DenseRank<TElement, TValue>(IEnumerable<TElement>, object?, object?, Func<TElement, IMultipleOrderByPart, IDefinedFunction<TValue>>)

Generates the SQL hypothetical-set DENSE_RANK() aggregate over two ordering keys — the rank the given values would have (no gaps after ties) in the doubly-ordered group.

public static long DenseRank<TElement, TValue>(this IEnumerable<TElement> source, object? value1, object? value2, Func<TElement, WindowFunctionBuilder.IMultipleOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

source IEnumerable<TElement>
value1 object
value2 object
func Func<TElement, WindowFunctionBuilder.IMultipleOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

long

Type Parameters

TElement
TValue

Remarks

Syntax: source.DenseRank(value1, value2, (e, f) => f.OrderBy(e.Key1).ThenBy(e.Key2)[.Filter(...)])

Native on Oracle and PostgreSQL; throws a descriptive exception at query-translation time on other providers.

C# usage:

var query =
    from t in db.Table
    group t by t.Dept into g
    select new
    {
        g.Key,
        DenseRank = g.DenseRank(1000, 2000, (e, f) => f.OrderBy(e.Salary).ThenBy(e.Bonus)),
    };

Generated SQL:

SELECT t.Dept, DENSE_RANK(1000, 2000) WITHIN GROUP (ORDER BY t.Salary, t.Bonus)
FROM Table t
GROUP BY t.Dept

FirstValue<T>(IWindowFunction, T, Func<IValueFinal, IDefinedFunction>)

Generates SQL FIRST_VALUE() window function. Returns the first value in the frame.

public static T FirstValue<T>(this Sql.IWindowFunction window, T expr, Func<WindowFunctionBuilder.IValueFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
expr T
func Func<WindowFunctionBuilder.IValueFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

T

Type Parameters

T

Remarks

Syntax: Sql.Window.FirstValue(expr, f => f.[IgnoreNulls()].[PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

May not be supported by all database providers. Does not support the FILTER clause.

Use .IgnoreNulls() for FIRST_VALUE(x) IGNORE NULLS (skips NULLs). Supported only where the provider allows it; otherwise a translation-time error is thrown.

C# usage:

var query =
    from t in db.Table
    select new
    {
        First1 = Sql.Window.FirstValue(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        First2 = Sql.Window.FirstValue(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  FIRST_VALUE(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  FIRST_VALUE(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Lag<T>(IWindowFunction, T, Func<ILeadLagFinal, IDefinedFunction>)

Generates SQL LAG() window function. Accesses a value from a preceding row.

public static T Lag<T>(this Sql.IWindowFunction window, T expr, Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
expr T
func Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

T

Type Parameters

T

Remarks

Syntax: Sql.Window.Lag(expr, [offset, [default,]] f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Prev  = Sql.Window.Lag(t.Value, f => f.OrderBy(t.Date)),
        Back2 = Sql.Window.Lag(t.Value, 2, f => f.OrderBy(t.Date)),
        Safe  = Sql.Window.Lag(t.Value, 1, 0, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
    };

Generated SQL:

SELECT
  LAG(t.Value) OVER (ORDER BY t.Date),
  LAG(t.Value, 2) OVER (ORDER BY t.Date),
  LAG(t.Value, 1, 0) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Lag<T>(IWindowFunction, T, int, Func<ILeadLagFinal, IDefinedFunction>)

Generates SQL LAG() window function. Accesses a value from a preceding row.

public static T Lag<T>(this Sql.IWindowFunction window, T expr, int offset, Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
expr T
offset int
func Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

T

Type Parameters

T

Remarks

Syntax: Sql.Window.Lag(expr, [offset, [default,]] f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Prev  = Sql.Window.Lag(t.Value, f => f.OrderBy(t.Date)),
        Back2 = Sql.Window.Lag(t.Value, 2, f => f.OrderBy(t.Date)),
        Safe  = Sql.Window.Lag(t.Value, 1, 0, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
    };

Generated SQL:

SELECT
  LAG(t.Value) OVER (ORDER BY t.Date),
  LAG(t.Value, 2) OVER (ORDER BY t.Date),
  LAG(t.Value, 1, 0) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Lag<T>(IWindowFunction, T, int, T, Func<ILeadLagFinal, IDefinedFunction>)

Generates SQL LAG() window function. Accesses a value from a preceding row.

public static T Lag<T>(this Sql.IWindowFunction window, T expr, int offset, T @default, Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
expr T
offset int
default T
func Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

T

Type Parameters

T

Remarks

Syntax: Sql.Window.Lag(expr, [offset, [default,]] f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Prev  = Sql.Window.Lag(t.Value, f => f.OrderBy(t.Date)),
        Back2 = Sql.Window.Lag(t.Value, 2, f => f.OrderBy(t.Date)),
        Safe  = Sql.Window.Lag(t.Value, 1, 0, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
    };

Generated SQL:

SELECT
  LAG(t.Value) OVER (ORDER BY t.Date),
  LAG(t.Value, 2) OVER (ORDER BY t.Date),
  LAG(t.Value, 1, 0) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

LastValue<T>(IWindowFunction, T, Func<IValueFinal, IDefinedFunction>)

Generates SQL LAST_VALUE() window function. Returns the last value in the frame.

public static T LastValue<T>(this Sql.IWindowFunction window, T expr, Func<WindowFunctionBuilder.IValueFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
expr T
func Func<WindowFunctionBuilder.IValueFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

T

Type Parameters

T

Remarks

Syntax: Sql.Window.LastValue(expr, f => f.[PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

May not be supported by all database providers. Does not support the FILTER clause.

C# usage:

var query =
    from t in db.Table
    select new
    {
        // Use UNBOUNDED FOLLOWING to get the true last value in partition
        Last = Sql.Window.LastValue(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date).RowsBetween.Unbounded.And.Unbounded),
    };

Generated SQL:

SELECT LAST_VALUE(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM Table t

Lead<T>(IWindowFunction, T, Func<ILeadLagFinal, IDefinedFunction>)

Generates SQL LEAD() window function. Accesses a value from a subsequent row.

public static T Lead<T>(this Sql.IWindowFunction window, T expr, Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
expr T
func Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

T

Type Parameters

T

Remarks

Syntax: Sql.Window.Lead(expr, [offset, [default,]] f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Next  = Sql.Window.Lead(t.Value, f => f.OrderBy(t.Date)),
        Skip2 = Sql.Window.Lead(t.Value, 2, f => f.OrderBy(t.Date)),
        Safe  = Sql.Window.Lead(t.Value, 1, 0, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
    };

Generated SQL:

SELECT
  LEAD(t.Value) OVER (ORDER BY t.Date),
  LEAD(t.Value, 2) OVER (ORDER BY t.Date),
  LEAD(t.Value, 1, 0) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Lead<T>(IWindowFunction, T, int, Func<ILeadLagFinal, IDefinedFunction>)

Generates SQL LEAD() window function. Accesses a value from a subsequent row.

public static T Lead<T>(this Sql.IWindowFunction window, T expr, int offset, Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
expr T
offset int
func Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

T

Type Parameters

T

Remarks

Syntax: Sql.Window.Lead(expr, [offset, [default,]] f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Next  = Sql.Window.Lead(t.Value, f => f.OrderBy(t.Date)),
        Skip2 = Sql.Window.Lead(t.Value, 2, f => f.OrderBy(t.Date)),
        Safe  = Sql.Window.Lead(t.Value, 1, 0, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
    };

Generated SQL:

SELECT
  LEAD(t.Value) OVER (ORDER BY t.Date),
  LEAD(t.Value, 2) OVER (ORDER BY t.Date),
  LEAD(t.Value, 1, 0) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Lead<T>(IWindowFunction, T, int, T, Func<ILeadLagFinal, IDefinedFunction>)

Generates SQL LEAD() window function. Accesses a value from a subsequent row.

public static T Lead<T>(this Sql.IWindowFunction window, T expr, int offset, T @default, Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
expr T
offset int
default T
func Func<WindowFunctionBuilder.ILeadLagFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

T

Type Parameters

T

Remarks

Syntax: Sql.Window.Lead(expr, [offset, [default,]] f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Next  = Sql.Window.Lead(t.Value, f => f.OrderBy(t.Date)),
        Skip2 = Sql.Window.Lead(t.Value, 2, f => f.OrderBy(t.Date)),
        Safe  = Sql.Window.Lead(t.Value, 1, 0, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
    };

Generated SQL:

SELECT
  LEAD(t.Value) OVER (ORDER BY t.Date),
  LEAD(t.Value, 2) OVER (ORDER BY t.Date),
  LEAD(t.Value, 1, 0) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

LongCount(IWindowFunction, Func<IOFilterOPartitionOOrderOFrameFinal, IDefinedFunction>)

Generates SQL COUNT(*) window function returning a 64-bit count. Use the LongCount(expr, ...) overload for COUNT(expr).

public static long LongCount(this Sql.IWindowFunction window, Func<WindowFunctionBuilder.IOFilterOPartitionOOrderOFrameFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
func Func<WindowFunctionBuilder.IOFilterOPartitionOOrderOFrameFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long

Remarks

Syntax: Sql.Window.LongCount(f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Identical SQL to Count(IWindowFunction, Func<IOFilterOPartitionOOrderOFrameFinal, IDefinedFunction>) (COUNT(*)), returning long.

LongCount(IWindowFunction, object?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL COUNT(expr) window function returning a 64-bit count. Use .Distinct() for COUNT(DISTINCT expr).

public static long LongCount(this Sql.IWindowFunction window, object? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument object
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long

Remarks

Syntax: Sql.Window.LongCount(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Identical SQL to Count(IWindowFunction, object?, Func<IAggregateFinal, IDefinedFunction>) (COUNT(expr)), returning long.

Max(IWindowFunction, byte, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static byte Max(this Sql.IWindowFunction window, byte argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument byte
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

byte

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, decimal, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static decimal Max(this Sql.IWindowFunction window, decimal argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument decimal
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

decimal

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, double, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static double Max(this Sql.IWindowFunction window, double argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument double
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, short, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static short Max(this Sql.IWindowFunction window, short argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument short
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

short

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, int, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static int Max(this Sql.IWindowFunction window, int argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument int
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

int

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

Use .Distinct() for MAX(DISTINCT x) OVER (...). DISTINCT in a window aggregate is not supported by most providers; where unsupported it throws a descriptive exception at query-translation time.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Max3 = Sql.Window.Max(t.Salary, f => f.Distinct().PartitionBy(t.Dept)),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  MAX(DISTINCT t.Salary) OVER (PARTITION BY t.Dept)
FROM Table t

Max(IWindowFunction, long, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static long Max(this Sql.IWindowFunction window, long argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument long
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, byte?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static byte? Max(this Sql.IWindowFunction window, byte? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument byte?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

byte?

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, decimal?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static decimal? Max(this Sql.IWindowFunction window, decimal? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument decimal?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

decimal?

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, double?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static double? Max(this Sql.IWindowFunction window, double? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument double?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, short?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static short? Max(this Sql.IWindowFunction window, short? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument short?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

short?

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, int?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static int? Max(this Sql.IWindowFunction window, int? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument int?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

int?

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, long?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static long? Max(this Sql.IWindowFunction window, long? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument long?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long?

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, float?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static float? Max(this Sql.IWindowFunction window, float? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument float?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

float?

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Max(IWindowFunction, float, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MAX() window function. Returns the maximum value within the window frame.

public static float Max(this Sql.IWindowFunction window, float argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument float
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

float

Remarks

Syntax: Sql.Window.Max(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Max1 = Sql.Window.Max(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Max2 = Sql.Window.Max(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MAX(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MAX(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Median<T>(IWindowFunction, T, Func<IOPartitionFinal, IDefinedFunction>)

Generates the SQL MEDIAN() window function — the median (50th percentile, continuous) of the values within the window.

public static double? Median<T>(this Sql.IWindowFunction window, T argument, Func<WindowFunctionBuilder.IOPartitionFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument T
func Func<WindowFunctionBuilder.IOPartitionFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T

Remarks

Syntax: Sql.Window.Median(expr, f => f.[PartitionBy(...)])

Native on Oracle, DB2, DuckDB and MariaDB; throws a descriptive exception at query-translation time elsewhere. Its OVER clause carries PARTITION BY only (no ORDER BY or frame).

C# usage:

Sql.Window.Median(t.Value, f => f.PartitionBy(t.Dept))

Generated SQL (Oracle):

MEDIAN(t.Value) OVER (PARTITION BY t.Dept)

Min(IWindowFunction, byte, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static byte Min(this Sql.IWindowFunction window, byte argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument byte
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

byte

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, decimal, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static decimal Min(this Sql.IWindowFunction window, decimal argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument decimal
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

decimal

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, double, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static double Min(this Sql.IWindowFunction window, double argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument double
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, short, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static short Min(this Sql.IWindowFunction window, short argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument short
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

short

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, int, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static int Min(this Sql.IWindowFunction window, int argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument int
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

int

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

Use .Distinct() for MIN(DISTINCT x) OVER (...). DISTINCT in a window aggregate is not supported by most providers; where unsupported it throws a descriptive exception at query-translation time.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Min3 = Sql.Window.Min(t.Salary, f => f.Distinct().PartitionBy(t.Dept)),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  MIN(DISTINCT t.Salary) OVER (PARTITION BY t.Dept)
FROM Table t

Min(IWindowFunction, long, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static long Min(this Sql.IWindowFunction window, long argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument long
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, byte?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static byte? Min(this Sql.IWindowFunction window, byte? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument byte?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

byte?

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, decimal?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static decimal? Min(this Sql.IWindowFunction window, decimal? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument decimal?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

decimal?

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, double?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static double? Min(this Sql.IWindowFunction window, double? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument double?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, short?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static short? Min(this Sql.IWindowFunction window, short? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument short?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

short?

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, int?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static int? Min(this Sql.IWindowFunction window, int? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument int?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

int?

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, long?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static long? Min(this Sql.IWindowFunction window, long? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument long?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long?

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, float?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static float? Min(this Sql.IWindowFunction window, float? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument float?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

float?

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

Min(IWindowFunction, float, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL MIN() window function. Returns the minimum value within the window frame.

public static float Min(this Sql.IWindowFunction window, float argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument float
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

float

Remarks

Syntax: Sql.Window.Min(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Min1 = Sql.Window.Min(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Min2 = Sql.Window.Min(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
    };

Generated SQL:

SELECT
  MIN(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  MIN(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Table t

NTile(IWindowFunction, int, Func<IOPartitionROrderFinal, IDefinedFunction>)

Generates SQL NTILE(n) window function. Distributes rows into n groups.

public static long NTile(this Sql.IWindowFunction window, int n, Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
n int
func Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long

Remarks

Syntax: Sql.Window.NTile(n, f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers (e.g. ClickHouse, Firebird 3).

C# usage:

var query =
    from t in db.Table
    select new
    {
        Quartile = Sql.Window.NTile(4, f => f.PartitionBy(t.Dept).OrderBy(t.Salary)),
        Tercile  = Sql.Window.NTile(3, f => f.OrderBy(t.Score)),
    };

Generated SQL:

SELECT
  NTILE(4) OVER (PARTITION BY t.Dept ORDER BY t.Salary),
  NTILE(3) OVER (ORDER BY t.Score)
FROM Table t

NthValue<T>(IWindowFunction, T, long, Func<INthValueFinal, IDefinedFunction>)

Generates SQL NTH_VALUE() window function. Returns the value at position n in the frame.

public static T NthValue<T>(this Sql.IWindowFunction window, T expr, long n, Func<WindowFunctionBuilder.INthValueFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
expr T
n long
func Func<WindowFunctionBuilder.INthValueFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

T

Type Parameters

T

Remarks

Syntax: Sql.Window.NthValue(expr, n, f => f.[FromLast()].[IgnoreNulls()].[PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

May not be supported by all database providers (e.g. SQL Server). Does not support the FILTER clause.

Use .FromLast() and/or .IgnoreNulls() (in that order) for NTH_VALUE(x, n) FROM LAST IGNORE NULLS. Each is supported only where the provider allows it; otherwise a translation-time error is thrown.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Second = Sql.Window.NthValue(t.Salary, 2L, f => f.PartitionBy(t.Dept).OrderBy(t.Date).RowsBetween.Unbounded.And.Unbounded),
    };

Generated SQL:

SELECT NTH_VALUE(t.Salary, 2) OVER (PARTITION BY t.Dept ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM Table t

PercentRank(IWindowFunction, Func<IOPartitionROrderFinal, IDefinedFunction>)

Generates SQL PERCENT_RANK() window function. Returns the relative rank (0 to 1).

public static double PercentRank(this Sql.IWindowFunction window, Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
func Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double

Remarks

Syntax: Sql.Window.PercentRank(f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers (e.g. ClickHouse).

C# usage:

var query =
    from t in db.Table
    select new
    {
        PR = Sql.Window.PercentRank(f => f.PartitionBy(t.Dept).OrderBy(t.Salary)),
    };

Generated SQL:

SELECT PERCENT_RANK() OVER (PARTITION BY t.Dept ORDER BY t.Salary)
FROM Table t

PercentRank<TElement, TValue>(IEnumerable<TElement>, object?, Func<TElement, IOnlyOrderByPart, IDefinedFunction<TValue>>)

Generates the SQL hypothetical-set PERCENT_RANK() aggregate — the relative rank, a value in [0, 1] computed as (rank - 1) / (rowCount - 1), the given value would have in the ordered group.

public static double PercentRank<TElement, TValue>(this IEnumerable<TElement> source, object? value, Func<TElement, WindowFunctionBuilder.IOnlyOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

source IEnumerable<TElement>
value object
func Func<TElement, WindowFunctionBuilder.IOnlyOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

double

Type Parameters

TElement
TValue

Remarks

Syntax: source.PercentRank(value, (e, f) => f.OrderBy(e.Column)[.Filter(...)])

Native on Oracle and PostgreSQL; throws a descriptive exception at query-translation time on other providers.

C# usage:

var query =
    from t in db.Table
    group t by t.Dept into g
    select new
    {
        g.Key,
        PercentRank = g.PercentRank(1000, (e, f) => f.OrderBy(e.Salary)),
    };

Generated SQL:

SELECT t.Dept, PERCENT_RANK(1000) WITHIN GROUP (ORDER BY t.Salary)
FROM Table t
GROUP BY t.Dept

PercentRank<TElement, TValue>(IEnumerable<TElement>, object?, object?, Func<TElement, IMultipleOrderByPart, IDefinedFunction<TValue>>)

Generates the SQL hypothetical-set PERCENT_RANK() aggregate over two ordering keys — the relative rank (a value in [0, 1]) the given values would have in the doubly-ordered group.

public static double PercentRank<TElement, TValue>(this IEnumerable<TElement> source, object? value1, object? value2, Func<TElement, WindowFunctionBuilder.IMultipleOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

source IEnumerable<TElement>
value1 object
value2 object
func Func<TElement, WindowFunctionBuilder.IMultipleOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

double

Type Parameters

TElement
TValue

Remarks

Syntax: source.PercentRank(value1, value2, (e, f) => f.OrderBy(e.Key1).ThenBy(e.Key2)[.Filter(...)])

Native on Oracle and PostgreSQL; throws a descriptive exception at query-translation time on other providers.

C# usage:

var query =
    from t in db.Table
    group t by t.Dept into g
    select new
    {
        g.Key,
        PercentRank = g.PercentRank(1000, 2000, (e, f) => f.OrderBy(e.Salary).ThenBy(e.Bonus)),
    };

Generated SQL:

SELECT t.Dept, PERCENT_RANK(1000, 2000) WITHIN GROUP (ORDER BY t.Salary, t.Bonus)
FROM Table t
GROUP BY t.Dept

PercentileCont<TValue>(IWindowFunction, double, Func<IOrderedSetWindowSingleOrder, IDefinedFunction<TValue>>)

Generates the SQL PERCENTILE_CONT() windowed ordered-set aggregate: PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY key) OVER (PARTITION BY ...).

public static TValue PercentileCont<TValue>(this Sql.IWindowFunction window, double fraction, Func<WindowFunctionBuilder.IOrderedSetWindowSingleOrder, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

window Sql.IWindowFunction
fraction double
func Func<WindowFunctionBuilder.IOrderedSetWindowSingleOrder, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

TValue

Type Parameters

TValue

Remarks

Syntax: Sql.Window.PercentileCont(fraction, w => w.OrderBy(key)[.PartitionBy(...)])

The windowed form is native on SQL Server, Oracle and MariaDB; PostgreSQL supports only the group form (g.PercentileCont).

C# usage:

Sql.Window.PercentileCont(0.5, w => w.OrderBy(t.Salary).PartitionBy(t.Dept))

Generated SQL:

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY t.Salary) OVER (PARTITION BY t.Dept)

PercentileCont<TElement, TValue>(IEnumerable<TElement>, double, Func<TElement, IOnlyOrderByPart, IDefinedFunction<TValue>>)

Generates SQL PERCENTILE_CONT() ordered-set aggregate. Computes a percentile based on continuous distribution.

public static TValue PercentileCont<TElement, TValue>(this IEnumerable<TElement> source, double argument, Func<TElement, WindowFunctionBuilder.IOnlyOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

source IEnumerable<TElement>
argument double
func Func<TElement, WindowFunctionBuilder.IOnlyOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

TValue

Type Parameters

TElement
TValue

Remarks

Syntax: source.PercentileCont(fraction, (e, f) => f.OrderBy(e.Column)[.Filter(...)])

May not be supported by all database providers (e.g. SQLite, MySQL, ClickHouse).

C# usage:

var query =
    from t in db.Table
    group t by t.Dept into g
    select new
    {
        g.Key,
        Median = g.PercentileCont(0.5, (e, f) => f.OrderBy(e.Salary)),
    };

Generated SQL:

SELECT t.Dept, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY t.Salary)
FROM Table t
GROUP BY t.Dept

PercentileDisc<TValue>(IWindowFunction, double, Func<IOrderedSetWindowMultiOrder, IDefinedFunction<TValue>>)

Generates the SQL PERCENTILE_DISC() windowed ordered-set aggregate: PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY key) OVER (PARTITION BY ...).

public static TValue PercentileDisc<TValue>(this Sql.IWindowFunction window, double fraction, Func<WindowFunctionBuilder.IOrderedSetWindowMultiOrder, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

window Sql.IWindowFunction
fraction double
func Func<WindowFunctionBuilder.IOrderedSetWindowMultiOrder, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

TValue

Type Parameters

TValue

Remarks

Syntax: Sql.Window.PercentileDisc(fraction, w => w.OrderBy(key)[.ThenBy(...)][.PartitionBy(...)])

The windowed form is native on SQL Server, Oracle and MariaDB; PostgreSQL supports only the group form (g.PercentileDisc).

C# usage:

Sql.Window.PercentileDisc(0.5, w => w.OrderBy(t.Salary).PartitionBy(t.Dept))

Generated SQL:

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY t.Salary) OVER (PARTITION BY t.Dept)

PercentileDisc<TElement, TValue>(IEnumerable<TElement>, double, Func<TElement, IMultipleOrderByPart, IDefinedFunction<TValue>>)

Generates SQL PERCENTILE_DISC() ordered-set aggregate. Returns the value at the specified percentile from the sorted set.

public static TValue PercentileDisc<TElement, TValue>(this IEnumerable<TElement> source, double argument, Func<TElement, WindowFunctionBuilder.IMultipleOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

source IEnumerable<TElement>
argument double
func Func<TElement, WindowFunctionBuilder.IMultipleOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

TValue

Type Parameters

TElement
TValue

Remarks

Syntax: source.PercentileDisc(fraction, (e, f) => f.OrderBy(e.Column)[.ThenBy(...)][.Filter(...)])

May not be supported by all database providers (e.g. SQLite, MySQL, ClickHouse).

C# usage:

var query =
    from t in db.Table
    group t by t.Dept into g
    select new
    {
        g.Key,
        Median = g.PercentileDisc(0.5, (e, f) => f.OrderBy(e.Salary)),
    };

Generated SQL:

SELECT t.Dept, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY t.Salary)
FROM Table t
GROUP BY t.Dept

Rank(IWindowFunction, Func<IOPartitionROrderFinal, IDefinedFunction>)

Generates SQL RANK() window function. Returns the rank of each row, with gaps for ties.

public static long Rank(this Sql.IWindowFunction window, Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
func Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long

Remarks

Syntax: Sql.Window.Rank(f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers.

C# usage:

var query =
    from t in db.Table
    select new
    {
        Rank1 = Sql.Window.Rank(f => f.PartitionBy(t.Dept).OrderBy(t.Salary)),
        Rank2 = Sql.Window.Rank(f => f.OrderByDesc(t.Score)),
    };

Generated SQL:

SELECT
  RANK() OVER (PARTITION BY t.Dept ORDER BY t.Salary),
  RANK() OVER (ORDER BY t.Score DESC)
FROM Table t

Rank<TElement, TValue>(IEnumerable<TElement>, object?, Func<TElement, IOnlyOrderByPart, IDefinedFunction<TValue>>)

Generates the SQL hypothetical-set RANK() aggregate — the rank the given value would have (with gaps after ties) if inserted into the ordered group.

public static long Rank<TElement, TValue>(this IEnumerable<TElement> source, object? value, Func<TElement, WindowFunctionBuilder.IOnlyOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

source IEnumerable<TElement>
value object
func Func<TElement, WindowFunctionBuilder.IOnlyOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

long

Type Parameters

TElement
TValue

Remarks

Syntax: source.Rank(value, (e, f) => f.OrderBy(e.Column)[.Filter(...)])

Native on Oracle and PostgreSQL; throws a descriptive exception at query-translation time on other providers.

C# usage:

var query =
    from t in db.Table
    group t by t.Dept into g
    select new
    {
        g.Key,
        Rank = g.Rank(1000, (e, f) => f.OrderBy(e.Salary)),
    };

Generated SQL:

SELECT t.Dept, RANK(1000) WITHIN GROUP (ORDER BY t.Salary)
FROM Table t
GROUP BY t.Dept

Rank<TElement, TValue>(IEnumerable<TElement>, object?, object?, Func<TElement, IMultipleOrderByPart, IDefinedFunction<TValue>>)

Generates the SQL hypothetical-set RANK() aggregate over two ordering keys — the rank the given values would have (with gaps after ties) in the doubly-ordered group.

public static long Rank<TElement, TValue>(this IEnumerable<TElement> source, object? value1, object? value2, Func<TElement, WindowFunctionBuilder.IMultipleOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>> func)

Parameters

source IEnumerable<TElement>
value1 object
value2 object
func Func<TElement, WindowFunctionBuilder.IMultipleOrderByPart, WindowFunctionBuilder.IDefinedFunction<TValue>>

Returns

long

Type Parameters

TElement
TValue

Remarks

Syntax: source.Rank(value1, value2, (e, f) => f.OrderBy(e.Key1).ThenBy(e.Key2)[.Filter(...)])

Native on Oracle and PostgreSQL; throws a descriptive exception at query-translation time on other providers.

C# usage:

var query =
    from t in db.Table
    group t by t.Dept into g
    select new
    {
        g.Key,
        Rank = g.Rank(1000, 2000, (e, f) => f.OrderBy(e.Salary).ThenBy(e.Bonus)),
    };

Generated SQL:

SELECT t.Dept, RANK(1000, 2000) WITHIN GROUP (ORDER BY t.Salary, t.Bonus)
FROM Table t
GROUP BY t.Dept

RatioToReport<T>(IWindowFunction, T, Func<IOPartitionFinal, IDefinedFunction>)

Generates the SQL RATIO_TO_REPORT() window function — the ratio of the value to the sum of the values within the window (expr / SUM(expr) OVER (...)).

public static double? RatioToReport<T>(this Sql.IWindowFunction window, T argument, Func<WindowFunctionBuilder.IOPartitionFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument T
func Func<WindowFunctionBuilder.IOPartitionFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T

Remarks

Syntax: Sql.Window.RatioToReport(expr, f => f.[PartitionBy(...)])

Emitted natively as RATIO_TO_REPORT on Oracle and DB2; emulated as expr / SUM(expr) OVER (...) on other providers.

C# usage:

Sql.Window.RatioToReport(t.Value, f => f.PartitionBy(t.Dept))

Generated SQL (Oracle):

RATIO_TO_REPORT(t.Value) OVER (PARTITION BY t.Dept)

RegrAvgX<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL REGR_AVGX() window function — the average of the independent variable (argument2) over non-null pairs.

public static double? RegrAvgX<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.RegrAvgX(y, x, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: REGR_AVGX(y, x) OVER (...)

RegrAvgY<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL REGR_AVGY() window function — the average of the dependent variable (argument1) over non-null pairs.

public static double? RegrAvgY<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.RegrAvgY(y, x, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: REGR_AVGY(y, x) OVER (...)

RegrCount<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL REGR_COUNT() window function — the number of non-null (argument1, argument2) pairs within the window.

public static long? RegrCount<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.RegrCount(y, x, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: REGR_COUNT(y, x) OVER (...)

RegrIntercept<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL REGR_INTERCEPT() window function — the y-intercept of the least-squares-fit linear equation of (argument1, argument2) pairs.

public static double? RegrIntercept<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.RegrIntercept(y, x, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: REGR_INTERCEPT(y, x) OVER (...)

RegrR2<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL REGR_R2() window function — the coefficient of determination (R²) of the regression of (argument1, argument2) pairs.

public static double? RegrR2<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.RegrR2(y, x, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: REGR_R2(y, x) OVER (...)

RegrSXX<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL REGR_SXX() window function — the sum of squares of the independent variable (argument2) over non-null pairs.

public static double? RegrSXX<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.RegrSXX(y, x, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: REGR_SXX(y, x) OVER (...)

RegrSXY<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL REGR_SXY() window function — the sum of products of the independent and dependent variables over non-null pairs.

public static double? RegrSXY<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.RegrSXY(y, x, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: REGR_SXY(y, x) OVER (...)

RegrSYY<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL REGR_SYY() window function — the sum of squares of the dependent variable (argument1) over non-null pairs.

public static double? RegrSYY<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.RegrSYY(y, x, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: REGR_SYY(y, x) OVER (...)

RegrSlope<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Generates the SQL REGR_SLOPE() window function — the slope of the least-squares-fit linear equation of (argument1, argument2) pairs.

public static double? RegrSlope<T1, T2>(this Sql.IWindowFunction window, T1 argument1, T2 argument2, Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument1 T1
argument2 T2
func Func<WindowFunctionBuilder.IBivariateAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T1
T2

Remarks

Syntax: Sql.Window.RegrSlope(y, x, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: REGR_SLOPE(y, x) OVER (...)

RowNumber(IWindowFunction, Func<IOPartitionROrderFinal, IDefinedFunction>)

Generates SQL ROW_NUMBER() window function. Assigns a unique sequential integer to each row within a partition.

public static long RowNumber(this Sql.IWindowFunction window, Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
func Func<WindowFunctionBuilder.IOPartitionROrderFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long

Remarks

Syntax: Sql.Window.RowNumber(f => f.[PartitionBy(...)].OrderBy(...)[.ThenBy(...)])

May not be supported by all database providers.

C# usage:

var query =
    from t in db.Table
    select new
    {
        RN1 = Sql.Window.RowNumber(f => f.PartitionBy(t.Category).OrderBy(t.Id)),
        RN2 = Sql.Window.RowNumber(f => f.OrderBy(t.Date)),
        RN3 = Sql.Window.RowNumber(f => f.PartitionBy(t.Dept).OrderBy(t.Date).ThenByDesc(t.Id)),
    };

Generated SQL:

SELECT
  ROW_NUMBER() OVER (PARTITION BY t.Category ORDER BY t.Id),
  ROW_NUMBER() OVER (ORDER BY t.Date),
  ROW_NUMBER() OVER (PARTITION BY t.Dept ORDER BY t.Date, t.Id DESC)
FROM Table t

StdDevPop<T>(IWindowFunction, T, Func<IAggregateFinal, IDefinedFunction>)

Generates the SQL STDDEV_POP() window function — the population standard deviation of values within the window.

public static double? StdDevPop<T>(this Sql.IWindowFunction window, T argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument T
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T

Remarks

Syntax: Sql.Window.StdDevPop(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: STDDEV_POP(expr) OVER (...)

StdDevSamp<T>(IWindowFunction, T, Func<IAggregateFinal, IDefinedFunction>)

Generates the SQL STDDEV_SAMP() window function — the sample standard deviation of values within the window.

public static double? StdDevSamp<T>(this Sql.IWindowFunction window, T argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument T
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T

Remarks

Syntax: Sql.Window.StdDevSamp(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: STDDEV_SAMP(expr) OVER (...)

StdDev<T>(IWindowFunction, T, Func<IAggregateFinal, IDefinedFunction>)

Generates the SQL STDDEV() window function (STDEV() on SQL Server) — the sample standard deviation of values within the window.

public static double? StdDev<T>(this Sql.IWindowFunction window, T argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument T
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T

Remarks

Syntax: Sql.Window.StdDev(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

C# usage:

Sql.Window.StdDev(t.Value, f => f.PartitionBy(t.Dept).OrderBy(t.Date))

Generated SQL (Oracle):

STDDEV(t.Value) OVER (PARTITION BY t.Dept ORDER BY t.Date)

Sum(IWindowFunction, decimal, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL SUM() window function. Computes the sum of values within the window frame.

public static decimal Sum(this Sql.IWindowFunction window, decimal argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument decimal
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

decimal

Remarks

Syntax: Sql.Window.Sum(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        Sum1 = Sql.Window.Sum(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum2 = Sql.Window.Sum(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Sum3 = Sql.Window.Sum(t.Salary, f => f.Filter(t.IsActive).PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum4 = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
    };

Generated SQL (PostgreSQL):

SELECT
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  SUM(t.Salary) FILTER (WHERE t.IsActive) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Sum(IWindowFunction, double, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL SUM() window function. Computes the sum of values within the window frame.

public static double Sum(this Sql.IWindowFunction window, double argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument double
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double

Remarks

Syntax: Sql.Window.Sum(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        Sum1 = Sql.Window.Sum(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum2 = Sql.Window.Sum(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Sum3 = Sql.Window.Sum(t.Salary, f => f.Filter(t.IsActive).PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum4 = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
    };

Generated SQL (PostgreSQL):

SELECT
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  SUM(t.Salary) FILTER (WHERE t.IsActive) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Sum(IWindowFunction, int, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL SUM() window function. Computes the sum of values within the window frame.

public static int Sum(this Sql.IWindowFunction window, int argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument int
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

int

Remarks

Syntax: Sql.Window.Sum(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

Use .Distinct() for SUM(DISTINCT x) OVER (...). DISTINCT in a window aggregate is not supported by most providers; where unsupported it throws a descriptive exception at query-translation time.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        Sum1 = Sql.Window.Sum(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum2 = Sql.Window.Sum(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Sum3 = Sql.Window.Sum(t.Salary, f => f.Filter(t.IsActive).PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum4 = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
        Sum5 = Sql.Window.Sum(t.Salary, f => f.Distinct().PartitionBy(t.Dept)),
    };

Generated SQL (PostgreSQL):

SELECT
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  SUM(t.Salary) FILTER (WHERE t.IsActive) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(DISTINCT t.Salary) OVER (PARTITION BY t.Dept)
FROM Table t

Sum(IWindowFunction, long, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL SUM() window function. Computes the sum of values within the window frame.

public static long Sum(this Sql.IWindowFunction window, long argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument long
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long

Remarks

Syntax: Sql.Window.Sum(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        Sum1 = Sql.Window.Sum(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum2 = Sql.Window.Sum(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Sum3 = Sql.Window.Sum(t.Salary, f => f.Filter(t.IsActive).PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum4 = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
    };

Generated SQL (PostgreSQL):

SELECT
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  SUM(t.Salary) FILTER (WHERE t.IsActive) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Sum(IWindowFunction, decimal?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL SUM() window function. Computes the sum of values within the window frame.

public static decimal? Sum(this Sql.IWindowFunction window, decimal? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument decimal?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

decimal?

Remarks

Syntax: Sql.Window.Sum(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        Sum1 = Sql.Window.Sum(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum2 = Sql.Window.Sum(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Sum3 = Sql.Window.Sum(t.Salary, f => f.Filter(t.IsActive).PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum4 = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
    };

Generated SQL (PostgreSQL):

SELECT
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  SUM(t.Salary) FILTER (WHERE t.IsActive) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Sum(IWindowFunction, double?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL SUM() window function. Computes the sum of values within the window frame.

public static double? Sum(this Sql.IWindowFunction window, double? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument double?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Remarks

Syntax: Sql.Window.Sum(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        Sum1 = Sql.Window.Sum(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum2 = Sql.Window.Sum(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Sum3 = Sql.Window.Sum(t.Salary, f => f.Filter(t.IsActive).PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum4 = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
    };

Generated SQL (PostgreSQL):

SELECT
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  SUM(t.Salary) FILTER (WHERE t.IsActive) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Sum(IWindowFunction, int?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL SUM() window function. Computes the sum of values within the window frame.

public static int? Sum(this Sql.IWindowFunction window, int? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument int?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

int?

Remarks

Syntax: Sql.Window.Sum(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        Sum1 = Sql.Window.Sum(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum2 = Sql.Window.Sum(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Sum3 = Sql.Window.Sum(t.Salary, f => f.Filter(t.IsActive).PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum4 = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
    };

Generated SQL (PostgreSQL):

SELECT
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  SUM(t.Salary) FILTER (WHERE t.IsActive) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Sum(IWindowFunction, long?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL SUM() window function. Computes the sum of values within the window frame.

public static long? Sum(this Sql.IWindowFunction window, long? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument long?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

long?

Remarks

Syntax: Sql.Window.Sum(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        Sum1 = Sql.Window.Sum(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum2 = Sql.Window.Sum(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Sum3 = Sql.Window.Sum(t.Salary, f => f.Filter(t.IsActive).PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum4 = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
    };

Generated SQL (PostgreSQL):

SELECT
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  SUM(t.Salary) FILTER (WHERE t.IsActive) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Sum(IWindowFunction, float?, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL SUM() window function. Computes the sum of values within the window frame.

public static float? Sum(this Sql.IWindowFunction window, float? argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument float?
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

float?

Remarks

Syntax: Sql.Window.Sum(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        Sum1 = Sql.Window.Sum(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum2 = Sql.Window.Sum(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Sum3 = Sql.Window.Sum(t.Salary, f => f.Filter(t.IsActive).PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum4 = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
    };

Generated SQL (PostgreSQL):

SELECT
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  SUM(t.Salary) FILTER (WHERE t.IsActive) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

Sum(IWindowFunction, float, Func<IAggregateFinal, IDefinedFunction>)

Generates SQL SUM() window function. Computes the sum of values within the window frame.

public static float Sum(this Sql.IWindowFunction window, float argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument float
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

float

Remarks

Syntax: Sql.Window.Sum(expr, f => f.[Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

The FILTER clause is natively supported by PostgreSQL and DuckDB. For other providers, it is emulated using CASE WHEN.

C# usage:

var query =
    from t in db.Table
    let wnd = Sql.Window.DefineWindow(f => f.PartitionBy(t.Dept).OrderBy(t.Date))
    select new
    {
        Sum1 = Sql.Window.Sum(t.Salary, f => f.PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum2 = Sql.Window.Sum(t.Salary, f => f.OrderBy(t.Date).RowsBetween.Unbounded.And.CurrentRow),
        Sum3 = Sql.Window.Sum(t.Salary, f => f.Filter(t.IsActive).PartitionBy(t.Dept).OrderBy(t.Date)),
        Sum4 = Sql.Window.Sum(t.Salary, f => f.UseWindow(wnd)),
    };

Generated SQL (PostgreSQL):

SELECT
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (ORDER BY t.Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  SUM(t.Salary) FILTER (WHERE t.IsActive) OVER (PARTITION BY t.Dept ORDER BY t.Date),
  SUM(t.Salary) OVER (PARTITION BY t.Dept ORDER BY t.Date)
FROM Table t

VarPop<T>(IWindowFunction, T, Func<IAggregateFinal, IDefinedFunction>)

Generates the SQL VAR_POP() window function — the population variance of values within the window.

public static double? VarPop<T>(this Sql.IWindowFunction window, T argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument T
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T

Remarks

Syntax: Sql.Window.VarPop(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: VAR_POP(expr) OVER (...)

VarSamp<T>(IWindowFunction, T, Func<IAggregateFinal, IDefinedFunction>)

Generates the SQL VAR_SAMP() window function — the sample variance of values within the window.

public static double? VarSamp<T>(this Sql.IWindowFunction window, T argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument T
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T

Remarks

Syntax: Sql.Window.VarSamp(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: VAR_SAMP(expr) OVER (...)

Variance<T>(IWindowFunction, T, Func<IAggregateFinal, IDefinedFunction>)

Generates the SQL VARIANCE() window function — the sample variance of values within the window.

public static double? Variance<T>(this Sql.IWindowFunction window, T argument, Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction> func)

Parameters

window Sql.IWindowFunction
argument T
func Func<WindowFunctionBuilder.IAggregateFinal, WindowFunctionBuilder.IDefinedFunction>

Returns

double?

Type Parameters

T

Remarks

Syntax: Sql.Window.Variance(expr, f => f.[Distinct()][.Filter(...)][.PartitionBy(...)][.OrderBy(...)][.RowsBetween|RangeBetween...])

Not supported by every provider. Where unsupported it throws a descriptive exception at query-translation time.

Generated SQL: VARIANCE(expr) OVER (...)

Table of Contents

Class WindowFunctionBuilder

Remarks

Methods

Average(IWindowFunction, byte, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, decimal, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, double, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, short, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, int, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, long, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, byte?, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, decimal?, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, double?, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, short?, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, int?, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, long?, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, float?, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Average(IWindowFunction, float, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Corr<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Parameters

Returns

Type Parameters

Remarks

Count(IWindowFunction, Func<IOFilterOPartitionOOrderOFrameFinal, IDefinedFunction>)

Parameters

Returns

Remarks

Count(IWindowFunction, object?, Func<IAggregateFinal, IDefinedFunction>)

Parameters

Returns

Remarks

CovarPop<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Parameters

Returns

Type Parameters

Remarks

CovarSamp<T1, T2>(IWindowFunction, T1, T2, Func<IBivariateAggregateFinal, IDefinedFunction>)

Parameters