143 reads

How to Solve the Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '$ Error

by Mark PelfNovember 16th, 2024

Too Long; Didn't Read

In .NET8/EF8, when working with the SQL Server, EF might create a newer version of SQL queries that old SQL servers/databases will not understand and will consider that as a Syntax Error. The solution is to synchronize Server and Client Compatibility levels.

featured image - How to Solve the Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '$ Error

‘scientists surrounding a computer sketch artsyle’ Image created by HackerNoon AI Image Generator

NET8/EF8 Breaking Change. Old servers/databases might not understand queries from EF8.

Abstract: In .NET8/EF8, when working with the SQL Server, EF might create a newer version of SQL queries that old SQL servers/databases will not understand and will consider that as a Syntax Error. The solution is to synchronize Server and Client Compatibility levels.

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 of 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 this 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:

Change the compatibility level on your SQL server.
Change the compatibility level in your EF code statically.
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 levels 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 the 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;
}