如何用EF查询获取含字符'A'的分组人员关联结果？

现有数据库表结构：

• Person表：包含firstname、lastname、id字段
• roles表：存储字符串类型的角色数据
• categories表：存储字符串类型的类别数据
• Occupation表：通过外键关联roles和categories，建立两者的关系
• PersonOccupation表：关联Person表和Occupation表，建立人员与职业信息的关联

需求：搜索firstname、lastname、roles或categories中包含字符'A'的记录，返回每个Person对应Categories和Roles的数组形式结果。当前LINQ查询会返回重复的人员记录（每个类别或角色单独生成一条人员记录），需要修正为分组后的目标结构。

当前LINQ代码 #

Context.PersonOccupation
    .Include(x => x.Occupation)
    .ThenInclude(x => x.categories)
    .Include(x => x.Occupation)
    .ThenInclude(x => x.roles)
    .Include(x => x.Person)
    .AsNoTracking();

当前返回结果（重复记录） #

{
    "Res": [
        {
            "PersonId": "1",
            "LastName": "Chris",
            "FirstName": "Angel",
            "Categories": "HumanResource"
        },
        {
            "PersonId": "1",
            "LastName": "Chris",
            "FirstName": "Angel",
            "Categories": "It professional"
        },
        {
            "PersonId": "1",
            "LastName": "Chris",
            "FirstName": "Angel",
            "Roles": "Manager"
        },
        {
            "PersonId": "1",
            "LastName": "Chris",
            "FirstName": "Angel",
            "Roles": "Social Activist"
        }
    ]
}

期望返回结果（分组后） #

{
    "Res": [
        {
            "PersonId": "1",
            "LastName": "Chris",
            "FirstName": "Angel",
            "Categories": [
                "HumanResource",
                "It professional"
            ],
            "Roles": [
                "Manager",
                "Social Activist"
            ]
        },
        {
            "PersonId": "2",
            "LastName": "Tex",
            "FirstName": "Roy",
            "Categories": [
                "HumanResource",
                "It professional"
            ],
            "Roles": [
                "Manager",
                "Social Activist"
            ]
        }
    ]
}

要实现分组聚合，需要先筛选符合条件的记录，再按Person分组，提取对应的Categories和Roles集合：

var result = Context.PersonOccupation
    .Include(x => x.Occupation)
        .ThenInclude(x => x.categories)
    .Include(x => x.Occupation)
        .ThenInclude(x => x.roles)
    .Include(x => x.Person)
    .AsNoTracking()
    // 筛选包含'A'的记录（可根据需求添加IgnoreCase忽略大小写）
    .Where(x => x.Person.firstname.Contains("A") 
             || x.Person.lastname.Contains("A")
             || x.Occupation.roles.Any(r => r.Contains("A"))
             || x.Occupation.categories.Any(c => c.Contains("A")))
    // 按Person的ID分组
    .GroupBy(x => x.Person.id)
    // 投影成目标结构
    .Select(g => new 
    {
        PersonId = g.Key,
        LastName = g.First().Person.lastname,
        FirstName = g.First().Person.firstname,
        // 提取去重后的类别列表
        Categories = g.SelectMany(x => x.Occupation.categories)
                      .Distinct()
                      .ToList(),
        // 提取去重后的角色列表
        Roles = g.SelectMany(x => x.Occupation.roles)
                 .Distinct()
                 .ToList()
    })
    .ToList();

代码说明 #

1. 筛选逻辑：通过Where子句过滤出符合搜索条件的记录，检查人员姓名、关联的角色或类别中是否包含字符'A'。
2. 分组：使用GroupBy按Person的ID分组，确保每个人员只生成一条结果。
3. 投影聚合：
   • 从分组中取第一条记录的人员姓名信息（同组内人员信息一致）。
   • 通过SelectMany扁平化所有关联的类别和角色，再用Distinct去重，最后转成列表，得到数组形式的Categories和Roles。

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