'LINQ where clause

I have the following linq query

var ret = from u in MenuHeaders
   .Include("MenuHeaderItems.MenuItem")                 
   select u;

I need to select ONLY menu headers which exist for certain users, which belong to a certain role given a user id.

So, the relational path would be something like this...

MenuHeader    RoleMenuHeaders   Roles      UserRoles   Users
----------    ---------------   -----      ---------   -----
ID        <---MenuHeaderID  |-> ID <---|   UserID----->ID
              RoleID -------|          |-- RoleID

How do I get my above query to only return MenuHeaders where UserID=1?

linqwhere-clause


Solution 1:[1]

If you're using LINQ to Entities, this relationship is probably automatically mapped via properties, and (assuming these are many-to-many relationships, as they appear to be in the schema you show) you can take advantage of the Any operator:

var ret = from mh in MenuHeaders.Include("MenuHeaderItems.MenuItem")      
   where mh.Roles.Any(r => r.Users.Any(u => u.UserId == 1))
   select mh;

Solution 2:[2]

Well, you could do it with a join:

var ret = from mh in MenuHeaders.Include("MenuHeaderItems.MenuItem")
          join ur in UserRoles on mh.RoleID equals u.RoleID
          where ur.UserID == 1
          select mh;

(I don't know whether you need Include("UserRoles.UserRole") or anything like that. I don't know which LINQ provider you're using.)

Solution 3:[3]

Adding a where clause that references a joined table modifies the resultant query and invalidates the Include.

Check this out. How to make an Include really include.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 StriplingWarrior
Solution 2 Jon Skeet
Solution 3 Glorfindel

Related Questions