Question 288252 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – SqlException: Incorrect syntax near '$'. After Upgrading EFCore 7 To 8

I have an ASP project which uses Code First EF Core.
After Upgrading Dotnet7 to Dotnet8 and also EFCore Packages I’ve faced with an error

SqlException: Incorrect syntax near '$'.

enter image description here

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

    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 Dotnet8 and Updated the database, but unfortunately it didn’t worked.
I’ve searched a lot in Stackoverflow community but none of threads are related to my issue.

Dotnet 8
SQL Server 2022 Version 16.0.1000.6
Windows 11

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As Martin Smith comment I've executed this query on master database

    SELECT name, compatibility_level FROM sys.databases

    And saw that my database compatibility is 120 instead of 160

    enter image description here

    Seems I should update my database compatibility first
    Microsoft Documentation


  2. 0

    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));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search