快捷導(dǎo)航

.NET9?EFcore支持早期MSSQL數(shù)據(jù)庫?ROW_NUMBER()分頁功能

更新時間：2024年11月27日 09:58:03 作者：萬雅虎

文章介紹了如何在.NET?9中使用EF?Core實現(xiàn)對早期MSSQL數(shù)據(jù)庫的ROW_NUMBER()分頁兼容,由于EF?Core?9對底層API進行了重大更新,原有的兼容代碼需要重新實現(xiàn),具體實現(xiàn)代碼跟隨小編一起看看吧

前言

NET程序員是很幸福的,MS在上個月發(fā)布了NET9.0RTM,帶來了不少的新特性,但是呢,還有很多同學軟硬件都還沒跟上時代的步伐,比如,自己的電腦還在跑Win7,公司服務(wù)器還在跑MSSQL2005-2008的!

這不就引入了我們本文要探索的問題,因為MS早在EFcore3.1后就不再內(nèi)置支持ROW_NUMBER()了,以至于需要兼容分頁的代碼都需要自行處理,當然同學們?nèi)绻麑FCore沒有依賴度也可以使用其他的ORM選型,當然如果不想折騰EFCore也能使用萬能的RawSql拼接執(zhí)行也是可以的 ??

最近自己發(fā)的Nuget包有個國外的程序員朋友提了一個Issue,以至于我馬上行動起來

在EFCore9中, 以前兼容的好好的ROW_NUMBER()代碼,升級嘗鮮后發(fā)現(xiàn)跑不起來了,這主要是因為新版本的EFCore9做了很多破壞性更新,以至于我們不得不研究新的底層代碼!

兼容實現(xiàn)

之前發(fā)布過一個Nuget包,代碼主要是基于以前道友兼容EFCore7適配到EFCore8的兼容,代碼也不多變化也不大,不過呢,升級到EFCore9后發(fā)現(xiàn)底層的API全變了,不得不重新再實現(xiàn)一遍!

以下是兼容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_";//下標避免數(shù)據(jù)表存在字段
            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);
                // 創(chuàng)建子查詢
                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,
                    [],//排序已經(jīng)在rowNumber中了
                    null,
                    null,
                    null,
                    null);
                //構(gòu)造新的條件:
                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;
                });
                // 創(chuàng)建新的 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

最后

實現(xiàn)上邏輯還是一致的,反正都是將Offset轉(zhuǎn)換為ROW_NUMBER()子查詢中,取行號數(shù)據(jù)

只是代碼實現(xiàn)區(qū)別有一些,以前的EFCore底層代碼很多已經(jīng)不在可用比如直接使用PushdownIntoSubquery()會報錯,GenerateOuterColumn()內(nèi)部的擴展方法發(fā)生了破壞性更新導(dǎo)致不能再使用等!

如果你的程序需要升級到NET9并還在使用早期數(shù)據(jù)庫的話,可以引用我實現(xiàn)的代碼部分,或者直接引用我發(fā)布的Nuget包

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

代碼我放在了GitHub,任何問題歡迎Issue https://github.com/vipwan/Biwen.EFCore.UseRowNumberForPaging

到此這篇關(guān)于.NET9 EFcore支持早期MSSQL數(shù)據(jù)庫 ROW_NUMBER()分頁的文章就介紹到這了,更多相關(guān).NET9 EFcore MSSQL數(shù)據(jù)庫 ROW_NUMBER()分頁內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: