编写对应T-SQL ROW_NUMBER() OVER子句的C# LINQ泛型扩展方法

Got it, let's build a LINQ extension method that mirrors the exact behavior and keyword style of T-SQL's ROW_NUMBER() OVER(PARTITION BY [col1] ORDER BY [col2] DESC)—no workarounds, just direct, one-to-one logic mapping.

Core Approach #

The T-SQL clause does three key things in sequence:

1. PARTITION BY: Splits the dataset into subsets based on a specified column/expression
2. ORDER BY: Sorts each subset individually using the given column/expression
3. ROW_NUMBER(): Assigns a sequential number starting at 1 to every item in the sorted subset

Our LINQ extension will replicate this flow exactly, using generic methods to work with any data type.

The Extension Method Code #

First, here's the base generic extension that covers both ascending and descending sorting, plus optional overloads for even more T-SQL-like syntax:

using System;
using System.Collections.Generic;
using System.Linq;

public static class LinqWindowFunctions
{
    /// <summary>
    /// Replicates T-SQL's ROW_NUMBER() OVER(PARTITION BY partitionKey ORDER BY orderKey [ASC/DESC])
    /// </summary>
    /// <typeparam name="T">Type of the source elements</typeparam>
    /// <typeparam name="TPartitionKey">Type of the partition key</typeparam>
    /// <typeparam name="TOrderKey">Type of the order key</typeparam>
    /// <param name="source">Source enumerable</param>
    /// <param name="partitionBy">Selector for the PARTITION BY column/expression</param>
    /// <param name="orderBy">Selector for the ORDER BY column/expression</param>
    /// <param name="isDescending">Flag to control sort direction (default: ascending)</param>
    /// <returns>Enumerable of tuples containing the original item and its row number</returns>
    public static IEnumerable<(T Item, int RowNumber)> RowNumberOver<T, TPartitionKey, TOrderKey>(
        this IEnumerable<T> source,
        Func<T, TPartitionKey> partitionBy,
        Func<T, TOrderKey> orderBy,
        bool isDescending = false)
    {
        // Guard clauses for null inputs
        if (source == null) throw new ArgumentNullException(nameof(source));
        if (partitionBy == null) throw new ArgumentNullException(nameof(partitionBy));
        if (orderBy == null) throw new ArgumentNullException(nameof(orderBy));

        // Step 1: Partition the source (matches PARTITION BY)
        var partitions = source.GroupBy(partitionBy);

        foreach (var partition in partitions)
        {
            // Step 2: Sort the partition (matches ORDER BY [ASC/DESC])
            var orderedPartition = isDescending
                ? partition.OrderByDescending(orderBy)
                : partition.OrderBy(orderBy);

            // Step 3: Assign row numbers starting at 1
            int currentRow = 1;
            foreach (var item in orderedPartition)
            {
                yield return (item, currentRow++);
            }
        }
    }

    // Optional overloads for more T-SQL-like readability
    public static IEnumerable<(T Item, int RowNumber)> RowNumberOverOrderBy<T, TPartitionKey, TOrderKey>(
        this IEnumerable<T> source,
        Func<T, TPartitionKey> partitionBy,
        Func<T, TOrderKey> orderBy)
    {
        return source.RowNumberOver(partitionBy, orderBy, isDescending: false);
    }

    public static IEnumerable<(T Item, int RowNumber)> RowNumberOverOrderByDescending<T, TPartitionKey, TOrderKey>(
        this IEnumerable<T> source,
        Func<T, TPartitionKey> partitionBy,
        Func<T, TOrderKey> orderBy)
    {
        return source.RowNumberOver(partitionBy, orderBy, isDescending: true);
    }
}

How to Use It #

Let's test this with a sample Employee class to mirror a common T-SQL use case:

public class Employee
{
    public int DepartmentId { get; set; }
    public string FullName { get; set; }
    public decimal AnnualSalary { get; set; }
}

// Sample data
var team = new List<Employee>
{
    new Employee { DepartmentId = 10, FullName = "Emma Wilson", AnnualSalary = 95000 },
    new Employee { DepartmentId = 10, FullName = "Liam Davis", AnnualSalary = 88000 },
    new Employee { DepartmentId = 20, FullName = "Olivia Smith", AnnualSalary = 82000 },
    new Employee { DepartmentId = 20, FullName = "Noah Brown", AnnualSalary = 90000 },
    new Employee { DepartmentId = 20, FullName = "Ava Garcia", AnnualSalary = 78000 },
};

// Equivalent to T-SQL:
// SELECT *, ROW_NUMBER() OVER(PARTITION BY DepartmentId ORDER BY AnnualSalary DESC) AS RowNum
// FROM Employees
var rankedEmployees = team.RowNumberOverOrderByDescending(
    partitionBy: emp => emp.DepartmentId,
    orderBy: emp => emp.AnnualSalary);

// Output the results
foreach (var (emp, rowNum) in rankedEmployees)
{
    Console.WriteLine($"Dept {emp.DepartmentId} | {emp.FullName} | Salary: ${emp.AnnualSalary:N0} | Row #: {rowNum}");
}

Expected Output #

Dept 10 | Emma Wilson | Salary: $95,000 | Row #: 1
Dept 10 | Liam Davis | Salary: $88,000 | Row #: 2
Dept 20 | Noah Brown | Salary: $90,000 | Row #: 1
Dept 20 | Olivia Smith | Salary: $82,000 | Row #: 2
Dept 20 | Ava Garcia | Salary: $78,000 | Row #: 3

Key Notes #

• Direct Mapping: This implementation follows the exact order of operations as T-SQL: partition first, sort each partition, then assign row numbers. No pre-sorting the entire dataset or using indirect grouping tricks.
• Type Safety: Generic parameters ensure type safety for any data type you're working with.
• Flexibility: The base method supports both ascending and descending sorts, while the overloads let you write code that reads almost exactly like the T-SQL clause.

内容的提问来源于stack exchange，提问作者Burak Kalafat