Window Functions & Ranking

This recipe shows how to use SQL window functions in EF Core LINQ queries via the ExpressiveSharp.EntityFrameworkCore.RelationalExtensions package. Coverage includes ranking (ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST), aggregate (SUM, AVG, COUNT, MIN, MAX), and navigation (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE) functions.

Setup

Install the package:

dotnet add package ExpressiveSharp.EntityFrameworkCore.RelationalExtensions

Enable it via the plugin architecture:

var options = new DbContextOptionsBuilder<MyDbContext>()
    .UseSqlite(connection)
    .UseExpressives(o => o.UseRelationalExtensions())
    .Options;

Add the using directive where you write queries:

using ExpressiveSharp.EntityFrameworkCore.RelationalExtensions.WindowFunctions;

ROW_NUMBER for Pagination

ROW_NUMBER assigns a sequential number to each row. This is useful for implementing pagination with guaranteed deterministic ordering:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
    public string Category { get; set; } = "";
}

var page = dbContext.Products
    .Select(p => new
    {
        p.Id,
        p.Name,
        p.Price,
        RowNum = WindowFunction.RowNumber(
            Window.OrderBy(p.Price))
    })
    .Where(x => x.RowNum > 20 && x.RowNum <= 30)  // page 3 (10 items per page)
    .ToList();

Generated SQL (SQLite):

SELECT "p"."Id", "p"."Name", "p"."Price",
       ROW_NUMBER() OVER(ORDER BY "p"."Price") AS "RowNum"
FROM "Products" AS "p"

TIP

While EF Core's .Skip() and .Take() handle most pagination, ROW_NUMBER is essential when you need the row number as a value in the result set, or when combining pagination with window-based partitioning.

RANK for Leaderboards

RANK assigns a rank to each row, with gaps for ties. If two items tie at rank 2, the next rank is 4 (not 3):

public class Player
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public int Score { get; set; }
    public string League { get; set; } = "";
}

var leaderboard = dbContext.Players
    .Select(p => new
    {
        p.Name,
        p.Score,
        Rank = WindowFunction.Rank(
            Window.OrderByDescending(p.Score))
    })
    .OrderBy(x => x.Rank)
    .ToList();

Generated SQL (SQLite):

SELECT "p"."Name", "p"."Score",
       RANK() OVER(ORDER BY "p"."Score" DESC) AS "Rank"
FROM "Players" AS "p"
ORDER BY "Rank"

Example output:

Name	Score	Rank
Alice	950	1
Bob	920	2
Carol	920	2
Dave	890	4

Notice that Carol and Bob both get rank 2 (tied), and Dave gets rank 4 (gap at 3).

DENSE_RANK for Continuous Ranking

DENSE_RANK works like RANK but without gaps. Ties share a rank, and the next distinct value gets the next consecutive rank:

var rankings = dbContext.Players
    .Select(p => new
    {
        p.Name,
        p.Score,
        DenseRank = WindowFunction.DenseRank(
            Window.OrderByDescending(p.Score))
    })
    .OrderBy(x => x.DenseRank)
    .ToList();

Generated SQL (SQLite):

SELECT "p"."Name", "p"."Score",
       DENSE_RANK() OVER(ORDER BY "p"."Score" DESC) AS "DenseRank"
FROM "Players" AS "p"
ORDER BY "DenseRank"

Example output:

Name	Score	DenseRank
Alice	950	1
Bob	920	2
Carol	920	2
Dave	890	3

Dave gets rank 3 instead of 4 -- no gap after the tie.

NTILE for Bucketing

NTILE distributes rows into a specified number of roughly equal groups. This is useful for computing quartiles, deciles, or percentile buckets:

// Divide products into 4 price quartiles
var quartiles = dbContext.Products
    .Select(p => new
    {
        p.Name,
        p.Price,
        PriceQuartile = WindowFunction.Ntile(4,
            Window.OrderBy(p.Price))
    })
    .ToList();

Generated SQL (SQLite):

SELECT "p"."Name", "p"."Price",
       NTILE(4) OVER(ORDER BY "p"."Price") AS "PriceQuartile"
FROM "Products" AS "p"

Example output for 12 products:

Name	Price	PriceQuartile
Widget A	5.00	1
Widget B	10.00	1
Widget C	15.00	1
Widget D	25.00	2
Widget E	30.00	2
Widget F	40.00	2
Widget G	50.00	3
Widget H	60.00	3
Widget I	75.00	3
Widget J	100.00	4
Widget K	150.00	4
Widget L	200.00	4

Use NTILE(10) for deciles, NTILE(100) for percentiles.

PARTITION BY for Per-Group Ranking

Window.PartitionBy(...) restricts the window function to operate within each group independently. This is the SQL equivalent of "rank within each category":

// Rank each customer's orders by total, per customer
var rankedOrders = dbContext.Orders
    .Select(o => new
    {
        o.Id,
        o.CustomerId,
        o.GrandTotal,
        RankInCustomer = WindowFunction.Rank(
            Window.PartitionBy(o.CustomerId)
                  .OrderByDescending(o.GrandTotal))
    })
    .ToList();

Generated SQL (SQLite):

SELECT "o"."Id", "o"."CustomerId", "o"."GrandTotal",
       RANK() OVER(PARTITION BY "o"."CustomerId" ORDER BY "o"."GrandTotal" DESC) AS "RankInCustomer"
FROM "Orders" AS "o"

Top N per Group

Combine PARTITION BY with filtering to get the top N items per group:

// Top 3 most expensive products in each category
var topPerCategory = dbContext.Products
    .Select(p => new
    {
        p.Id,
        p.Name,
        p.Category,
        p.Price,
        RankInCategory = WindowFunction.RowNumber(
            Window.PartitionBy(p.Category)
                  .OrderByDescending(p.Price))
    })
    .Where(x => x.RankInCategory <= 3)
    .ToList();

Generated SQL (SQLite):

SELECT "p"."Id", "p"."Name", "p"."Category", "p"."Price",
       ROW_NUMBER() OVER(PARTITION BY "p"."Category" ORDER BY "p"."Price" DESC) AS "RankInCategory"
FROM "Products" AS "p"

Multiple Sort Columns

Chain .ThenBy() and .ThenByDescending() for multi-column ordering within the window:

var results = dbContext.Orders
    .Select(o => new
    {
        o.Id,
        RowNum = WindowFunction.RowNumber(
            Window.PartitionBy(o.CustomerId)
                  .OrderByDescending(o.GrandTotal)
                  .ThenBy(o.CreatedDate))
    })
    .ToList();

Generated SQL:

SELECT "o"."Id",
       ROW_NUMBER() OVER(PARTITION BY "o"."CustomerId"
                         ORDER BY "o"."GrandTotal" DESC, "o"."CreatedDate" ASC)
FROM "Orders" AS "o"

Indexed Select for Row Numbering

The RelationalExtensions package also transforms .Select((element, index) => ...) into ROW_NUMBER-based SQL. This uses the overload of Select that provides the row index:

var numbered = dbContext.Products
    .OrderBy(p => p.Name)
    .Select((p, index) => new
    {
        RowNumber = index + 1,
        p.Name,
        p.Price
    })
    .ToList();

Generated SQL (SQLite):

SELECT ROW_NUMBER() OVER(ORDER BY "p"."Name") AS "RowNumber",
       "p"."Name",
       "p"."Price"
FROM "Products" AS "p"
ORDER BY "p"."Name"

Window Specification Fluent API

Build window specifications with the fluent API:

Method	SQL
Window.OrderBy(expr)	ORDER BY expr ASC
Window.OrderByDescending(expr)	ORDER BY expr DESC
Window.PartitionBy(expr)	PARTITION BY expr
.ThenBy(expr)	Additional ORDER BY expr ASC
.ThenByDescending(expr)	Additional ORDER BY expr DESC

The type system ensures correct usage:

• Window.OrderBy(...) returns OrderedWindowDefinition -- ready for any window function
• Window.PartitionBy(...) returns PartitionedWindowDefinition -- must chain .OrderBy(...) before passing to Rank or DenseRank
• .ThenBy(...) and .ThenByDescending(...) return OrderedWindowDefinition

Supported Providers

Window functions are supported across all major relational providers:

Provider	Supported	Notes
SQLite	Yes
SQL Server	Yes	NTH_VALUE is not supported
PostgreSQL	Yes
MySQL	Yes
Oracle	Yes

The generated SQL uses standard window function syntax, which all these providers support.

Aggregate Window Functions

In addition to ranking, ExpressiveSharp supports aggregate window functions (SUM, AVG, COUNT, MIN, MAX) with optional frame clauses. These are useful for running totals, moving averages, and cumulative min/max:

using ExpressiveSharp.EntityFrameworkCore.RelationalExtensions.WindowFunctions;

var results = dbContext.Orders
    .Select(o => new
    {
        o.Id,
        o.Price,
        RunningTotal = WindowFunction.Sum(o.Price,
            Window.PartitionBy(o.CustomerId)
                  .OrderBy(o.Price)
                  .RowsBetween(WindowFrameBound.UnboundedPreceding, WindowFrameBound.CurrentRow)),
        MovingAvg = WindowFunction.Average(o.Price,
            Window.OrderBy(o.Price)
                  .RowsBetween(WindowFrameBound.Preceding(2), WindowFrameBound.CurrentRow))
    })
    .ToList();

See the Window Functions guide for the full frame specification reference.

Frames apply to aggregate functions only

The SQL standard forbids frame clauses on ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE). SQL Server and PostgreSQL reject the syntax. Aggregate functions (SUM, AVG, COUNT, MIN, MAX) and value functions (FIRST_VALUE, LAST_VALUE, NTH_VALUE) support frames.

Tips

Combine with other ExpressiveSharp features

Window functions work alongside [Expressive] members, null-conditional operators, and switch expressions in the same query. Use ExpressiveDbSet<T> or .AsExpressive() for the full experience.

PARTITION BY for analytics

Use Window.PartitionBy(...) to compute per-group metrics without a subquery or GROUP BY. This keeps all original rows in the result set while adding aggregate-like values.

Filtering on window results

Some databases do not support WHERE directly on window function results. EF Core typically wraps the query in a subquery to make this work, but check your provider's behavior.

See Also

• Computed Entity Properties -- combine computed properties with window functions
• Modern Syntax in LINQ Chains -- modern syntax alongside window functions
• Scoring and Classification -- CASE expressions and window-based ranking together