CODEWITHSUNDEEP
entity-frameworkc#-4.0
Mukil Deepthi
Mukil Deepthi

Reputation: 6452

C# Entityframework how to include based on condition

I have a table with one to many to many relation ie ManyToMany table:

MenuGroup

menuid  groupid
1       4
1       5

Menu

menuid  name
1       One
2       Two

Group

groupid name
4       group4
5       group5

Groupuser

groupid     userid
4           101
4           103
5           102

i would like to get all menus of the user 101 ie

Menuid  groupid name
1       4       group4

But i am getting the wrong out put eventhough writing the correct join queries. Can anyone help what am i doing wrong here?

Menuid  groupid name
1       4       group4
1       5       group5

(from m in context.Menus
      join mg in context.MenuGroup on m.MenuId equals mg.MenuId
      join gu in context.Groupuser on mg.GroupId equals gu.GroupId
      where gu.UserId == 101
      select m);

i would like to include only this particular user's group details though this menu is in other group as well.

my expected output in json would be

{
    "menuid": 1,
    "name": "One",
    "groups":[
        {
            "groupid":4,
"name":"group4"
        }
    ]

}

Upvotes: 2

Views: 151

Answers (1)

pjobs
pjobs

Reputation: 1247

Your linq query looks good, I suspect data issue, but would you like to try the following query to see what you get back. This following query requires navigation properties declared.

var userMenus = context.GroupUser.Where(u=>u.UserId = 101).SelectMany(g=>g.Group.Menus
           .Select(m=> new {Menu=m.MenuId, GroupId=g.GroupId,GroupName=g.Group.name))
           .ToList();

In case you want the complete Menu object

var userMenus = context.GroupUser.Where(u=>u.UserId = 101).SelectMany(g=>g.Group.Menus
           .Select(m=> new {Menu=m.Menu, GroupId=g.GroupId,GroupName=g.Group.name))
           .ToList();

In case you don't care about Group columns and just want Menu then

var userMenus = context.GroupUsers.Where(u => u.UserId == 101)
               .SelectMany(g => g.Group.Menus.Select(m=>m.Menu));

Upvotes: 1

Related Questions