EF Core中高效查询序列化字符串列表含指定关键词的方案

问题根源 #

EF Core无法将List<string>.Contains()的调用转换为SQL语句，因为数据库中HashTags是以序列化字符串（通常是JSON）存储的，而非原生集合类型。直接用客户端评估会拉取全表数据，性能极差，需要从数据库层面实现过滤逻辑。

方案1：利用EF Core的JSON查询功能（适用于SQL Server 2016+） #

如果HashTags是序列化为JSON数组存储（比如["ab", "cd"]），可以借助EF Core的JSON函数在数据库端完成过滤，无需拉取全表。

步骤1：配置实体序列化转换 #

在DbContext的OnModelCreating中定义HashTags的序列化规则：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .Property(p => p.HashTags)
        .HasConversion(
            list => JsonSerializer.Serialize(list, null),
            json => JsonSerializer.Deserialize<List<string>>(json, null));
}

步骤2：执行JSON包含查询 #

用参数化方式避免SQL注入，精确匹配数组元素：

var keyword = "ab";
var targetJson = JsonSerializer.Serialize(new[] { keyword });

var posts = await _appDbContext.Posts
    .Where(p => EF.Functions.JsonContains(p.HashTags, targetJson))
    .ToListAsync();

该代码会被EF Core转换为SQL Server的JSON_CONTAINS函数，直接在数据库端过滤数据。

方案2：规范化数据库设计（最优性能方案） #

如果可以调整数据库结构，建议将多对多的HashTags拆分为独立关联表，这是最符合数据库设计规范且性能最优的方式。

调整实体类 #

public class Post
{
    public string Id { get; set; }
    public string BodyText { get; set; }
    public ICollection<PostHashTag> PostHashTags { get; set; } = new List<PostHashTag>();
}

public class PostHashTag
{
    public string PostId { get; set; }
    public Post Post { get; set; }
    public string HashTag { get; set; }
}

配置数据库关系 #

在OnModelCreating中设置联合主键和关联关系：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<PostHashTag>()
        .HasKey(pt => new { pt.PostId, pt.HashTag });

    modelBuilder.Entity<PostHashTag>()
        .HasOne(pt => pt.Post)
        .WithMany(p => p.PostHashTags)
        .HasForeignKey(pt => pt.PostId);
}

执行查询 #

给PostHashTag.HashTag字段创建索引后，查询性能会大幅提升：

var keyword = "ab";
var posts = await _appDbContext.Posts
    .Where(p => p.PostHashTags.Any(pt => pt.HashTag == keyword))
    .ToListAsync();

方案3：原生SQL查询（兼容旧版本EF或特殊场景） #

如果EF Core的JSON函数无法满足需求，可以直接编写原生SQL利用SQL Server的JSON处理能力：

var keyword = "ab";
var parameter = new SqlParameter("@keyword", keyword);

var posts = await _appDbContext.Posts
    .FromSqlRaw(@"
        SELECT DISTINCT p.* 
        FROM Posts p
        CROSS APPLY OPENJSON(p.HashTags) AS j
        WHERE j.value = @keyword", parameter)
    .ToListAsync();

OPENJSON会将JSON数组拆分为行，通过value字段匹配关键词后，用DISTINCT避免重复的Post记录。

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