如何在EF Core中对BinaryData属性使用LIKE运算符？

我在SQL Server 2022的varbinary(max)列中存储JSON数据，虽然SQL Server 2025提供了正式的JSON数据类型，但目前只能使用2022版本。我通过EF Core访问数据，按照值转换的规范将JSON列建模为BinaryData类型，并使用自定义的BinaryDataConverter。我知道LIKE运算符可作用于varbinary(max)列，但不知道如何在EF Core中实现。尝试以下代码时，出现了预期的异常：

InvalidOperationException: The LINQ expression could not be translated.

如何让EF Core正确转换LIKE运算符？

相关代码文件 #

SearchBinaryData.csproj #

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="10.0.7" />
    <PackageReference Include="Testcontainers.MsSql" Version="4.11.0" />
  </ItemGroup>

</Project>

Program.cs #

using System;
using System.Linq;
using System.Text.Encodings.Web;
using System.Text.Json;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using SearchBinaryData;
using Testcontainers.MsSql;

var container = new MsSqlBuilder("mcr.microsoft.com/mssql/server:2022-latest")
    .WithName("SearchBinaryData")
    .WithReuse(true)
    .Build();
await container.StartAsync();
const string dbName = "AppDatabase";
const string sql = $"IF DB_ID('{dbName}') IS NULL BEGIN CREATE DATABASE {dbName} END";
await container.ExecScriptAsync(sql);
var builder = new SqlConnectionStringBuilder(container.GetConnectionString());
builder.InitialCatalog = dbName;

var dbContextOptions = new DbContextOptionsBuilder<AppDbContext>()
    .UseSqlServer(builder.ConnectionString)
    .Options;
await using var dbContext = new AppDbContext(dbContextOptions);
await dbContext.Database.EnsureCreatedAsync();

if (!await dbContext.Examples.AnyAsync())
{
    var encoder = JavaScriptEncoder.UnsafeRelaxedJsonEscaping;
    var options = new JsonSerializerOptions { Encoder = encoder };
    var payload = BinaryData.FromObjectAsJson(new { Name = "Cédric Luthi" }, options);
    dbContext.Examples.Add(new Example { JsonPayload = payload });
    await dbContext.SaveChangesAsync();
}

var results = await dbContext.Examples
    .Where(t => EF.Functions.Like(t.JsonPayload.ToString(), "%Cédric%"))
    .ToListAsync();
foreach (var result in results)
{
    Console.WriteLine(result.JsonPayload);
}

AppDbContext.cs #

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace SearchBinaryData;

internal class AppDbContext(DbContextOptions options) : DbContext(options)
{
    public DbSet<Example> Examples { get; set; } = null!;

    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder.Properties<BinaryData>().HaveConversion<BinaryDataConverter>();
    }

    private class BinaryDataConverter()
        : ValueConverter<BinaryData, byte[]>(v => v.ToArray(), v => BinaryData.FromBytes(v));
}

internal class Example
{
    public int Id { get; set; }

    public BinaryData JsonPayload { get; set; } = null!;
}

EF Core无法直接将BinaryData.ToString()转换为SQL，因为值转换器仅处理属性的读写逻辑，不会自动转换LINQ查询中的方法调用。要实现对varbinary(max)列的LIKE查询，有两种可行方案：

方案1：直接在LINQ中调用SQL转换函数 #

通过EF.Property直接访问列的原始字节数组类型，再用EF.Functions.Cast转换为字符串，EF Core会将其翻译为SQL的CONVERT操作：

var searchTerm = "%Cédric%";
var results = await dbContext.Examples
    .Where(x => EF.Functions.Like(
        EF.Functions.Cast<string>(EF.Property<byte[]>(x, "JsonPayload")), 
        searchTerm))
    .ToListAsync();

如果需要指定UTF-8编码避免乱码，可改用EF.Functions.SqlFunction调用带编码参数的CONVERT：

var results = await dbContext.Examples
    .Where(x => EF.Functions.Like(
        EF.Functions.SqlFunction<string>("CONVERT", new[] { typeof(string), typeof(byte[]), typeof(int) }, EF.Property<byte[]>(x, "JsonPayload"), 65001),
        "%Cédric%"))
    .ToListAsync();

（注：65001是SQL Server中UTF-8编码的参数值）

方案2：自定义数据库函数映射 #

如果需要重复使用该逻辑，可在DbContext中定义映射到SQL原生CONVERT的函数：

// 在AppDbContext类中添加
[DbFunction("CONVERT", IsBuiltIn = true)]
public static string ConvertToUtf8String(byte[] binaryData)
    => throw new InvalidOperationException("仅用于EF Core查询转换");

之后即可在查询中直接调用：

var results = await dbContext.Examples
    .Where(t => EF.Functions.Like(AppDbContext.ConvertToUtf8String(t.JsonPayload), "%Cédric%"))
    .ToListAsync();

注意事项 #

• 转换时需确保编码一致：BinaryData.FromObjectAsJson默认用UTF-8，SQL Server的CONVERT默认用数据库编码，指定65001参数可避免特殊字符乱码。
• 对varbinary(max)列做字符串转换后执行LIKE查询无法利用索引，数据量大时性能会受影响。若频繁需要此类查询，建议新增文本类型的冗余存储列，或考虑使用全文索引。

内容的提问来源于stack exchange，提问作者0xced