LINQ表达式无法转换问题：如何筛选含指定权限的请求数据？

我有一个包含Requests和Authorities两张表的数据库：

• Authorities表的AuthorityOf列存储字符串（如"AAAA"）；
• Requests表的RequiresAuthorityFrom列存储为JSON字符串，通过Innofactor.EfCoreJsonValueConverter包的[JsonField]特性转换为List<string>（如["AAAA","BBBB","CCCC"]）。

我想编写LINQ查询，获取RequiresAuthorityFrom列表包含Authorities表中任意AuthorityOf值的所有请求，于是写了这段代码：

var result = _dbContext.Requests
    .Where(req => req.RequiresAuthorityFrom.Any(a => _dbContext.Authorities.Select(auth => auth.AuthorityOf).Contains(a)))
    .ToList();

实体类简化定义如下：

public class Authority
{
    public int Id { get; set; }
    public string AuthorityOf { get; set; }
}

public class Request
{
    public int Id { get; set; }
    [JsonField]
    public List<string> RequiresAuthorityFrom { get; set; }
}

执行后出现报错：

The LINQ expression 'a => DbSet()
.Select(a => a.AuthorityOf)
.Any(p => object.Equals(
objA: p,
objB: a))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

我尝试添加AsEnumerable/ToList启用客户端评估，但没有效果，请问该如何编写正确的查询？

方法1：先加载权限值到内存再查询 #

先把Authorities表中的所有AuthorityOf值加载到内存列表，再用这个列表去匹配Requests的JSON数组：

// 先将权限值拉取到本地内存
var authorityValues = _dbContext.Authorities.Select(auth => auth.AuthorityOf).ToList();

// 再查询符合条件的请求
var result = _dbContext.Requests
    .Where(req => req.RequiresAuthorityFrom.Any(a => authorityValues.Contains(a)))
    .ToList();

这种方式把服务器端的子查询转为内存匹配，EF Core能正确翻译这种逻辑，结合JsonField的转换来检查JSON数组中的值是否在内存列表里。

方法2：使用原生SQL JSON查询（推荐） #

如果你的数据库支持JSON函数（比如MySQL的JSON_CONTAINS、SQL Server的OPENJSON），可以直接写原生SQL查询绕过转换器的限制：

MySQL版本： #

var result = _dbContext.Requests
    .FromSqlRaw(@"
        SELECT * FROM Requests
        WHERE EXISTS (
            SELECT 1 FROM Authorities
            WHERE JSON_CONTAINS(Requests.RequiresAuthorityFrom, CONCAT('""', Authorities.AuthorityOf, '""'))
        )
    ")
    .ToList();

SQL Server版本： #

var result = _dbContext.Requests
    .FromSqlRaw(@"
        SELECT r.* FROM Requests r
        JOIN Authorities a ON EXISTS (
            SELECT 1 FROM OPENJSON(r.RequiresAuthorityFrom) j
            WHERE j.value = a.AuthorityOf
        )
    ")
    .ToList();

方法3：更换为EF Core原生JSON特性（EF Core 7+） #

Innofactor的[JsonField]可能不支持服务器端LINQ查询转换，换成EF Core 7+原生的[JsonColumn]特性，原生支持JSON列的LINQ查询：

// 修改Request实体类的特性
public class Request
{
    public int Id { get; set; }
    [JsonColumn] // EF Core 7+ 原生JSON列特性
    public List<string> RequiresAuthorityFrom { get; set; }
}

之后可以用更简洁的LINQ查询：

var result = _dbContext.Requests
    .Where(req => _dbContext.Authorities.Any(a => req.RequiresAuthorityFrom.Contains(a.AuthorityOf)))
    .ToList();

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