Search code examples
linqwhere-clause

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?

Solution

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