.NET 7.0中如何用Entity Framework通过LINQ查询字典属性

问题场景 #

在.NET 7.0环境下使用Entity Framework 6.4.4，需查询Positions表中关联的Localizations实体的JSON字典属性Values是否包含指定术语：

• Positions表通过TitleId字段关联Localizations表
• Localizations表的Values字段存储JSON格式多语言键值对（示例：{"de": "Beispiel auf Deutsch", "fr": "Exemple en français", "en" : "Example in English"}）
• 原查询代码尝试用Dictionary.ContainsValue()过滤，但触发SQL翻译错误：

var positions = _dataContext.Positions
    .Include(p => p.Title)
    .Where(p => p.Title.Values.ContainsValue(query))
    .AsAsyncEnumerable();

错误信息 #

The LINQ expression 'DbSet()
.LeftJoin(
inner: DbSet(),
outerKeySelector: p => EF.Property<ulong?>(p, "TitleId"),
innerKeySelector: l => EF.Property<ulong?>(l, "Id"),
resultSelector: (o, i) => new TransparentIdentifier<Position, Localization>(
Outer = o,
Inner = i
))
.Where(p => p.Inner.Values.ContainsValue(__query_0))' could not be translated. Additional information: Translation of method 'System.Collections.Generic.Dictionary<string, string>.ContainsValue' failed. 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'.

错误原因 #

EF6.4.4无法将.NET Dictionary.ContainsValue()方法转换为数据库可执行的SQL语句——数据库中Values是存储为字符串的JSON数据，而非.NET字典对象，EF6没有内置逻辑将字典方法映射到JSON查询的SQL函数。

解决方案 #

方案1：使用数据库JSON函数（推荐，性能最优） #

利用数据库原生JSON查询能力（以SQL Server为例），通过OPENJSON解析JSON字段并判断匹配值：

方法A：原始SQL查询

var queryParam = new SqlParameter("@query", $"%{query}%");
var positions = _dataContext.Positions.SqlQuery(@"
    SELECT p.*
    FROM Positions p
    INNER JOIN Localizations l ON p.TitleId = l.Id
    WHERE EXISTS (
        SELECT 1 
        FROM OPENJSON(l.Values)
        WHERE value LIKE @query
    )", queryParam)
    .Include(p => p.Title)
    .AsAsyncEnumerable();

方法B：EF LINQ结合SQL函数

通过自定义SQL函数映射或直接嵌入SQL片段（EF6支持在LINQ中使用原生SQL表达式），实现数据库端过滤：

var positions = _dataContext.Positions
    .Where(p => _dataContext.Localizations
        .Any(l => l.Id == p.TitleId && 
            SqlFunctions.PatIndex($"%{query}%", l.Values) > 0))
    .Include(p => p.Title)
    .AsAsyncEnumerable();

方案2：客户端评估（仅适用于小数据量） #

将数据先拉取到客户端内存，再用.NET Dictionary.ContainsValue()过滤。缺点是会加载所有Positions及关联Localizations数据到内存，数据量大时性能极差：

var positions = _dataContext.Positions
    .Include(p => p.Title)
    .AsEnumerable() // 切换到客户端评估
    .Where(p => p.Title.Values.ContainsValue(query))
    .AsAsyncEnumerable();

实体类参考 #

// Position类
public class Position
{
    public Localization Title { get; set; }
}

// Localization类
public class Localization
{
    public ulong Id { get; set; }
    public Dictionary<string, string> Values { get; set; } = new();
}

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