如何将含json_array_elements的PostgreSQL查询转换为Entity Framework写法

前提依赖 #

你需要先安装对应版本的Npgsql.EntityFrameworkCore.PostgreSQL包，该包是PostgreSQL官方适配EF Core的驱动，内置了PG专属函数的映射支持。

步骤1：实体类与DbContext配置 #

实体类定义 #

public class Employee
{
    public int Id { get; set; }
    // 其余业务字段按需添加
    // 推荐用JsonElement存储JSON数组，也可替换为string类型
    public System.Text.Json.JsonElement Offers { get; set; }
}

DbContext字段映射配置 #

在你的DbContext的OnModelCreating方法中添加JSON字段的类型声明：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>()
        .Property(e => e.Offers)
        // 如果你的字段实际是jsonb类型，替换为"jsonb"即可
        .HasColumnType("json");
}

步骤2：EF Core查询写法 #

该写法生成的SQL和你提供的原生语句逻辑完全一致：

// 定义要匹配的目标值集合
var matchValues = new[] { "1", "3" };

var queryResult = await _yourDbContext.Employees
    // 对应CROSS JOIN json_array_elements("Offers"::json)
    .SelectMany(
        e => EF.Functions.JsonArrayElements(e.Offers), 
        (employee, offerValue) => new { Employee = employee, OfferValue = offerValue }
    )
    // 对应WHERE value::TEXT IN('1', '3')
    .Where(x => matchValues.Contains(x.OfferValue.ToString()))
    // 对应ORDER BY "Id" DESC
    .OrderByDescending(x => x.Employee.Id)
    // 如果你只需要去重后的Employee数据，保留下面两行；如果需要保留每行匹配的offer值和Employee的对应关系，删除下面两行即可
    .Select(x => x.Employee)
    .Distinct()
    .ToListAsync();

注意事项 #

• 如果你的Offers字段用string类型存储，需要额外加类型转换：将EF.Functions.JsonArrayElements(e.Offers)替换为EF.Functions.JsonArrayElements(EF.Functions.Cast<string, System.Text.Json.JsonElement>(e.Offers))
• 如果Offers是jsonb类型，将JsonArrayElements替换为JsonbArrayElements即可
• 建议EF Core版本和Npgsql驱动版本都使用6.0及以上，避免函数映射不兼容问题

内容的提问来源于stack exchange，提问作者AG Mohamed