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

Required Nuget Package:

Motivation

Enables the usage of function ROW_NUMBER (SQLite and SQL Server) in Entity Framework Core queries. The functions SUM, AVG, MIN and MAX are available in MS SQL Server only.

Usage

1. Activate the RowNumber support

var services = new ServiceCollection() 
                       .AddDbContext<DemoDbContext>(builder => builder
                               .UseSqlServer("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.1 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))
                      })

2.2 [SQL Server] Functions Sum, Average, Min and Max with and without ORDER BY

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:

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]