LINQ many to many relationship - get a list of grouped objects

1.6k views Asked by At

I have a bit of a complicated one (for me anyway):

I have 2 entities in a many to many relationship.

Projects       {Project_Id, ProjectName}
Users          {User_Id, UserName} 
Projects-Users {Id, Project_Id, User_id}   

A project can be assigned to more users.

Now I want to retrieve the List of all projects (listed once) with Project_id, Name, ListOfAssignedUsers:

Id  ProjectName  Users
1   Project1     U1, U2, U3
2   Project2     
3   Project3     U1

I can do this IN SQL, but can't figure out how to do it in LINQ!

It's LINQ to Entity - Framework(DB first):
My classes look like this:

public partial class Projects { public Projects() { this.Users = new HashSet(); }

    public int Project_Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Users> Users { get; set; }
}

public partial class Users { public Users() { this.Projects = new HashSet(); }

    public int User_Id { get; set; }
    public string UserName { get; set; }

    public virtual ICollection<Projects> Projects { get; set; }
}

(I made the change with removing the id from Projects-Users and made the combo (Project_Id, User_id) the primary key of that.

2

There are 2 answers

0
AdrianD On BEST ANSWER

Getting the list of all the projects with the assigned users:

var projects= from p in db.Projects select new{ project_Id = p.Project_Id, projectName = p.ProjectName, userList = p.Projects-Users.Select(pu=>pu.Users.UserName).ToList()};
2
Robert McKee On

Remove the Id field from the Project-Users table. The primary key if you need one should be {Project_Id,User_id}. There should be (if not already) a foreign key constraint between Projects.Project_Id and Project-Users.Project_Id and another FK between Users.User_Id and Project-Users.User_id

If you have your model mapped in Entity Framework, then you reference it like this:

var result=context.Project.Include(p=>p.Users);

You can dump the results like this:

foreach(var record in result)
{
  Console.WriteLine("{0} {1} {2}",
    record.Project_Id,
    record.ProjectName,
    String.Join(",",record.Users.Select(u=>u.UserName)));
}