locked
EF: Where clause in where clause in Navigation property? RRS feed

  • Question

  • Hi I am fairly new to EF and I cannot seem to figure out how to return the data I want. I am just trying to get back all the users where their navigation entities UserRoles match a specific ID.

            public IEnumerable<User> GetUsersForRole(Role role)
            {
                if (!RoleExists(role)) {
                    throw new ArgumentException(MissingRole);
                }
    
                return dbContext.Users.Where(u => u.UserRoles.Where(ur => ur.RoleID == role.RoleID));
            }

    I have also tried to user to use an Include but I can't figure out the rather cryptic messages I keep getting back (I am on pain med's at the moment so I am experiencing brain farts a plenty and the last thing I need right now are MS's "helpful" error messages).

    Please can someone clarify how I am supposed to return what I need.

    Thank you in advance!


    Thanks, Cep

    Wednesday, June 27, 2012 3:20 PM

Answers

  • Hi Cepeleon;

    The issue you are most likely having is that the first Where clause lambda expression is not being compared to anything and therefor is not returning a boolean which a Where clause requires . To correct the issue add a .Any() method as shown below and it should work.

    return dbContext.Users.Where(u => u.UserRoles.Where(ur => ur.RoleID == role.RoleID).Any());

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Cepeleon Thursday, June 28, 2012 8:01 AM
    Thursday, June 28, 2012 1:58 AM

All replies

  • are you trying to join 2 tables?

    can you write a select statement to understand what you are trying to do


    KiranMP

    Wednesday, June 27, 2012 3:37 PM
  • I am trying to return all "User" entities where their Navigation Property entities within "UserRoles" match the RoleID property passed into the method. I am not joining tables I am using the Entity Framework.


    Thanks, Cep

    I guess if you were doing it in SQL it would be

    SELECT * FROM users
    INNER JOIN userroles ON users.userid = userroles.userid
    WHERE userroles.roleid = <roleid>;

    • Edited by Cepeleon Wednesday, June 27, 2012 3:46 PM
    Wednesday, June 27, 2012 3:44 PM
  • you can write something like this

    dbContext.Users.join(dbContext.userroles,u.RoleID,ur=>ur.RoleId,(u,ur)=>new{userName=u.name,Role=u.Role})


    KiranMP

    Wednesday, June 27, 2012 4:01 PM
  • What you have written does not make sense to me. Where are you comparing the value of RoleID passed to the method to each UserRole entity?

    Thanks, Cep

    Wednesday, June 27, 2012 4:12 PM
  • sorry missed where condition

    dbContext.Users.join(dbContext.userroles,u=>u.RoleID,ur=>ur.RoleId,(u,ur)=>new{userName=u.name,Role=u.Role}).where(uandur=>uandur.RoleID==Id)


    KiranMP

    Wednesday, June 27, 2012 4:22 PM
  • Or could it be this way.. just an example replace with your real entities

    var that = ne.Categories.Join(ne.Products.Where(ii => ii.ProductID < 20), c => c.CategoryID, p => p.CategoryID, (c,p) => c);

    Wednesday, June 27, 2012 4:27 PM
  • Hi Cepeleon;

    The issue you are most likely having is that the first Where clause lambda expression is not being compared to anything and therefor is not returning a boolean which a Where clause requires . To correct the issue add a .Any() method as shown below and it should work.

    return dbContext.Users.Where(u => u.UserRoles.Where(ur => ur.RoleID == role.RoleID).Any());

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Cepeleon Thursday, June 28, 2012 8:01 AM
    Thursday, June 28, 2012 1:58 AM
  • Thank you all for your help!

    Thanks, Cep

    Thursday, June 28, 2012 8:02 AM
  •   

    Not a problem, glad I was able to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, June 28, 2012 2:32 PM