Zomp EF Core Extensions

This repository is home to two packages which extend Entity Framework Core:

• Zomp.EFCore.WindowFunctions
• Zomp.EFCore.BinaryFunctions

Zomp.EFCore.WindowFunctions

Provides Window functions or analytics functions for providers. Currently supported for:

Provider	Package
SQL Server
PostgreSQL
SQLite

Window functions supported:

• MIN
• MAX
• SUM
• AVG
• COUNT
• ROW_NUMBER
• RANK
• DENSE_RANK
• PERCENT_RANK
• LEAD
• LAG

Installation

To add provider-specific library use:

dotnet add package Zomp.EFCore.WindowFunctions.SqlServer
dotnet add package Zomp.EFCore.WindowFunctions.Npgsql
dotnet add package Zomp.EFCore.WindowFunctions.Sqlite

To add provider-agnostic library use:

dotnet add package Zomp.EFCore.WindowFunctions

Set up your specific provider to use Window Functions with DbContextOptionsBuilder.UseWindowFunctions. For example here is the SQL Server syntax:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(
        myConn,
        sqlOptions => sqlOptions.UseWindowFunctions());
}

Basic usage

LINQ query

using var dbContext = new MyDbContext();
var query = dbContext.TestRows
.Select(r => new
{
    Max = EF.Functions.Max(
        r.Col1,
        EF.Functions.Over()
            .OrderBy(r.Col2)),
});

translates into the following SQL on SQL Server:

SELECT MAX([t].[Col1]) OVER(ORDER BY [t].[Col2]) AS [Max]
FROM [TestRows] AS [t]
ORDER BY [t].[Id]

Advanced usage

This example shows:

• Partition clause (can be chained)
• Order by clause
  • Can me chained
  • Used in ascending or descending order
• Range or Rows clause

using var dbContext = new MyDbContext();
var query = dbContext.TestRows
.Select(r => new
{
    Max = EF.Functions.Max(
        r.Col1,
        EF.Functions.Over()
            .PartitionBy(r.Col2).ThenBy(r.Col3)
            .OrderBy(r.Col4).ThenByDescending(r.Col5)
                .Rows().FromUnbounded().ToCurrentRow()),
});

Zomp.EFCore.BinaryFunctions

Provides Window functions or analytics functions for providers. Currently supported for:

Provider	Package
SQL Server
PostgreSQL
SQLite

The following extension methods are available

• DbFunctions.GetBytes - converts an expression into binary expression
• DbFunctions.ToValue<T> - Converts binary expression to type T
• DbFunctions.BinaryCast<TFrom, TTo> - Converts one type to another by taking least significant bytes when overflow occurs.
• DbFunctions.Concat - concatenates two binary expressions
• DbFunctions.Substring - Returns part of a binary expression

Usage

Set up your specific provider to use Binary Functions with DbContextOptionsBuilder.UseWindowFunctions. For example here is the SQL Server syntax:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(
        myConn,
        sqlOptions => sqlOptions.UseBinaryFunctions());
}

LINQ query

using var dbContext = new MyDbContext();
var query = dbContext.TestRows
    .Select(r => EF.Functions.GetBytes(r.Id));

translates into the following SQL on SQL Server:

SELECT CAST([t].[Id] AS binary(4))
FROM [TestRows] AS [t]

Applications

Last non null puzzle

One problem window functions are solving is displaying last non-null values for a given column / expressions. The problem is described in Itzik Ben-Gan's article. Below are 2 effective approaches of solving this issue.

Binary approach

Solution 2 of the article above uses both binary functions and window functions. Here is how it can be combined using this library:

// Relies on Max over binary.
// Currently works with SQL Server only.
var query = dbContext.TestRows
.Select(r => new
{
    LastNonNull =
    EF.Functions.ToValue<int>(
        EF.Functions.Substring(
            EF.Functions.Max(
                EF.Functions.Concat(
                    EF.Functions.GetBytes(r.Id),
                    EF.Functions.GetBytes(r.Col1)),
                EF.Functions.Over().OrderBy(r.Id)),
            5,
            4)),
});

In case of limitations of combining bytes (SQLite) and window max function on binary data (PostgreSQL) it might be possible to combine columns into 8-bit integer expression(s) and perform max window function on it:

var query = dbContext.TestRows
.Select(r => new
{
    LastNonNull =
    EF.Functions.BinaryCast<long, int>(
        EF.Functions.Max(
            r.Col1.HasValue ? r.Id * (1L << 32) | r.Col1.Value & uint.MaxValue : (long?)null,
            EF.Functions.Over().OrderBy(r.Id))),
});

LAG approach

Starting with SQL Server 2022 (16.x) it is possible to use LAG with IGNORE NULLS to retrieve last non-null value. Ensure the latest cumulative update is applied due to a bug fix.

Use the following expression:

Expression<Func<TestRow, int?>> lastNonNullExpr = r => EF.Functions.Lag(r.Col1, 0, NullHandling.IgnoreNulls, EF.Functions.Over().OrderBy(r.Id)

More SQL Server related information on the LAG function here available here.

Note: PostgreSQL and SQLite don't support RESPECT NULLS / IGNORE NULLS at this time.

Examples

See the

• Zomp.EFCore.WindowFunctions.Testing
• Zomp.EFCore.BinaryFunctions.Testing
• Zomp.EFCore.Combined.Testing

projects for more examples.