EF Core中如何用LINQ高效检查文本列JSON数组是否含指定整数

针对PostgreSQL中存储为text类型的JSON数组列，要实现与指定整数数组的任意匹配，可以通过以下两种LINQ方案解决，无需使用FromSqlRaw：

方案1：不修改实体类，直接映射数据库函数实现 #

此方案适配现有实体类结构，通过映射PostgreSQL原生函数实现原SQL的逻辑：

步骤1：在DbContext中映射translate函数 #

在你的DbContext类中添加静态方法，映射PostgreSQL的translate内置函数：

using Microsoft.EntityFrameworkCore;

public class YourDbContext : DbContext
{
    // 其他DbSet和配置...

    [DbFunction("translate", IsBuiltIn = true)]
    public static string Translate(string input, string fromChars, string toChars)
    {
        // 仅用于EF Core查询翻译，无需实现具体逻辑
        throw new NotImplementedException("This method is for EF Core query translation only.");
    }
}

步骤2：编写LINQ查询 #

利用EF.Functions.Cast将转换后的字符串转为整数数组，再通过EF.Functions.Overlaps实现数组重叠匹配（对应PostgreSQL的&&操作符）：

var targetIds = new[] { 1, 7 };

var result = await _dbContext.Table
    .Where(x => !string.IsNullOrEmpty(x.TypeJson))
    .Where(x => 
        EF.Functions.Overlaps(
            EF.Functions.Cast<int[]>(YourDbContext.Translate(x.TypeJson, "[]", "{}")),
            targetIds
        )
    )
    .ToListAsync();

此查询会生成与你提供的原生SQL等价的语句，精准匹配包含目标数组任意值的行。

方案2：使用值转换器将text列映射为int[] #

如果允许修改实体类，可以将数据库的text类型JSON数组直接映射为实体的int[]属性，简化查询逻辑：

步骤1：修改实体类 #

将原TypeJson属性替换为int[]类型：

public class Table
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int[] TypeIds { get; set; } // 替代原TypeJson属性
}

步骤2：配置值转换器 #

在DbContext的OnModelCreating方法中，配置属性与数据库列的转换规则：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Table>()
        .Property(t => t.TypeIds)
        .HasColumnName("TypeJson") // 对应数据库中的TypeJson列
        .HasColumnType("text")
        .HasConversion(
            // 将int[]序列化为JSON字符串存入数据库
            array => System.Text.Json.JsonSerializer.Serialize(array, new System.Text.Json.JsonSerializerOptions()),
            // 将数据库中的JSON字符串反序列化为int[]
            jsonStr => System.Text.Json.JsonSerializer.Deserialize<int[]>(jsonStr, new System.Text.Json.JsonSerializerOptions())
        );
}

步骤3：编写简洁LINQ查询 #

直接使用EF.Functions.Overlaps完成匹配：

var targetIds = new[] { 1, 7 };

var result = await _dbContext.Table
    .Where(x => x.TypeIds != null && EF.Functions.Overlaps(x.TypeIds, targetIds))
    .ToListAsync();

为什么原JsonContains方案报错 #

EF.Functions.JsonContains要求操作的列必须是PostgreSQL的json或jsonb类型，而你的列是text类型，EF Core无法识别为JSON参数，因此抛出The EF JSON methods require a JSON parameter and none was found异常。上述方案通过将text转为整数数组的方式，避开了JSON函数的类型限制，同时保证查询效率。

内容的提问来源于stack exchange，提问作者Working Pickle