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

时间:2022-10-19 01:56:22

创建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

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

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

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

请求信息:

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

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

ERROR -- ::, [   ] 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` result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`.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`.AsyncEnumerator.MoveNextAsync()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable` source, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable` source, CancellationToken cancellationToken)
at Abp.Application.Services.AsyncCrudAppService`.GetAllAsync(TGetAllInput input)
at Abp.Threading.InternalAsyncHelper.AwaitTaskWithPostActionAndFinallyAndGetResult[T](Task` actualReturnValue, Func` postAction, Action` 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` 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:,State:,Class:

原因是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 =

修改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、选择Target Version:v2.x

我选择了后者:

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

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