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

1 Problem description

1.1 The environment

The typical environment to which this article applies is C#/.NET8/Entity Framework 8/MS SQL Server.

1.2 Problem manifestation

You get an exception: Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '$'.

1.3 Cause of problem

Typically, you have a Database Compatibility level of 120, and the Entity Framework Compatibility level is 160. Shortly speaking, your EF is generating SQL code in a more advanced version of SQL language than your database can understand. Or similar values for levels, but the point is the mismatch of Compatibility Levels.

You might ask, how different is the new version SQL code produced by EF8? Well, shortly it looks like it is trying to send JSON lists to SQL server, which newer SQL Servers will understand, but the old one will not, so it will consider it as a Syntax error.

1.4 Verifying the problem

To verify that is really the issue you are facing, you need to check your Database Compatibility level. The script for that is below.

1.5 Resolution

I see three ways to resolve the problem:

1. change the compatibility level on your SQL server
2. change the compatibility level in your EF code statically
3. change the compatibility level in your EF code dynamically, adapting it to the database compatibility level which is obtained during first contact with the database, typically during the login process. 

1.5.1 change the compatibility level on your SQL server

• If your database is SQL Server 2016 (13.x) or newer, or if you're using Azure SQL, check the configured compatibility level. If the compatibility level is below 130 (SQL Server 2016), consider modifying it to a newer value
• If your SQL Server is older, you have no other option than to change the compatibility level in EF

1.5.2 change the compatibility level in your EF code statically

Below I am providing a sample code to change the compatibility level in your EF, if you are using the Factory pattern, or otherwise. You can statically assign new compatibility level in your Factory

1.5.3 change the compatibility level in your EF code dynamically

If you are using Factory Pattern for EF8, you can in the first call to the database enquire about database compatibility level and set your Factory to generate each time the EFContext of appropriate compatibility level. I am showing below how to get a database compatibility level during the Database Connection test.

A strategy could be, for example, to start with a lower compatibility level [2] in your EF, for example 80, then based on the discovered real data state of the database, raise the compatibility level for the following connections. Factory Pattern supports such a scenario.

1.6 Side effects

A higher compatibility level for EF is claimed to generate more efficient/faster SQL statements/queries.

2 Code samples

SQL scripts you might need.

--finding compatibility level for the database

SELECT compatibility_level  FROM sys.databases  WHERE name = 'mydbname';

--changing compatibility level for the database

ALTER DATABASE AdventureWorks2022
SET COMPATIBILITY_LEVEL = 150;

Sample C#/EF8 code

//For my database TBS, this is my EF Context+++++++++++++++++++++++++++++++++++++++

public partial class EF_TBS_Context : DbContext
{
    public EF_TBS_Context(DbContextOptions<EF_TBS_Context> options)
        : base(options)
    {
    }

    //...a lot of code here......................
}

//this is my Factory pattern +++++++++++++++++++++++++++++++++++++++++++++++++++++
public class EF_TBS_Context_Factory : IDesignTimeDbContextFactory<EF_TBS_Context>
{
    static EF_TBS_Context_Factory()
    {
        //static constructor, can be useful later
    }

    static string? _connectionString = null;
    static byte _compatibilityLevel = 120;

    public static void SetConnectionString(string? connectionString)
    {
        _connectionString=connectionString;
    }

    public static void SetDatabaseCompatibilityLevel(byte compatibilityLevel)
    {
        _compatibilityLevel = compatibilityLevel;
    }

    public EF_TBS_Context CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<EF_TBS_Context>();

        optionsBuilder.UseSqlServer(
            _connectionString, o => o.UseCompatibilityLevel(_compatibilityLevel));

        return new EF_TBS_Context(optionsBuilder.Options);
    }
}

//This is how to set and use Coontext Factory static properties++++++++++++++++++++++++++++++++++
EF_TBS_Context_Factory.SetConnectionString("..some connection string...");
EF_TBS_Context_Factory.SetDatabaseCompatibilityLevel(120);

using EF_TBS_Context ctx = _EF_TBS_ContextFactory.CreateDbContext(new string[0]);
//now you do with ctx what you want
byte compatibility_level = ctx.Database.SqlQuery<byte>(
    $"SELECT compatibility_level AS [Value] FROM sys.databases  WHERE name = {DatabaseName}")
                    .SingleOrDefault();
                    
                    
                    
//this is my DatabaseConnectionTest, it also shows usage of Factory pattern+++++
//and it shows how to get in code Database compatibility level
public bool DoConnectionTest(bool ShowDatabaseConnectionStringInLog = false)
{
    bool hasConnection = false;
    Exception? exConnection = null;

    try
    {
        using EF_TBS_Context ctx = _EF_TBS_ContextFactory.CreateDbContext(new string[0]);
        if (ShowDatabaseConnectionStringInLog)
        {
            string? txt1 = ctx.Database.GetConnectionString();
            _logger.LogInformation("Database connection string:[" + txt1 + "]");
        }
        else
        {
            _logger.LogInformation("Logging of Database connection string is disabled");
        }
        string? DatabaseName = null;
        {
            System.Data.Common.DbConnection conn = ctx.Database.GetDbConnection();
            conn.Open();
            DatabaseName = conn.Database;
            string? txt2 = "Database Info: Database[" + conn.Database + "] ";
            txt2 += "DataSource[" + conn.DataSource + "] ";
            txt2 += "ServerVersion[" + conn.ServerVersion + "] ";
            txt2 += "ConnectionTimeout[" + conn.ConnectionTimeout + "] ";
            DbCommand command = conn.CreateCommand();
            txt2 += "DefaultCommandTimeout[" + command.CommandTimeout + "] ";
            conn.Close();
            _logger.LogInformation(txt2);
        }
        {
            if(!String.IsNullOrEmpty(DatabaseName))
            {
                byte compatibility_level = ctx.Database.SqlQuery<byte>(
                $"SELECT compatibility_level AS [Value] FROM sys.databases  WHERE name = {DatabaseName}")
                    .SingleOrDefault();
                string? text5 = $"Database Compatibility level:{compatibility_level.ToString()}";
                _logger.LogInformation(text5);
            }
        }
        hasConnection = true;
    }
    catch (Exception ex)
    {
        hasConnection = false;
        exConnection = ex;
    }

    if (hasConnection)
    {
        string msg = "Database connection test: SUCCESS.";
        _logger.LogInformation(msg);
    }
    else
    {
        string msg = "Database connection test: FAILED." + exConnection?.Message;
        _logger.LogError(msg);
        //_logger.LogError(exConnection, msg);
    }

    return hasConnection;
}

3 References

[1] High-impact changes - Contains in LINQ queries may stop working on older SQL Server versions
https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#contains-in-linq-queries-may-stop-working-on-older-sql-server-versions

[2] ALTER DATABASE (Transact-SQL) compatibility level
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16#best-practices-for-upgrading-database-compatibility-leve