解决.NET9.0下EFCore不再内置支持ROW_NUMBER()的兼容实现

前言

NET程序员在上个月迎来了一个好消息，微软发布了.NET9.0 RTM，带来了许多新特性。然而，许多开发者的软硬件设备仍未跟上时代的步伐。例如，个人电脑仍在运行Win7，公司服务器仍在使用MSSQL2005-2008。

这引出了我们本文要探讨的问题。由于微软在EFcore3.1后不再内置支持ROW_NUMBER()，因此需要自行处理兼容分页的代码。当然，如果开发者对EFCore没有依赖，也可以选择其他的ORM选型。另外，如果不想折腾EFCore，也可以使用万能的RawSql拼接执行。

最近，我发布的Nuget包收到了一位国外程序员朋友提出的问题，因此我立即行动起来。

在EFCore9中，以前兼容良好的ROW_NUMBER()代码，在升级后发现无法运行。这主要是因为新版本的EFCore9进行了许多破坏性更新，我们不得不研究新的底层代码。

兼容实现

之前发布过一个Nuget包，代码主要是基于以前兼容EFCore7适配到EFCore8的兼容。代码变化不大，但升级到EFCore9后发现底层的API全变了，不得不重新实现一遍。

以下是兼容EFCore9的代码部分:

#if NET9_0_OR_GREATER

#pragma warning disable EF1001 // Internal EF Core API usage.

namespace Biwen.EFCore.UseRowNumberForPaging;

using Microsoft.EntityFrameworkCore.Query;
using System.Collections.Generic;

public class SqlServer2008QueryTranslationPostprocessorFactory(
    QueryTranslationPostprocessorDependencies dependencies,
    RelationalQueryTranslationPostprocessorDependencies relationalDependencies) : IQueryTranslationPostprocessorFactory
{
    private readonly QueryTranslationPostprocessorDependencies _dependencies = dependencies;
    private readonly RelationalQueryTranslationPostprocessorDependencies _relationalDependencies = relationalDependencies;

    public virtual QueryTranslationPostprocessor Create(QueryCompilationContext queryCompilationContext)
        => new SqlServer2008QueryTranslationPostprocessor(
            _dependencies,
            _relationalDependencies,
            queryCompilationContext);

    public class SqlServer2008QueryTranslationPostprocessor(QueryTranslationPostprocessorDependencies dependencies, RelationalQueryTranslationPostprocessorDependencies relationalDependencies, QueryCompilationContext queryCompilationContext) :
        RelationalQueryTranslationPostprocessor(dependencies, relationalDependencies, (RelationalQueryCompilationContext)queryCompilationContext)
    {
        public override Expression Process(Expression query)
        {
            query = base.Process(query);
            query = new Offset2RowNumberConvertVisitor(query, RelationalDependencies.SqlExpressionFactory).Visit(query);
            return query;
        }
        internal class Offset2RowNumberConvertVisitor(
            Expression root,
            ISqlExpressionFactory sqlExpressionFactory) : ExpressionVisitor
        {
            private readonly Expression root = root;
            private readonly ISqlExpressionFactory sqlExpressionFactory = sqlExpressionFactory;
            private const string SubTableName = "subTbl";
            private const string RowColumnName = "_Row_";//下标避免数据表存在字段

            protected override Expression VisitExtension(Expression node) => node switch
            {
                ShapedQueryExpression shapedQueryExpression => shapedQueryExpression.Update(Visit(shapedQueryExpression.QueryExpression), shapedQueryExpression.ShaperExpression),
                SelectExpression se => VisitSelect(se),
                _ => base.VisitExtension(node)
            };

            private SelectExpression VisitSelect(SelectExpression selectExpression)
            {
                var oldOffset = selectExpression.Offset;
                if (oldOffset == null)
                    return selectExpression;

                var oldLimit = selectExpression.Limit;
                var oldOrderings = selectExpression.Orderings;
                var newOrderings = oldOrderings switch
                {
                    { Count: > 0 } when oldLimit != null || selectExpression == root => oldOrderings.ToList(),
                    _ => []
                };

                var rowOrderings = oldOrderings.Any() switch
                {
                    true => oldOrderings,
                    false => [new OrderingExpression(new SqlFragmentExpression("(SELECT 1)"), true)]
                };

                var oldSelect = selectExpression;

                var rowNumberExpression = new RowNumberExpression([], rowOrderings, oldOffset.TypeMapping);
                // 创建子查询
                IList<ProjectionExpression> projections = [new ProjectionExpression(rowNumberExpression, RowColumnName),];

                var subquery = new SelectExpression(
                    SubTableName,
                    oldSelect.Tables,
                    oldSelect.Predicate,
                    oldSelect.GroupBy,
                    oldSelect.Having,
                    [.. oldSelect.Projection, .. projections],
                    oldSelect.IsDistinct,
                    [],//排序已经在rowNumber中了
                    null,
                    null,
                    null,
                    null);

                //构造新的条件:
                var and1 = sqlExpressionFactory.GreaterThan(
                    new ColumnExpression(RowColumnName, SubTableName, typeof(int), null, true),
                    oldOffset);
                var and2 = sqlExpressionFactory.LessThanOrEqual(
                    new ColumnExpression(RowColumnName, SubTableName, typeof(int), null, true),
                    sqlExpressionFactory.Add(oldOffset, oldLimit));

                var newPredicate = sqlExpressionFactory.AndAlso(and1, and2);

                //新的Projection:
                var newProjections = oldSelect.Projection.Select(e =>
                {
                    if (e is { Expression: ColumnExpression col })
                    {
                        var newCol = new ColumnExpression(col.Name, SubTableName, col.Type, col.TypeMapping, col.IsNullable);
                        return new ProjectionExpression(newCol, e.Alias);
                    }
                    return e;
                });


                // 创建新的 SelectExpression，将子查询作为来源
                var newSelect = new SelectExpression(
                    oldSelect.Alias,
                    [subquery],
                    newPredicate,
                    oldSelect.GroupBy,
                    oldSelect.Having,
                    [.. newProjections],
                    oldSelect.IsDistinct,
                    [],
                    null,
                    null,
                    null,
                    null);

                // projectionMapping replace
                var pm = new ProjectionMember();
                var projectionMapping = new Dictionary<ProjectionMember, Expression>
                {
                    {
                        pm,
                        oldSelect.GetProjection(new ProjectionBindingExpression(null,pm,null))
                    }
                };
                newSelect.ReplaceProjection(projectionMapping);

                return newSelect;
            }
        }
    }
}

#pragma warning restore EF1001 // Internal EF Core API usage.

#endif

最后

实现上逻辑还是一致的，都是将Offset转换为ROW_NUMBER()子查询中，取行号数据。只是代码实现区别有一些，以前的EFCore底层代码很多已经不再可用，比如直接使用PushdownIntoSubquery()会报错，GenerateOuterColumn()内部的扩展方法发生了破坏性更新导致不能再使用等。

如果你的程序需要升级到.NET9并且仍在使用早期数据库，可以引用我实现的代码部分，或者直接引用我发布的Nuget包。

<PackageReference Include="Biwen.EFCore.UseRowNumberForPaging" Version="2.1.1" />

代码我放在了GitHub，任何问题欢迎Issue： https://github.com/vipwan/Biwen.EFCore.UseRowNumberForPaging