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
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 only)
                               //.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(e.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 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.

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

OrderItems.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))
                     })

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]