.NET8/EF8 - 异常：Microsoft.Data.SqlClient.SqlException (0x80131904)：'$' 附近有不正确的语法

 

1 问题描述

1.1 环境

本文适用的典型环境是 C#/.NET8/Entity Framework 8/MS SQL Server。

 

1.2 问题表现

您会遇到以下异常：Microsoft.Data.SqlClient.SqlException (0x80131904)：'$' 附近有不正确的语法。

 

1.3 问题原因

通常情况下，您的数据库兼容级别为 120，而 Entity Framework 兼容级别为 160。 简而言之，您的 EF 正在生成的 SQL 代码使用的 SQL 语言版本比您的数据库能够理解的版本更高级。 或者类似的级别值，但关键在于兼容级别不匹配。

您可能会问，EF8 生成的新的 SQL 代码有多大不同？ 简而言之，它看起来像是试图向 SQL Server 发送 JSON 列表，较新的 SQL Server 可以理解，但旧的 SQL Server 无法理解，因此它会将其视为语法错误。

 

1.4 验证问题

要验证这确实是您遇到的问题，您需要检查您的数据库兼容级别。 相应的脚本如下所示。

 

1.5 解决方案

我看到三种解决此问题的方法

1. 更改 SQL Server 上的兼容级别
2. 在您的 EF 代码中静态更改兼容级别
3. 在您的 EF 代码中动态更改兼容级别，使其适应在首次与数据库接触期间（通常在登录过程中）获得的数据库兼容级别。

 

1.5.1 更改 SQL Server 上的兼容级别

• 如果您的数据库是 SQL Server 2016 (13.x) 或更高版本，或者您正在使用 Azure SQL，请检查配置的兼容级别。 如果兼容级别低于 130 (SQL Server 2016)，请考虑将其修改为更高的值
• 如果您的 SQL Server 较旧，除了更改 EF 中的兼容级别外，您别无选择

 

1.5.2 在您的 EF 代码中静态更改兼容级别

下面我提供一个示例代码，用于在您使用工厂模式或其他方式时更改 EF 中的兼容级别。 您可以在您的工厂中静态分配新的兼容级别

 

1.5.3 在您的 EF 代码中动态更改兼容级别

如果您正在使用 EF8 的工厂模式，您可以在第一次调用数据库时查询数据库兼容级别，并设置您的工厂，以便每次生成具有适当兼容级别的 EFContext。 我在下面展示了如何在数据库连接测试期间获取数据库兼容级别。

例如，一种策略是，从 EF 中的较低兼容级别 [2]（例如 80）开始，然后根据数据库的实际数据状态，提高后续连接的兼容级别。 工厂模式支持这种场景。

1.6 副作用

据称，EF 的较高兼容级别会生成更高效/更快的 SQL 语句/查询。

 

2 代码示例

您可能需要的 SQL 脚本。

--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;

 

C#/EF8 示例代码

//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 参考

[1] 高影响变更 - LINQ 查询中的 Contains 可能会在旧版 SQL Server 上停止工作
https://learn.microsoft.com/zh-cn/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) 兼容级别
https://learn.microsoft.com/zh-cn/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16#best-practices-for-upgrading-database-compatibility-leve