Quick Fix: Set your database compatibility level to 130 or above to resolve the syntax issue. Keep in mind that doing so might impact performance. Consider testing the change’s impact on your workload before proceeding.
The Problem:
When upgrading an ASP.NET Core project using EF Core from .NET 7 to .NET 8, a SqlException with the error message "Incorrect syntax near ‘\(‘". is encountered. This issue arises when using `OPENJSON` with a parameter in a SQL query, particularly when targeting a database with a compatibility level of 160. The `OPENJSON` syntax with the `\)` sign is not recognized in this context, leading to the error. The goal is to resolve this issue and enable the project to run successfully with .NET 8 and the updated database.
The Solutions:
Solution 1: Check SQL Server Compatibility Level
The error is caused by a change in the SQL generated by EF Core 8. The new SQL requires SQL Server 2016+ or a compatibility level of 130+. You can mitigate the issue by:
Updating the database compatibility level to 130+. This can be done in SQL Server Management Studio by right-clicking on the database and selecting “Properties”. Then, change the “Compatibility level” to 130 or higher.
Configuring EF Core to revert to the older, less efficient SQL. This can be done by setting the compatibility level to 120 in the `OnConfiguring` method of your DbContext.
Before making any changes, it’s important to test the performance implications of the change with your workload. Some people have reported significant performance degradation with the new approach, so it’s important to make sure that it doesn’t negatively impact your application.
Solution 2: Update Database Compatibility Level
The error “SqlException: Incorrect syntax near ‘$'” is caused by a compatibility issue between your database and the version of SQL Server you are using. To resolve this, you need to update the compatibility level of your database to 160, which is the minimum required for .NET 8.
To update the compatibility level of your database:
Open SQL Server Management Studio (SSMS) and connect to the database server.
In the Object Explorer pane, right-click on the database you want to update and select "Properties".
In the Database Properties dialog box, select the "Options" page.
Under "Compatibility Level", select "160" from the drop-down list.
Click "OK" to save the changes.
Once you have updated the compatibility level of your database, the error should go away and you should be able to use the new features of .NET 8.
Q&A
After upgrading to dotnet 8 and updating the database, why am I still getting "incorrect syntax near "$"?"
—
The SQL generated has changed, requiring SQL Server 2016+.
What mitigation options are there?
—
Change compatibility level or revert to older, less efficient SQL.
Why am I getting "incorrect syntax" after updating my database?
—
Database compatibility is 120 instead of 160; set it to 160.
Video Explanation:
The following video, titled "SQL Incorrect Syntax near ')'/ SQL Server Incorrect Syntax near ...", provides additional insights and in-depth exploration related to the topics discussed in this post.
SQL Incorrect Syntax near ')', SQL Server Incorrect Syntax near ')' To Subscribe the channel, click on below link ...
