ef core 3.1遇到 sqlserver2008 'OFFSET' 附近有语法错误。\r\n在 FETCH 语句中选项 NEXT 的用法无效。

ef core 3.1遇到 sqlserver2008 'OFFSET' 附近有语法错误。\r\n在 FETCH 语句中选项 NEXT 的用法无效。

UseRowNumberForPaging() 已过时且无作用

 

加个类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Internal;

namespace Aceo.Api.EntityFrameworkCore
{
    internal class SqlServer2008QueryTranslationPostprocessorFactory : IQueryTranslationPostprocessorFactory
    {
        private readonly QueryTranslationPostprocessorDependencies _dependencies;
        private readonly RelationalQueryTranslationPostprocessorDependencies _relationalDependencies;

        public SqlServer2008QueryTranslationPostprocessorFactory(QueryTranslationPostprocessorDependencies dependencies,
            RelationalQueryTranslationPostprocessorDependencies relationalDependencies)
        {
            _dependencies = dependencies;
            _relationalDependencies = relationalDependencies;
        }

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

        public class SqlServer2008QueryTranslationPostprocessor : SqlServerQueryTranslationPostprocessor
        {
            public SqlServer2008QueryTranslationPostprocessor(QueryTranslationPostprocessorDependencies dependencies, RelationalQueryTranslationPostprocessorDependencies relationalDependencies, QueryCompilationContext queryCompilationContext)
                : base(dependencies, relationalDependencies, queryCompilationContext)
            {
            }

            public override Expression Process(Expression query)
            {
                query = base.Process(query);
                query = new Offset2RowNumberConvertVisitor(query, SqlExpressionFactory).Visit(query);
                return query;
            }

            private class Offset2RowNumberConvertVisitor : ExpressionVisitor
            {
                private static readonly Func<SelectExpression, SqlExpression, string, ColumnExpression> GenerateOuterColumnAccessor;

                static Offset2RowNumberConvertVisitor()
                {
                    var method = typeof(SelectExpression).GetMethod("GenerateOuterColumn", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, null, new Type[] { typeof(SqlExpression), typeof(string) }, null);
                    if (method?.ReturnType != typeof(ColumnExpression))
                        throw new InvalidOperationException("SelectExpression.GenerateOuterColumn() is not found");
                    GenerateOuterColumnAccessor = (Func<SelectExpression, SqlExpression, string, ColumnExpression>)method.CreateDelegate(typeof(Func<SelectExpression, SqlExpression, string, ColumnExpression>));
                }

                private readonly Expression root;
                private readonly ISqlExpressionFactory sqlExpressionFactory;

                public Offset2RowNumberConvertVisitor(Expression root, ISqlExpressionFactory sqlExpressionFactory)
                {
                    this.root = root;
                    this.sqlExpressionFactory = sqlExpressionFactory;
                }

                protected override Expression VisitExtension(Expression node)
                {
                    if (node is SelectExpression se)
                        node = VisitSelect(se);
                    return base.VisitExtension(node);
                }

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

                    var oldLimit = selectExpression.Limit;
                    var oldOrderings = selectExpression.Orderings;
                    //order by in subQuery without TOP N is invalid.
                    var newOrderings = oldOrderings.Count > 0 && (oldLimit != null || selectExpression == root)
                        ? oldOrderings.ToList()
                        : new List<OrderingExpression>();
                    selectExpression = selectExpression.Update(selectExpression.Projection.ToList(),
                                                               selectExpression.Tables.ToList(),
                                                               selectExpression.Predicate,
                                                               selectExpression.GroupBy.ToList(),
                                                               selectExpression.Having,
                                                               orderings: newOrderings,
                                                               limit: null,
                                                               offset: null,
                                                               selectExpression.IsDistinct,
                                                               selectExpression.Alias);
                    var rowOrderings = oldOrderings.Count != 0 ? oldOrderings
                        : new[] { new OrderingExpression(new SqlFragmentExpression("(SELECT 1)"), true) };
                    _ = selectExpression.PushdownIntoSubquery();
                    var subQuery = (SelectExpression)selectExpression.Tables[0];
                    var projection = new RowNumberExpression(Array.Empty<SqlExpression>(), rowOrderings, oldOffset.TypeMapping);
                    var left = GenerateOuterColumnAccessor(subQuery, projection, "row");
                    selectExpression.ApplyPredicate(sqlExpressionFactory.GreaterThan(left, oldOffset));
                    if (oldLimit != null)
                    {
                        if (oldOrderings.Count == 0)
                        {
                            selectExpression.ApplyPredicate(sqlExpressionFactory.LessThanOrEqual(left, sqlExpressionFactory.Add(oldOffset, oldLimit)));
                        }
                        else
                        {
                            //the above one not working when used as subQuery with orderBy
                            selectExpression.ApplyLimit(oldLimit);
                        }
                    }
                    return selectExpression;
                }
            }
        }
    }
}

 

usesqlserver之前加个代码

builder.ReplaceService<IQueryTranslationPostprocessorFactory, SqlServer2008QueryTranslationPostprocessorFactory>();
builder.UseSqlServer(connectionString);

可以了

 

.net 5.0 +

.net5 就不支持sqlserver2008了，不过可以通过nugit引用这个包来支持EntityFrameworkCore.UseRowNumberForPaging

地址：
https://www.nuget.org/packages/EntityFrameworkCore.UseRowNumberForPaging/