search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.68k stars 3.17k forks source link

Contains to OPENJSON translation generates invalid SQL: Sintax error near '$' #32468

Closed emperador-ming closed 9 months ago

emperador-ming commented 10 months ago 
// Execution throws Microsoft.Data.SqlClient.SqlException 
dbContext.Justificantes.Where(j => request.ListaDelegantes.Contains(j.AbogadoOrdenante))
--SQL translation
exec sp_executesql N'SELECT COUNT(*)
FROM [Justificantes] AS [j]
WHERE [j].[abogado_ordenante] IN (
    SELECT [r].[value]
    FROM OPENJSON(@__request_ListaDelegantes_0) WITH ([value] varchar(6) ''$'') AS [r]
)',N'@__request_ListaDelegantes_0 nvarchar(4000)',@__request_ListaDelegantes_0=N'[]'

Msg 102, Level 15, State 1, Line 5
Sintax error near '$'.
An unhandled exception has occurred while executing the request.
Microsoft.Data.SqlClient.SqlException (0x80131904): Sintaxis incorrecta cerca de '$'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at lambda_method106(Closure, QueryContext)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Kendo.Mvc.Extensions.QueryableExtensions.Count(IQueryable source)
   at Kendo.Mvc.Extensions.QueryableExtensions.CreateDataSourceResult[TModel,TResult](IQueryable queryable, DataSourceRequest request, ModelStateDictionary modelState, Func`2 selector)
   at Kendo.Mvc.Extensions.QueryableExtensions.ToDataSourceResult[TModel,TResult](IQueryable`1 enumerable, DataSourceRequest request, Func`2 selector)
   at Justificantes.UI.Web.Controllers.JustificantesController.GetJustificantesGrid(DataSourceRequest request, String textoBusqueda, Int32 estados, Int32 tiposJustificante, String descripcionDetalle, Nullable`1 importeDesdeDetalle, Nullable`1 importeHastaDetalle, String conceptoDetalle, String asuntoDetalle, CancellationToken cancellationToken) in C:\Users\pcv\source\repos\Justificantes\Justificantes.UI.Web\Controllers\JustificantesController.cs:line 130
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, 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.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|7_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Justificantes.UI.Web.Middlewares.NavBarMiddleware.InvokeAsync(HttpContext context, ICurrentAuthenticatedUserService currentAuthenticatedUserService, IMediator mediator) in C:\Users\pcv\source\repos\Justificantes\Justificantes.UI.Web\Middlewares\NavBarMiddleware.cs:line 23
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Justificantes.UI.Web.Authorizations.Handlers.AuthorizationResultTransformer.HandleAsync(RequestDelegate requestDelegate, HttpContext httpContext, AuthorizationPolicy authorizationPolicy, PolicyAuthorizationResult policyAuthorizationResult) in C:\Users\pcv\source\repos\Justificantes\Justificantes.UI.Web\Authorizations\Handlers\AuthorizationResultTransformer.cs:line 11
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)
ClientConnectionId:068704bc-ed1b-4846-a2b5-35d1244e63f5
Error Number:102,State:1,Class:15

Relevant POCO and configuration

public class Justificante 
{
    public Iniciales AbogadoOrdenante { get; set; } = null!;
}
public void Configure(EntityTypeBuilder<Justificante> builder)
{
    builder.Property(e => e.AbogadoOrdenante)
        .HasMaxLength(6)
        .IsUnicode(false)
        .HasColumnName("abogado_ordenante")
        .HasConversion(new InicialesConverter());
}
public class InicialesConverter : ValueConverter<Iniciales, string>
{
    public InicialesConverter() : base(
        inicialesValue => inicialesValue.Value,
        inicialesStr => Iniciales.From(inicialesStr))
    { }
}

Notes

Same code works in EF7

Provider and version information

EF Core version: 8.0 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 8.0 Operating system: Windows 10 IDE: Microsoft Visual Studio Enterprise 2022 (64-bit) Version 17.8.2

ErikEJ commented 10 months ago

See https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#contains-in-linq-queries-may-stop-working-on-older-sql-server-versions

emperador-ming commented 9 months ago

See https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#contains-in-linq-queries-may-stop-working-on-older-sql-server-versions

Thank you! It worked like a charm.