Window Functions Support (RowNumber, Sum, Average, Min, Max)

Required Nuget Package:

Motivation

Enables the usage of window functions in Entity Framework Core queries.

Function	SQL Server	PostgreSQL	SQLite
ROW_NUMBER	Yes	Yes	Yes
NTILE	Yes	Yes	Yes
SUM	Yes	Yes	No
AVG	Yes	Yes	No
MIN	Yes	Yes	No
MAX	Yes	Yes	No

Usage

1. Activate the window functions support

var services = new ServiceCollection()
                       .AddDbContext<DemoDbContext>(builder => builder
                               .UseSqlServer("conn-string",
                                             options => options.AddWindowFunctionsSupport())
                               // or PostgreSQL
                               //.UseNpgsql("conn-string",
                               //           options => options.AddWindowFunctionsSupport())
                               // or SQLite (RowNumber and NTile)
                               //.UseSqlite("conn-string",
                               //           options => options.AddWindowFunctionsSupport())

2. Use Extension methods RowNumber, Sum, Average, Min, Max

2.1 RowNumber with ORDER BY only

1 column only

DbContext.OrderItems.Select(o => new
                     {
                        RowNumber = EF.Functions.RowNumber(EF.Functions.OrderBy(o.ProductId))
                     })

Use AsSubQuery before using the RowNumber in Where

DbContext.OrderItems.Select(o => new
                     {
                        RowNumber = EF.Functions.RowNumber(EF.Functions.OrderBy(o.ProductId))
                     })
                     .AsSubQuery()
                     .Where(i => i.RowNumber == 1)

ORDER BY ... DESC

DbContext.OrderItems.Select(o => new
                     {
                        RowNumber = EF.Functions.RowNumber(EF.Functions.OrderByDescending(o.ProductId))
                     })

Order by multiple columns

DbContext.OrderItems.Select(o => new
                     {
                        RowNumber = EF.Functions.RowNumber(EF.Functions.OrderBy(o.ProductId)
                                                                       .ThenByDescending(o.OrderId))
                     })

2.2 RowNumber with PARTITION BY and ORDER BY

First parameter defines the PARTITION BY part and the second the ORDER BY part.

// partition by "ProductId"
// order by "OrderId" and "Count"
DbContext.OrderItems.Select(o => new
                     {
                        RowNumber = EF.Functions.RowNumber(o.ProductId,
                                                           EF.Functions.OrderBy(o.OrderId)
                                                                       .ThenBy(o.Count))
                      })

// partition by "ProductId" and "OrderId"
// order by "Count"
DbContext.OrderItems.Select(o => new
                     {
                        RowNumber = EF.Functions.RowNumber(o.ProductId, o.OrderId,
                                                           EF.Functions.OrderBy(o.Count))
                      })

Supports up to 16 PARTITION BY columns.

2.3 NTILE bucketing

Available on SQL Server, PostgreSQL, and SQLite.

NTILE(n) distributes ordered rows across n buckets and returns the bucket number for each row. The return type is provider-specific: SQL Server returns long (T-SQL NTILE yields bigint), while PostgreSQL and SQLite return int. Make sure the receiving DTO property uses the matching type.

// SQL Server: NTILE([n]) OVER (ORDER BY [o].[Price])
DbContext.OrderItems.Select(o => new
                     {
                        Bucket = EF.Functions.NTile(4, EF.Functions.OrderBy(o.Price))
                     })

// with PARTITION BY
DbContext.OrderItems.Select(o => new
                     {
                        Bucket = EF.Functions.NTile(4, o.ProductId, EF.Functions.OrderBy(o.Price))
                     })

The bucket count can be a constant or a captured variable; it is emitted as a SQL parameter.

SQL Server requires ORDER BY (per T-SQL spec) — the API enforces this; every overload takes a WindowFunctionOrderByClause as the last argument.

PostgreSQL and SQLite allow omitting ORDER BY — overloads exist with and without it.

Up to 5 PARTITION BY columns are supported.

2.4 Functions Sum, Average, Min and Max with and without ORDER BY

Available on SQL Server and PostgreSQL.

The first parameter is a column or mathematical expression to aggregate. Afterwards we specify the columns to PARTITION BY. As the last (optional) parameter we can define an ORDER BY clause.

DbContext.OrderItems.Select(e => new
                     {
                        // SUM([o].[Count] * 2) OVER (PARTITION BY [o].[Name]) AS [Sum]
                        Sum = EF.Functions.Sum(e.Count * 2,
                                               e.Name)
                     })

DbContext.OrderItems.Select(e => new
                     {
                        // SUM([o].[Count] * 2) OVER (PARTITION BY [o].[Name] ORDER BY [o].[Id])
                        Sum = EF.Functions.Sum(e.Count * 2,
                                               e.Name,
                                               EF.Functions.OrderBy(e.Id))
                     })

PostgreSQL note (Guid/uuid columns): PostgreSQL has no native max(uuid)/min(uuid) aggregate. The provider handles this transparently — both the windowed Min/Max (EF.Functions.Min/Max) and the regular GroupBy().Min/Max over a Guid-mapped uuid column are rewritten to min(col::text)::uuid / max(col::text)::uuid (text ordering of canonical uuid strings matches uuid byte ordering, so the result is identical). No configuration or workaround is required. SQL Server (uniqueidentifier) and SQLite (Guid as text/blob) support Min/Max over Guid natively and are unaffected.

Examples

[RowNumber] Fetch correct translation according to specific priority

This example is provided by the community. For more info see github issue 34.

Use case: Given is an entity Product. A product can have multiple translations. We need to fetch products with correct translations according to following rules:

• First, try to get the translation in the user's language
• If there is none, then try to get the application default language
• otherwise, get any available translation

There are multiple ways to write the query.

Approach 1: with inlined ternary

var userLanguage = 2; //Portuguese
var applicationLanguage = 1; //English

var productList = await ctx.ProductTranslations
                            .Where(pt=> pt.Field == ProductTranslation.FieldEnum.Name)
                            .Select(pt=> new
                            {
                               pt.ProductId,
                               pt.Product,
                               Name = pt.Text,
                               LangOrder = EF.Functions.RowNumber(
                                   pt.ProductId,
                                   EF.Functions.OrderBy(
                                       pt.LanguageId == userLanguage ? 0 //user's language has priority 0
                                       : pt.LanguageId == applicationLanguage ? 1 //application language has priority 1
                                       : 2 )) //Any other language has priority 2
                            })
                            .AsSubQuery()
                            .Where(i => i.LangOrder == 1)
                            .OrderBy(l => l.Name)
                            .ToListAsync();

Approach 2: definition of the ternary in a separate Select

var productList = await ctx.ProductTranslations
                            .Where(pt=> pt.Field == ProductTranslation.FieldEnum.Name)
                            .Select(pt=> new
                            {
                                Translation = pt,
                                Priority =  pt.LanguageId == userLanguage ? 0 //user's language has priority 0
                                            : pt.LanguageId == applicationLanguage ? 1 //application language has priority 1
                                            : 2 //Any other language has priority 2
                            })
                            .Select(i=> new
                            {
                                i.Translation.ProductId,
                                i.Translation.Product,
                                Name = i.Translation.Text,
                                LangOrder = EF.Functions.RowNumber(
                                    i.Translation.ProductId, // partition by ProductId
                                    EF.Functions.OrderBy(i.Priority)) // order the partition by Priority
                            })
                            .AsSubQuery()
                            .Where(i => i.LangOrder == 1)
                            .OrderBy(l => l.Name)
                            .ToListAsync();

Generated SQL (SQL Server):

SELECT [t].[ProductId], [t].[Id], [t].[Name], [t].[LangOrder]
FROM (
    SELECT [p].[ProductId], [p0].[Id], [p].[Text] AS [Name], ROW_NUMBER() OVER(PARTITION BY [p].[ProductId] ORDER BY CASE
        WHEN [p].[LanguageId] = @__userLanguage_2 THEN 0
        WHEN [p].[LanguageId] = @__applicationLanguage_3 THEN 1
        ELSE 2
    END) AS [LangOrder]
    FROM [demo].[ProductTranslations] AS [p]
    INNER JOIN [demo].[Products] AS [p0] ON [p].[ProductId] = [p0].[Id]
    WHERE [p].[Field] = N'Name'
) AS [t]
WHERE [t].[LangOrder] = 1
ORDER BY [t].[Name]