CODEWITHSUNDEEP
linqmany-to-many
Gavello
Gavello

Reputation: 1491

Linq query join table

I have a User table and a group table one user can be part of many groups 

[USERID]----*-[USERID GROUPID]-*-----[GROUPID]
                (join table)

public partial class user
{
    public int user_id { get; set; }
    public string name { get; set; }
    public virtual ICollection<group> groups{ get; set; }
}

public partial class group
{
    public int user_id { get; set; }
    public string name { get; set; }
    public virtual ICollection<user> users{ get; set; }
}

1) I need to return all the users that belongs to a specific group

var queryable = (from g in db.groups
                  where g.group_id == id
                  select g.users)
                  .ToList()
                  .Select(u => new {    
                      id = u.id,      //Error does not contain definition for id
                      name = u.name   //Error does not contain definition for name
                   });

2) I need to return a specific user that belongs to a specific group

Thanks for your suggestions

Upvotes: 1

Views: 65

Answers (3)

Raja Danish
Raja Danish

Reputation: 235

var queryable = (from p in db.plants
                join u in db.users on p.userId equals u.id
                where p.plant_id == plant_id
                select new { p.id, p.name, u.name}).ToList();

Upvotes: 0

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236248

I need to return all the users that belongs to a specific group

Lambda syntax:

db.groups.Where(g => g.group_id == id).SelectMany(g => g.users)

Or query syntax:

from g in db.groups
where g.group_id == id
from u in g.users
select u

I need to return a specific user that belongs to a specific group

Just like query above, with one additional filter

db.groups.Where(g => g.group_id == id)
         .SelectMany(g => g.users)
         .Where(u => u.id == userId)

Query syntax:

from g in db.groups
where g.group_id == id
from u in g.users
where u.id == userId
select u

Upvotes: 1

Opal
Opal

Reputation: 1

Try something like this:

var queryable = (from p in db.plants
             join u in db.users on p.userId equals u.id
              where p.plant_id == plant_id
              select u)
              .ToList()
              .Select(u => new {    
                  id = u.id,      
                  name = u.name   
               });

where p.userId is the column which connect plants and users tables

Upvotes: 0

Related Questions