** UPDATE 2 ** The issue ended up being the database compatibility level. Setting the level on the context did nothing as it needed be set on the database itself for it to work. There is a breaking changes note with EF 8 Breaking Changes in EF 8
In my case I'm on 2019 SQL Server, but the compatibility level was set to 100 which behaves like SQL Server 2008. It must have taken that from a restored backup.
To quote the note:
Old behavior Previously, when the Contains operator was used in LINQ queries with a parameterized value list, EF generated SQL that was inefficient but worked on all SQL Server versions.
New behavior Starting with EF Core 8.0, EF now generates SQL that is more efficient, but is unsupported on SQL Server 2014 and below.
Note that newer SQL Server versions may be configured with an older compatibility level, also making them incompatible with the new SQL. This can also occur with an Azure SQL database which was migrated from a previous on-premises SQL Server instance, carrying over the old compatibility level.
** 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)
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.