代码之家 › 专栏 › 技术社区 › Mukil Deepthi

多表间多对多表中的C LINQ查询GROUP BY

linq-to-entities c#

Mukil Deepthi · 技术社区 · 6 年前

我在C_有以下实体。我要按TeamName获取用户组。我尝试了以下查询,但联接中不包括workuser。include语句在以下查询中不起作用。

        var query = from gu in DbContext.WorkTeamUsers
                    select gu;

        var query1 = query.Include(x => x.WorkUser);

    var result = await (from team in DbContext.WorkTeams
                        join tgu in query1 on team.WorkTeamId equals tgu.WorkTeamId
                        join u in DbContext.WorkUsers on tgu.UserId equals u.UserId
                        group tgu by tgu.WorkTeam.Name into grp
                        select new
                        {
                            Name = grp.Key,
                            Value = grp.ToList()
                        }).ToListAsync();


public class WorkTeam
{
    [Key]
    public int WorkTeamId { get; set; }

    public ICollection<WorkTeamUser> WorkTeamUsers { get; } 
                                                    = new List<WorkTeamUser>();     
}

public class WorkUser
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int WorkUserId { get; set; }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int UserId { get; set; }

    public string UserName { get; set; }

    public ICollection<WorkTeamUser> WorkTeamUsers { get; } = new List<WorkTeamUser>();
}

public class WorkTeamUser
{
    [Key]
    public int Id { get; set; }

    public int WorkTeamId { get; set; }

    public int UserId { get; set; }

    public WorkTeam WorkTeam { get; set; }

    public WorkUser WorkUser { get; set; }
}

样本数据如下:

    Table WorkTeamUser:
    =====================
Id  UserId  WorkTeamId
--  ------  ----------
1   2       1
2   11      1
6   10      3
7   15      1
8   16      3

Table WorkUser:
================
UserId  UserName
2       John
11      Bob
10      Daniel
15      Simon
16      Fred


Expected result:

public class Result {
    public string TeamName {get;set;}
    public List<WorkUser> Users {get;set;}
}

有谁能帮我写正确的linq查询,这样我就能得到预期的结果。

谢谢

2 回复 | 直到 6 年前

jdweng 6 年前

不需要分组或加入。您仍然缺少团队名称。尝试以下操作:

            List<Result> results = DbContext.WorkTeams.Select(x => new Result() {
                TeamName = x.Name,
                 Users = x.WorkTeamUsers.Select(y => y.WorkUser).ToList()
            }).ToList();

Mukil Deepthi 6 年前

        var result = await (from g in DbContext.WorkTeamUsers
                   group g.WorkUser by g.WorkTeam.Name into grp
                   select new
                   {
                       TeamName = grp.Key,
                       Users = grp.ToList()
                   }).ToListAsync();