C# Linq query group by in many to many table between many tables

Question

I have the following entities in C#. I want to get the users group by teamname. I tried the following query but the WorkUser is not included in the join. the include statement is not working in the following query.

        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; }
}   

The sample data is ashown below:

    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;}
}

Can anyone help me to write the correct linq query so that i will get the expected result.

Thanks

Answer 1

There is no requirement to Group or Join. You are still missing Team Name. Try following :

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

Answer 2

        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();