.NET8/EF8 - Exception: Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '$

Entity Framework 8 may generate SQL queries that are not compatible with older SQL Server versions, resulting in a syntax error. This issue occurs when the database compatibility level is lower than the Entity Framework compatibility level. The typical environment where this issue arises is in C#/.NET8/Entity Framework 8/MS SQL Server. The problem manifests as a Microsoft.Data.SqlClient.SqlException with an "Incorrect syntax near '$'" error message. The cause of the issue is the mismatch between the database compatibility level and the Entity Framework compatibility level. To verify the issue, you can check the database compatibility level using a SQL script. There are three ways to resolve the issue: changing the compatibility level on the SQL server, changing the compatibility level in the Entity Framework code statically, or changing the compatibility level in the Entity Framework code dynamically. If the database is SQL Server 2016 or newer, you can modify the compatibility level to a newer value. If the SQL Server is older, you need to change the compatibility level in the Entity Framework code. You can also use the Factory pattern to set the compatibility level dynamically based on the database compatibility level. A higher compatibility level for Entity Framework is claimed to generate more efficient and faster SQL statements. To change the compatibility level on the SQL server, you can use the ALTER DATABASE statement. To change the compatibility level in the Entity Framework code, you can use the DbContextOptionsBuilder and set the compatibility level using the UseCompatibilityLevel method.