CODEWITHSUNDEEP
linqwhere-clause
Scottie
Scottie

Reputation: 11308

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?

Upvotes: 1

Views: 779

Answers (3)

strattonn
strattonn

Reputation: 2012

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.

Upvotes: 0

StriplingWarrior
StriplingWarrior

Reputation: 156748

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;

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1503924

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.)

Upvotes: 0

Related Questions