首页 新闻 搜索 专区 学院

asp.net core3.1使用EF Core出现:'OFFSET' 附近有语法错误。 在 FETCH 语句中选项 NEXT 的用法无效。

1
悬赏园豆:10 [已解决问题] 解决于 2020-07-08 17:35

在不升级sql server的情况下,如何解决这个问题?使用UseRowNumberForPaging()也无济于事,求大佬帮忙看一下,十分感谢!!!

从前的倒影的主页 从前的倒影 | 初学一级 | 园豆:71
提问于:2020-07-08 17:00
< >
分享
最佳答案
1

不升级SQL就降级EF呗,EF 3.1 minimum require SQL 2012, EF 2.2 可以SQL 2008的。
人不能被那啥给憋死。

收获园豆:10
爱编程的大叔 | 高人七级 |园豆:30753 | 2020-07-08 17:10

非常感谢!降级到2.2在加上UseRowNumberForPaging()就可以了

从前的倒影 | 园豆:71 (初学一级) | 2020-07-08 17:34

初学者,不知道版本的一些问题,不知道2.2会不会多出什么bug之类的

从前的倒影 | 园豆:71 (初学一级) | 2020-07-08 17:37
其他回答(2)
0

如果实在不行,就直接写SQL吧

会长 | 园豆:9566 (大侠五级) | 2020-07-08 17:07
1

先添加下面的类, 然后 调用
.DbContextOptions.ReplaceService<IQueryTranslationPostprocessorFactory, SqlServer2008QueryTranslationPostprocessorFactory>();

//代码
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;
            }
        }
    }
}
Angel〆汐 | 园豆:211 (菜鸟二级) | 2020-07-09 12:48

哥,你太猛了,厉害啊

支持(0) 反对(0) 从前的倒影 | 园豆:71 (初学一级) | 2020-08-21 12:15

把项目升级到了最新的.net5.0,这种方法报错了,应该如果处理呢?

支持(0) 反对(0) 廉毅 | 园豆:202 (菜鸟二级) | 2020-11-15 15:51

@廉毅: 发了一个包, 安装后把自己写的类删掉就行了
Install-Package EfCore.SqlServer2008Query

支持(0) 反对(0) Angel〆汐 | 园豆:211 (菜鸟二级) | 2020-11-16 15:33

@Angel〆汐: 好的,我回去试试啊,多谢多谢!

支持(0) 反对(0) 廉毅 | 园豆:202 (菜鸟二级) | 2020-11-16 15:47

@Angel〆汐: 我试了一下,编译时不报错了,但运行以后还是会报错,因为我是初学,不太会看,应该还是和,net5内部的处理有冲突,
刚运行到登录查用户时就报错了,
user = _dbContext.DncUser.FirstOrDefault(x => x.LoginName == username);

"message": "资源服务器忙,请稍候再试,原因:Expression of type 'Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1[DncZeus.Api.Entities.DncUser]' cannot be used for return type 'DncZeus.Api.Entities.DncUser'"

===========================================================================

严重性 代码 说明 项目 文件 行 禁止显示状态
警告 EF1001 Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServer2008QueryTranslationPostprocessorFactory is an internal API that supports the Entity Framework Core infrastructure and not subject to the same compatibility standards as public APIs. It may be changed or removed without notice in any release. DncZeus.Api F:\共享代码\DncZeus-master\DncZeus.Api\Startup.cs 92 活动

支持(0) 反对(0) 廉毅 | 园豆:202 (菜鸟二级) | 2020-11-20 15:23

@廉毅: 包已更新, 重新获取下1.0.3版本

支持(0) 反对(0) Angel〆汐 | 园豆:211 (菜鸟二级) | 2020-11-20 16:04

@Angel〆汐: 更新后目前没报错,但输出窗口有下面提示,不知道有什么以后不会不会影响,继续试用,非常感谢!

严重性 代码 说明 项目 文件 行 禁止显示状态
警告 EF1001 Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServer2008QueryTranslationPostprocessorFactory is an internal API that supports the Entity Framework Core infrastructure and not subject to the same compatibility standards as public APIs. It may be changed or removed without notice in any release. DncZeus.Api F:\共享代码\DncZeus-master\DncZeus.Api\Startup.cs 92 活动

支持(0) 反对(0) 廉毅 | 园豆:202 (菜鸟二级) | 2020-11-21 09:59

@廉毅: efcore5 加的警告, 不影响使用, 因为替换了内部api, 每次更新版本这些api可能会随时被删除或修改, 所以谨慎更新

支持(0) 反对(0) Angel〆汐 | 园豆:211 (菜鸟二级) | 2020-11-21 10:10

@廉毅: 能开源不?如果以后有啥问题可以一起弄

支持(0) 反对(0) X-Flower | 园豆:200 (初学一级) | 2020-12-18 16:47

@Angel〆汐: 大佬 引用 包以后,一直在报错 为啥呢 'OFFSET' 附近有语法错误。 在 FETCH 语句中选项 NEXT 的用法无效。

支持(0) 反对(0) Rommel88 | 园豆:202 (菜鸟二级) | 2021-02-08 11:26

@Aaron_wusir:
有在
xxx.UseSqlServer();后面调用
xxx.DbContextOptions.ReplaceService<IQueryTranslationPostprocessorFactory, SqlServer2008QueryTranslationPostprocessorFactory>();

支持(0) 反对(0) Angel〆汐 | 园豆:211 (菜鸟二级) | 2021-02-09 16:07
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册