ASP.NET Core EF Core中无法通过表达式搜索JSON存储的List<KeyValuePair>

EF Core无法将JsonConvert.DeserializeObject这类.NET客户端方法转换为数据库可执行的SQL语句——数据库无法识别Newtonsoft.Json的序列化/反序列化逻辑，导致查询翻译失败。

方案1：使用EF Core原生JSON查询支持（推荐） #

EF Core 2.1及以上版本原生支持JSON列查询，针对不同数据库有对应的函数映射，无需在内存中解析JSON。

针对SQL Server的快速实现 #

使用EF.Functions.JsonValue直接提取JSON中的指定字段值：

if (!string.IsNullOrEmpty(staffCode))
{
    filter.And(x => x.ExtraInfo != null && 
                    EF.Functions.JsonValue(x.ExtraInfo, "$.StaffCode") == staffCode);
}

注意：路径语法需匹配JSON结构，示例中ExtraInfo是对象，所以用$.StaffCode；如果是数组结构，需调整为$[*].StaffCode这类路径。

通用JSON属性映射（EF Core 5+） #

将ExtraInfo直接映射为实体类的强类型属性，EF Core会自动处理JSON的序列化/反序列化：

1. 修改实体类属性：

// 替换原有的string类型ExtraInfo
public List<KeyValuePair<string, string>> ExtraInfo { get; set; }

2. 在DbContext的OnModelCreating中配置JSON转换：

modelBuilder.Entity<CusFinRequest>()
    .Property(e => e.ExtraInfo)
    .HasColumnType("nvarchar(max)") // SQL Server用nvarchar(max)，PostgreSQL用json/jsonb
    .HasConversion(
        v => JsonConvert.SerializeObject(v),
        v => JsonConvert.DeserializeObject<List<KeyValuePair<string, string>>>(v));

3. 直接使用LINQ查询：

if (!string.IsNullOrEmpty(staffCode))
{
    filter.And(x => x.ExtraInfo != null && 
                    x.ExtraInfo.Any(kv => kv.Key == "StaffCode" && kv.Value == staffCode));
}

EF Core会自动将该表达式转换为数据库对应的JSON查询SQL。

方案2：手动构造SQL查询（适用于老版本EF Core） #

如果无法升级EF Core版本，可直接编写包含JSON查询的原生SQL：

public async Task<List<CusFinRequest>> GetDealsWithPagination(int organiztionId, string staffCode, int index, int size)
{
    var query = dbSet
        .FromSqlRaw(@"
            SELECT * 
            FROM CusFinRequest 
            WHERE Organization = @orgId 
              AND JSON_VALUE(ExtraInfo, '$.StaffCode') = @staffCode",
            new SqlParameter("@orgId", organiztionId),
            new SqlParameter("@staffCode", staffCode))
        .Include("RequestHistory")
        .Include("UserCart")
        .Include("UserCart.BusinessLine")
        .OrderByDescending(e => e.CreationDate);

    if (index != default(int) && size != default(int) && size != -1)
    {
        query = query.Skip((index - 1) * size).Take(size);
    }

    return await query.ToListAsync();
}

注意：不同数据库的JSON函数不同，PostgreSQL用json_extract_path_text，MySQL用JSON_EXTRACT，需根据实际数据库调整。

方案3：客户端评估（仅小数据量场景） #

如果数据量很小，可先将基础过滤后的记录加载到内存，再在客户端解析JSON过滤：

public async Task<List<CusFinRequest>> GetDealsWithPagination(int organiztionId, string staffCode, int index, int size)
{
    var query = dbSet
        .Include("RequestHistory")
        .Include("UserCart")
        .Include("UserCart.BusinessLine")
        .Where(x => x.Organization == organiztionId)
        .OrderByDescending(e => e.CreationDate);

    // 先加载到内存
    var allMatches = await query.ToListAsync();

    // 客户端过滤StaffCode
    if (!string.IsNullOrEmpty(staffCode))
    {
        allMatches = allMatches.Where(x => x.ExtraInfo != null && 
            JsonConvert.DeserializeObject<List<KeyValuePair<string, string>>>(x.ExtraInfo)
            .Any(kv => kv.Key == "StaffCode" && kv.Value == staffCode))
            .ToList();
    }

    // 内存分页
    if (index != default(int) && size != default(int) && size != -1)
    {
        allMatches = allMatches.Skip((index - 1) * size).Take(size).ToList();
    }

    return allMatches;
}

警告：此方法会将所有符合Organization条件的记录加载到内存，数据量大时会严重影响性能，仅适合小数据量场景。

内容的提问来源于stack exchange，提问作者Manar Yousry