EF Core自有集合与外部集合的服务端交集查询实现问题

你当前使用JSON转换器将UserProfile.WorkTypes存储为JSON数组，尝试用Intersect做交集查询时EF Core无法翻译，这是因为EF Core无法把内存集合的Intersect操作转换为针对JSON列的SQL逻辑。以下是几种可行的服务端实现方案：

方案1：利用EF Core数据库原生JSON查询函数 #

根据你使用的数据库，调用对应的JSON查询函数来检查交集，以下是主流数据库的实现示例：

SQL Server #

使用EF.Functions.JsonContains检查JSON数组中是否存在目标集合的任一元素：

using System.Text.Json;

// ...

var users = userDbContext.Users
    .Include(e => e.Profile)
    .Where(e => e.Id == sameId)
    .Where(e => sameWorkTypes.Any(wt => 
        EF.Functions.JsonContains(e.Profile.WorkTypes, JsonSerializer.Serialize(wt))
    ))
    .ToList();

PostgreSQL #

使用EF.Functions.JsonbExistsAny判断JSONB数组与目标集合是否有交集：

var users = userDbContext.Users
    .Include(e => e.Profile)
    .Where(e => e.Id == sameId)
    .Where(e => EF.Functions.JsonbExistsAny(e.Profile.WorkTypes, sameWorkTypes))
    .ToList();

MySQL #

使用EF.Functions.JsonContains实现类似逻辑：

using System.Text.Json;

// ...

var users = userDbContext.Users
    .Include(e => e.Profile)
    .Where(e => e.Id == sameId)
    .Where(e => sameWorkTypes.Any(wt => 
        EF.Functions.JsonContains(e.Profile.WorkTypes, JsonSerializer.Serialize(wt))
    ))
    .ToList();

方案2：使用原生SQL查询 #

如果EF Core的JSON函数无法满足需求，可以直接编写原生SQL操作JSON列，灵活性更高：

using System.Text.Json;

// ...

var targetWorkTypesJson = JsonSerializer.Serialize(sameWorkTypes);
var users = userDbContext.Users
    .Include(e => e.Profile)
    .FromSqlInterpolated($@"
        SELECT u.*
        FROM Users u
        INNER JOIN UserProfiles up ON u.Id = up.UserId
        WHERE u.Id = {sameId}
        AND EXISTS (
            SELECT 1
            FROM OPENJSON(up.WorkTypes) AS WorkType
            WHERE WorkType.value IN (SELECT value FROM OPENJSON({targetWorkTypesJson}))
        )
    ")
    .ToList();

方案3：调整实体设计（推荐长期方案） #

如果业务允许，将WorkTypes拆分为独立的关联表，这是最符合EF Core设计理念的方式，能避免JSON查询的局限性，同时提升查询性能：

新增关联实体 #

public class UserWorkType
{
    public Guid UserProfileId { get; set; }
    public UserProfile UserProfile { get; set; }
    public int WorkType { get; set; }
}

修改UserProfile实体 #

public class UserProfile
{
    public Guid Id { get; set; }
    public ICollection<UserWorkType> WorkTypes { get; set; }
    public User User { get; set; }
    public Guid UserId { get; set; }
}

调整上下文配置（移除JSON转换器） #

在OnModelCreating中配置关联关系：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);        

    modelBuilder.Entity<UserWorkType>(builder =>
    {
        builder.HasKey(wt => new { wt.UserProfileId, wt.WorkType });
        builder.HasOne(wt => wt.UserProfile)
               .WithMany(p => p.WorkTypes)
               .HasForeignKey(wt => wt.UserProfileId);
    });
}

执行查询 #

此时可以使用标准LINQ语法，EF Core能完美翻译为SQL：

var users = userDbContext.Users
    .Include(e => e.Profile)
    .Include(e => e.Profile.WorkTypes)
    .Where(e => e.Id == sameId)
    .Where(e => e.Profile.WorkTypes.Any(wt => sameWorkTypes.Contains(wt.WorkType)))
    .ToList();

为什么原查询无法翻译？ #

EF Core无法将内存集合的Intersect操作转换为针对JSON列的SQL逻辑——JSON列在数据库中以字符串形式存储，EF Core默认无法解析其内部的集合结构来执行交集运算，必须借助数据库原生的JSON处理能力或调整实体设计。

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