SqlException: Incorrect syntax near '$'. after upgrading to Dotnet 8 - Database Compatibility Version - Not EFCore UseCompatibilityLevel

Question

I have an ASP.NET Core project which uses code-first in EF Core.
After upgrading from .NET 7 to .NET 8 and also EF Core packages I'm faced with an error

SqlException: Incorrect syntax near '$'.

This is query from profiler

exec sp_executesql N'SELECT [t].[id], [t].[idDatetime], [t].[IdDistPath], [t].[idLoadType], [t].[idLoader], [t].[idLoaderDriver], [t].[idShift], [t].[idTruck], [t].[idTruckDriver], [t].[level], [t].[pattern], [t].[Tonnage]
FROM [tbl_Loading] AS [t]
WHERE [t].[idShift] IN (
    SELECT [s].[value]
    FROM OPENJSON(@__shiftsAllIds_0) WITH ([value] int ''$'') AS [s]
)',N'@__shiftsAllIds_0 nvarchar(4000)',@__shiftsAllIds_0=N'[338769,338768]'

This is my OnConfiguring method:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    base.OnConfiguring(optionsBuilder);
    optionsBuilder.UseSqlServer(_configuration.GetConnectionString("DefaultConnection")
, o => o.UseCompatibilityLevel(160));
}

I've tried to add another migration with .NET 8 and updated the database, but unfortunately it didn't work.

I've searched a lot in Stackoverflow community but none of threads are related to my issue.

Environment:

• .NET 8
• SQL Server 2022 Version 16.0.1000.6
• Windows 11

Answer 1

The SQL generated has changed as described here. OPENJSON requires SQL Server 2016+/compat level 130+.

See the mitigation options in the above article

If the compatibility level is below 130 (SQL Server 2016), consider modifying it to a newer value (documentation).

Otherwise, if your database version really is older than SQL Server 2016, or is set to an old compatibility level which you cannot change for some reason, configure EF Core to revert to the older, less efficient SQL as follows:

c#

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseSqlServer(@"<CONNECTION STRING>", o => o.UseCompatibilityLevel(120));

Before just upgrading the database compatibility level and getting on with your day you should be aware that some people are reporting significant performance degradation with this new approach and not finding the old approach was "less efficient" than the new one. Due to potentially getting worse execution plans. You should test the performance implications of this change with your workload.