我在SQL Server 2022中把JSON数据存储在一个 varbinary(max) 列中。

我知道SQL Server 2025提供了专门的JSON数据类型，但目前我仍在使用SQL Server 2022。

我使用EF Core来访问数据，这个JSON列被建模为 [BinaryData] 类型，并搭配一个自定义的 BinaryDataConverter，遵循 [Value Conversions] 文档中的说明。

我知道 LIKE 运算符可以作用于 varbinary(max) 列，但我不知道如何在EF Core中实现它。

我尝试了这段代码，但如预期地抛出了一个异常：

InvalidOperationException：无法翻译LINQ表达式。

如何让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编写一个静态方法。它不需要实现内容，它只用于EF Core的转换。

这里使用了C# 14的扩展成员 在 EF 命名空间上。

DbFunctionsExtensions.cs

using System;
using Microsoft.EntityFrameworkCore;

namespace SearchBinaryData;

internal static class DbFunctionsExtensions
{
    extension(EF)
    {
        public static bool Like(BinaryData matchExpression, BinaryData pattern)
            => throw new InvalidOperationException("This method is for use with Entity Framework Core only and has no in-memory implementation.");
    }
}

然后你需要在EF Core中注册该函数。在你的DbContext中，为 OnModelCreating 方法添加一个新的覆盖，并告诉它如何把这个 Like 方法翻译成SQL表达式。（这还需要一个额外的 using Microsoft.EntityFrameworkCore.Query.SqlExpressions）

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .HasDbFunction(((Delegate)DbFunctionsExtensions.Like).Method)
        .HasTranslation(args => new LikeExpression(args[0], args[1], escapeChar: null, typeMapping: null));
}

你还需要为 BinaryData 注册一个默认类型映射。在你的DbContext的 ConfigureConventions 方法中添加这一行。

configurationBuilder.DefaultTypeMapping<BinaryData>().HasConversion<BinaryDataConverter>();

最后，用新的 Like 函数替换你的where子句，该函数接受 BinaryData 个参数：

.Where(t => EF.Like(t.JsonPayload, EF.Parameter(BinaryData.FromString("%Cédric%"))))

EF Core将把它翻译成正确的SQL，查询就能工作。 🥳

-- Executed DbCommand (14ms) [Parameters=[@FromString='0x2543C3A96472696325' (Size = 8000)], CommandType='Text', CommandTimeout='30']
SELECT [e].[Id], [e].[JsonPayload]
FROM [Examples] AS [e]
WHERE [e].[JsonPayload] LIKE @FromString