Entity Framework为何不参数化字符串比较？如何解决该问题？

问题背景 #

现有如下EF VB查询：

dim id = db.Logins.Where(Function(x) x.FirstName <> "").Select(Function(x) x.Id).FirstOrDefault

EF默认生成的SQL会直接嵌入硬编码常量：

SELECT TOP (1) 
    [Extent1].[Id] AS [Id]
    FROM [dbo].[Login] AS [Extent1]
    WHERE  NOT ((N'' = [Extent1].[FirstName]) AND ([Extent1].[FirstName] IS NOT NULL))

将FirstName列改为Azure SQL Server数据库加密后，该SQL执行失败。经测试确认：加密列无法与硬编码常量直接比较，必须使用参数化查询。

测试验证 #

• 可正常执行的SQL：

  select top 100 * from login where firstname is not null
  

  参数化版本也可正常运行：

  DECLARE @param nvarchar(10) = ''
  select top 100 * from login where firstname <> @param
  

• 执行失败的SQL（硬编码常量）：

  select top 100 * from login where firstname <> ''
  

  报错信息：

  Msg 402, Level 16, State 2, Line 4
  The data types nvarchar(100) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'DBNAME') and varchar are incompatible in the not equal to operator.
  Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 1]
  Statement(s) could not be prepared.

可行解决方案 #

1. 显式使用变量传递常量 #

在查询中先定义变量存储常量值，EF会自动将变量转为参数：

Dim emptyStr As String = ""
Dim id = db.Logins.Where(Function(x) x.FirstName <> emptyStr).Select(Function(x) x.Id).FirstOrDefault

2. 手动构建参数化原生SQL #

直接使用FromSqlRaw编写参数化SQL，精准控制参数传递：

Dim emptyParam = New SqlParameter("@emptyStr", SqlDbType.NVarChar) With {.Value = ""}
Dim id = db.Logins.FromSqlRaw("SELECT TOP 1 Id FROM Login WHERE FirstName <> @emptyStr", emptyParam).FirstOrDefault()

3. 全局拦截器替换硬编码常量 #

实现DbCommandInterceptor拦截EF生成的SQL，自动将硬编码常量替换为参数，无需修改现有查询：

Public Class ParameterizeConstantsInterceptor
    Inherits DbCommandInterceptor

    Public Overrides Function ReaderExecuting(command As DbCommand, eventData As CommandEventData, result As InterceptionResult(Of DbDataReader)) As InterceptionResult(Of DbDataReader)
        ' 替换SQL中的硬编码空字符串为参数
        If command.CommandText.Contains("N'' =") Then
            Dim param = command.CreateParameter()
            param.ParameterName = "@emptyVal"
            param.Value = ""
            param.DbType = DbType.String
            command.Parameters.Add(param)
            command.CommandText = command.CommandText.Replace("N'' =", "@emptyVal =")
        End If
        Return MyBase.ReaderExecuting(command, eventData, result)
    End Function
End Class

在DbContext中注册拦截器：

Protected Overrides Sub OnConfiguring(optionsBuilder As DbContextOptionsBuilder)
    optionsBuilder.AddInterceptors(New ParameterizeConstantsInterceptor())
End Sub

4. 升级EF版本 #

部分较新的EF版本（如EF Core 3.1及以上）对加密字段的参数化支持更完善，升级后可能自动处理硬编码常量为参数。

内容的提问来源于stack exchange，提问作者Joshua Frank