运行ABP(asp.net core 3.X+Vue)提示'OFFSET' 附近有语法错误。 在 FETCH 语句中选项 NEXT 的用法无效。

创建ASP.NET Boilerplate，还原数据库和启动客户端

这里就略过，具体参考

ABP框架（asp.net core 2.X+Vue）模板项目学习之路（一）

ASP.NET Boilerplate VueJS Template

 

选择

Target Version：v3.x

Target Framework：.NET Core (Cross Platform)

Single Page Web Application：vue.js

Choose your project's name：MyCompany.MyProject

 

用户名：admin  密码：123qwe 点击登录后成功进入系统，点击租户页面，提示错误：对不起,在处理您的请求期间,产生了一个服务器内部错误!

 

 

 

请求信息：

 

 

 

查看日志信息，路径5.0.0\aspnet-core\src\MyCompany.MyProject.Web.Host\App_Data\Logs：

ERROR 2019-12-13 12:47:27,121 [21   ] Mvc.ExceptionHandling.AbpExceptionFilter - 'OFFSET' 附近有语法错误。
在 FETCH 语句中选项 NEXT 的用法无效。
Microsoft.Data.SqlClient.SqlException (0x80131904): 'OFFSET' 附近有语法错误。
在 FETCH 语句中选项 NEXT 的用法无效。
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__164_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Abp.Application.Services.AsyncCrudAppService`8.GetAllAsync(TGetAllInput input)
   at Abp.Threading.InternalAsyncHelper.AwaitTaskWithPostActionAndFinallyAndGetResult[T](Task`1 actualReturnValue, Func`1 postAction, Action`1 finalAction)
   at lambda_method(Closure , Object )
   at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
ClientConnectionId:3cb2fb89-0c31-4bfa-93f7-6b70a7a6ea72
Error Number:102,State:1,Class:15

 

原因是ef core默认生成的分页sql语句带OFFSET和FETCH，2012和以后的数据库支持OFFSET和FETCH，2012以下版本不支持。否则会报错：'OFFSET' 附近有语法错误。 在 FETCH 语句中选项 NEXT 的用法无效。

EXEC sp_executesql N'SELECT [a].[Id], [a].[ConnectionString], [a].[CreationTime], [a].[CreatorUserId], [a].[DeleterUserId], [a].[DeletionTime], [a].[EditionId], [a].[IsActive], [a].[IsDeleted], [a].[LastModificationTime], [a].[LastModifierUserId], [a].[Name], [a].[TenancyName]
FROM [AbpTenants] AS [a]
WHERE (@__ef_filter__p_0 = CAST(1 AS bit)) OR ([a].[IsDeleted] <> CAST(1 AS bit))
ORDER BY [a].[Id] DESC
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',
    N'@__ef_filter__p_0 bit,@__p_0 int,@__p_1 int', @__ef_filter__p_0 = 0,
    @__p_0 = 0, @__p_1 = 10

 

修改5.0.0\aspnet-core\src\MyCompany.MyProject.EntityFrameworkCore\EntityFrameworkCore\MyProjectDbContextConfigurer文件如下即可：

using System.Data.Common;
using Microsoft.EntityFrameworkCore;

namespace MyCompany.MyProject.EntityFrameworkCore
{
    public static class MyProjectDbContextConfigurer
    {
        public static void Configure(DbContextOptionsBuilder<MyProjectDbContext> builder, string connectionString)
        {
            builder.UseSqlServer(connectionString);
        }

        public static void Configure(DbContextOptionsBuilder<MyProjectDbContext> builder, DbConnection connection)
        {
            builder.UseSqlServer(connection);
        }
    }
}

改为

using System.Data.Common;
using Microsoft.EntityFrameworkCore;

namespace MyCompany.MyProject.EntityFrameworkCore
{
    public static class MyProjectDbContextConfigurer
    {
        public static void Configure(DbContextOptionsBuilder<MyProjectDbContext> builder, string connectionString)
        {
            builder.UseSqlServer(connectionString, b => b.UseRowNumberForPaging());
        }

        public static void Configure(DbContextOptionsBuilder<MyProjectDbContext> builder, DbConnection connection)
        {
            builder.UseSqlServer(connection, b => b.UseRowNumberForPaging());
        }
    }
}

 

但修改完，还是提示一样的错误

去EFCore的issues

https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#urn

https://github.com/aspnet/EntityFrameworkCore/issues/16400

EF Core 3.0 从 Preview 6 开始不支持UseRowNumberForPaging，

居然因为sql server 2008不再长期支持，EF Core 3.0要舍弃sql server 2008，真是个大坑，难道要升级个sql server 2012？

尝试降到EF Core 3.0-preview5.19227.1，运行报错。

 

目前解决的方法

1、升级数据库到2012

2、选择EF Core Version：v2.x

3、使用mysql

 我选择了第二个：

加上UseRowNumberForPaging，打开租户页面正常

 

第三个方法：

MyProjectEntityFrameworkCore项目，安装Pomelo.EntityFrameworkCore.MySql，把Migrations文件夹删掉

 

using System.Data.Common;
using Microsoft.EntityFrameworkCore;

namespace MyProject.EntityFrameworkCore
{
    public static class MyProjectDbContextConfigurer
    {
        public static void Configure(DbContextOptionsBuilder<MyProjectDbContext> builder, string connectionString)
        {
            builder.UseMySql(connectionString);
        }

        public static void Configure(DbContextOptionsBuilder<MyProjectDbContext> builder, DbConnection connection)
        {
            builder.UseMySql(connection);
        }
    }
}

 

打开程序包管理控制台，用Add-Migration Init 初始化 ，重新生成镜像文件。

启动项目切换到MyProject.Web.Mvc项目，用Update-Database 生成数据库