Search a table using a list of strings in EF Core 8 preview (where in clause)

by

** UPDATE **
So I took the suggestion below and set the compatibility level to 150 (SQL Server 2019) as it was set at 100 and reran the code and it resulted with the same issue. Next step is to set it correctly in the Server.

Program.cs

builder.Services.AddDbContext<RIEDBContext>(options =>
                 options.UseSqlServer(builder.Configuration.GetConnectionString("RIEDB"),
                 o => o.UseCompatibilityLevel(150)));

Log verifies the compatibility level

2023-09-29 09:10:07.638 -04:00 [DBG] Entity Framework Core 8.0.0-rc.1.23419.6 initialized 'RIEDBContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer:8.0.0-rc.1.23419.6' with options: CompatibilityLevel=150

Same Stack Trace in the log

2023-09-29 09:11:23.390 -04:00 [DBG] Executing DbCommand [Parameters=[@__ReasonCodesList_0='?' (Size = 4000)], CommandType=""Text"", CommandTimeout="30"]
SELECT [r].[ReasonCodeID], [r].[ApplicationStatusCode], [r].[BenefitTypeCode], [r].[IsActive], [r].[ReasonCodes], [r].[ReasonLongDescription], [r].[ReasonShortDescription]
FROM [ReasonCode] AS [r]
WHERE [r].[ReasonCodes] IN (
    SELECT [r0].[value]
    FROM OPENJSON(@__ReasonCodesList_0) WITH ([value] varchar(6) '$') AS [r0]
)
2023-09-29 09:11:23.438 -04:00 [ERR] Failed executing DbCommand (49ms) [Parameters=[@__ReasonCodesList_0='?' (Size = 4000)], CommandType=""Text"", CommandTimeout="30"]
SELECT [r].[ReasonCodeID], [r].[ApplicationStatusCode], [r].[BenefitTypeCode], [r].[IsActive], [r].[ReasonCodes], [r].[ReasonLongDescription], [r].[ReasonShortDescription]
FROM [ReasonCode] AS [r]
WHERE [r].[ReasonCodes] IN (
    SELECT [r0].[value]
    FROM OPENJSON(@__ReasonCodesList_0) WITH ([value] varchar(6) '$') AS [r0]
)
2023-09-29 09:11:23.451 -04:00 [ERR] An exception occurred while iterating over the results of a query for context type 'RIE.Models.DBModels.RIEDBContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '$'.

** ORIGINAL POST **

I’m upgrading an existing EF 6 project to EF Core using the EF 8 preview 8.0.0-rc.1.23419.6 release.

I am getting an Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ‘$’. for something that seems pretty basic.

I pass a basic list of strings via WebAPI, the strings are passed correctly and the List parameter looks correct in the method call in debug.

Really not sure what the issue is here.

Entity (Navigation properties removed)

public partial class ReasonCode
{
    public int ReasonCodeID { get; set; }
    public string ApplicationStatusCode { get; set; } = null!;
    public string ReasonCodes { get; set; } = null!;
    public string? ReasonShortDescription { get; set; }
    public string? ReasonLongDescription { get; set; }
    public bool IsActive { get; set; }
    public string BenefitTypeCode { get; set; } = null!;
}

Calling Method

public async Task<IEnumerable<ReasonCode>> GetReasonsByCodesAsync(List<string> ReasonCodesList)
{
    var dbResult = await _context.ReasonCodes.Where(e => ReasonCodesList.Contains(e.ReasonCodes)).ToListAsync();
    return dbResult;
}

Controller Method

[HttpPost]
[Route("GetReasonsByCodes")]
[ProducesResponseType(StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status400BadRequest)]
public async Task<IActionResult> GetReasonsByCodes([FromBody] List<string> ReasonCodesList)
{
    var searchResult = await _reasonCodeRepository.GetReasonsByCodesAsync(ReasonCodesList);

    if ((searchResult == null) || (!searchResult.Any())) return NotFound();
    return Ok(searchResult);
}

Entity creation from OnModelCreating

modelBuilder.Entity<ReasonCode>(entity =>
{
    entity.ToTable("ReasonCode");

    entity.HasIndex(e => e.ReasonCodes, "UQ__ReasonCo__8CCF3D98662BF692").IsUnique();

    entity.Property(e => e.ApplicationStatusCode)
        .HasMaxLength(2)
        .IsUnicode(false);
    entity.Property(e => e.BenefitTypeCode)
        .HasMaxLength(2)
        .IsUnicode(false);
    entity.Property(e => e.ReasonCodes)
        .HasMaxLength(6)
        .IsUnicode(false);
    entity.Property(e => e.ReasonLongDescription)
        .HasMaxLength(600)
        .IsUnicode(false);
    entity.Property(e => e.ReasonShortDescription)
        .HasMaxLength(100)
        .IsUnicode(false);

    entity.HasOne(d => d.ApplicationStatusCodeNavigation).WithMany(p => p.ReasonCodes)
        .HasPrincipalKey(p => p.ApplicationStatusCode)
        .HasForeignKey(d => d.ApplicationStatusCode)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK__ReasonCod__Appli__5D61A667");
});

This is the error I get in the Log File. It looks like it wanted to do the right thing, but seems that it didn’t iterate the List for some reason.

2023-09-28 14:00:52.078 -04:00 [ERR] Failed executing DbCommand (42ms) [Parameters=[@__ReasonCodesList_0='?' (Size = 4000)], CommandType=""Text"", CommandTimeout="30"]
SELECT [r].[ReasonCodeID], [r].[ApplicationStatusCode], [r].[BenefitTypeCode], [r].[IsActive], [r].[ReasonCodes], [r].[ReasonLongDescription], [r].[ReasonShortDescription]
FROM [ReasonCode] AS [r]
WHERE [r].[ReasonCodes] IN (
    SELECT [r0].[value]
    FROM OPENJSON(@__ReasonCodesList_0) WITH ([value] varchar(6) '$') AS [r0]
)

Full stack trace

2023-09-28 14:00:52.091 -04:00 [ERR] An exception occurred while iterating over the results of a query for context type 'RIE.Models.DBModels.RIEDBContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '$'.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
ClientConnectionId:73e5670f-2c5b-4e05-8b86-3ac42e5d188c
Error Number:102,State:1,Class:15
Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '$'.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
ClientConnectionId:73e5670f-2c5b-4e05-8b86-3ac42e5d188c
Error Number:102,State:1,Class:15
2023-09-28 14:00:52.163 -04:00 [INF] Executed action RIE.Services.Controllers.LookupController.GetReasonsByCodes (RIE.Services) in 6474.8297ms
2023-09-28 14:00:52.164 -04:00 [INF] Executed endpoint 'RIE.Services.Controllers.LookupController.GetReasonsByCodes (RIE.Services)'
2023-09-28 14:00:52.165 -04:00 [ERR] An unhandled exception has occurred while executing the request.
Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '$'.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`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 RIE.DataImpl.Repositories.ReasonCodeRepository.GetReasonsByCodesAsync(List`1 ReasonCodesList) in C:\Users\segantim\source\repos\RIE\RIE.DataImpl\Repositories\ReasonCodeRepository.cs:line 30
   at RIE.Services.Controllers.LookupController.GetReasonsByCodes(List`1 ReasonCodesList) in C:\Users\segantim\source\repos\RIE\RIE.Services\Controllers\LookupController.cs:line 139
   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__Logged|12_1(ControllerActionInvoker invoker)
   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.<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 Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

  • Looks like ReasonCodes configured to be mapped to JSON, do you have entity type configuration you are not showing?

    – 

  • The entity was reverse engineered from the database. Not sure why it thinks its JSON as I haven’t had issues anywhere else. Not sure where I should look as I’m not an EF guru by any means. I’ll edit this to show the controller call…

    – 




  • Check out the OnModelCreating method in your db context.

    – 

  • 1

    SqlServer 15.0.4153.1 (Server 2019)

    – 

  • 1

    Github issue posted at github.com/dotnet/efcore/issues/31898

    – 

As noted in the comments to your GitHub post, this is an issue with the version and compatibility level of your database. OPENJSON using WITH only works on level 130 or higher.

So either upgrade your database (recommended), or tell EF Core that you are using a lower compatibility level:

optionsBuilder.UseSqlServer(connectionString, o => o.UseCompatibilityLevel(110));

There is a much better option anyway for doing bulk queries: use a Table Valued Parameter as I show here.

Leave a Comment