.NET9 EFCore支持早期MSSQL数据库 ROW_NUMBER()分页
前言NET程序员是很幸福的,MS在上个月发布了NET9.0RTM,带来了不少的新特性,但是呢,还有很多同学软硬件都还没跟上时代的步伐,比如,自己的电脑还在跑Win7,公司服务器还在跑MSSQL2005-2008的!
这不就引入了我们本文要探索的问题,因为MS早在EFcore3.1后就不再内置支持ROW_NUMBER()了,以至于需要兼容分页的代码都需要自行处理,当然同学们如果对EFCore没有依赖度也可以使用其他的ORM选型,当然如果不想折腾EFCore也能使用万能的FromSqlRaw()拼接执行也是可以的 😃
最近自己发的Nuget包有个国外的程序员朋友提了一个Issue,以至于我马上行动起来
在EFCore9中, 以前兼容的好好的ROW_NUMBER()代码,升级尝鲜后发现跑不起来了,这主要是因为新版本的EFCore9做了很多破坏性更新,以至于我们不得不研究新的底层代码!
兼容实现
之前发布过一个Nuget包,代码主要是基于以前道友兼容EFCore7适配到EFCore8的兼容,代码也不多变化也不大,不过呢,升级到EFCore9后发现底层的API全变了,不得不重新再实现一遍!
以下是兼容EFCore9的代码部分:
#if NET9_0#pragma warning disable EF1001 // Internal EF Core API usage.namespace Biwen.EFCore.UseRowNumberForPaging;using Microsoft.EntityFrameworkCore.Query;using System.Collections.Generic;using System.Reflection;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); internal 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(RelationalDependencies.SqlExpressionFactory).Visit(query); return query; } /// <summary> /// 将 Offset 转换为 RowNumber /// </summary> /// <param name="sqlExpressionFactory"></param> internal class Offset2RowNumberConvertVisitor(ISqlExpressionFactory sqlExpressionFactory) : ExpressionVisitor { private readonly ISqlExpressionFactory sqlExpressionFactory = sqlExpressionFactory; private const string SubTableName = "t"; private const string RowColumnName = "_Row_";//下标避免数据表存在字段 private static readonly FieldInfo _clientProjections = typeof(SelectExpression).GetField("_clientProjections", BindingFlags.NonPublic | BindingFlags.Instance); private static readonly FieldInfo _projectionMapping = typeof(SelectExpression).GetField("_projectionMapping", BindingFlags.NonPublic | BindingFlags.Instance); 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 rowOrderings = oldOrderings.Any() switch { true => oldOrderings, false => }; var oldSelect = selectExpression; var rowNumberExpression = new RowNumberExpression([], rowOrderings, oldOffset.TypeMapping); // 创建子查询 IList<ProjectionExpression> projection = ; var subQuery = new SelectExpression( SubTableName, oldSelect.Tables, oldSelect.Predicate, oldSelect.GroupBy, oldSelect.Having, [.. oldSelect.Projection, .. projection], oldSelect.IsDistinct, [],//排序已经在rowNumber中了 null, null, null, null); //构造新的条件: //存在 Limit 时，条件为 Row > Offset AND Row <= Offset + Limit //不存在 Limit 时，条件为 Row > Offset var newPredicate = oldLimit is not null ? sqlExpressionFactory.AndAlso( sqlExpressionFactory.GreaterThan( new ColumnExpression(RowColumnName, SubTableName, typeof(int), null, true), oldOffset), sqlExpressionFactory.LessThanOrEqual( new ColumnExpression(RowColumnName, SubTableName, typeof(int), null, true), sqlExpressionFactory.Add(oldOffset, oldLimit))) : sqlExpressionFactory.GreaterThan( new ColumnExpression(RowColumnName, SubTableName, typeof(int), null, true), oldOffset); //新的Projection: var newProjection = oldSelect.Projection.Select(exp => { if (exp is { Expression: ColumnExpression col }) { // 替换为子查询的别名 var newCol = new ColumnExpression(exp.Alias, SubTableName, col.Type, col.TypeMapping, col.IsNullable); return new ProjectionExpression(newCol, exp.Alias); } return exp; }); // 创建新的 SelectExpression，将子查询作为来源 var newSelect = new SelectExpression( oldSelect.Alias, ,//子查询 newPredicate,//新的条件 oldSelect.GroupBy, oldSelect.Having, [.. newProjection],//新的Projection oldSelect.IsDistinct, [], null, null, null, null); // replace ProjectionMapping & ClientProjections _projectionMapping.SetValue(newSelect, _projectionMapping.GetValue(oldSelect)); _clientProjections.SetValue(newSelect, _clientProjections.GetValue(oldSelect)); return newSelect; } } }}#pragma warning restore EF1001 // Internal EF Core API usage.#endif最终生成的SQL会形如以下代码:
DECLARE @__p_0 int = 0;DECLARE @__p_1 int = 10;SELECT ., .FROM ( SELECT ., ., ROW_NUMBER() OVER(ORDER BY .) AS FROM AS ) AS WHERE . > @__p_0 AND . <= @__p_0 + @__p_1最后
实现上逻辑还是一致的,反正都是将Offset转换为ROW_NUMBER()子查询中,取行号范围数据
只是代码实现区别有一些,以前的EFCore底层代码很多已经不再可用或发生了质变,比如直接使用PushdownIntoSubquery()会报错,GenerateOuterColumn()内部的扩展方法发生了破坏性更新导致参数不一致等!
如果你的程序需要升级到NET9并还在使用早期MSSQL数据库的话,可以引用我实现的代码部分,或者直接引用我发布的Nuget包
<PackageReference Include="Biwen.EFCore.UseRowNumberForPaging" Version="2.3.1" />代码我放在了GitHub,任何问题欢迎Issue https://github.com/vipwan/Biwen.EFCore.UseRowNumberForPaging
本文版权归作者所有,转载请注明出处!
页:
[1]