score:7

Accepted answer

I've not done this before but my guess is that you should do it like this:

 //Get the roles 
 var roles = (from q in context.Roles
     where context.UserHasRole.Any(o => o.UserId == userId && o.RoleId == q.Id)
     select q).ToList();

 return roles;

score:2

Try:

var roles = (from q in context.Roles 
   context.UserHasRole.Any(o => 
      o.UserId == userId 
      && o.RoleId == q.Id)
   select q).ToList();

score:2

You're asking for the roles where "there is a relation with a specific user in the UserHasRole table" and "there is a relation with the role in the UserHasRole table". The first condition will have the same result for every role, because it does not depend on the roles.

You want the roles where "there is a relation between the user and the role".

var roles = (from q in context.Roles
       where context.UserHasRole.Any(o => o.UserId == userId && o.RoleId == q.Id)
       select q).ToList();

return roles;

score:2

Break down the query and see what you area actually doing:

from q in context.Roles

So for every Role...

where context.UserHasRole.Any(o => o.UserId == userId)

Where there exists any entry in UserHasRole with the given userId...

&& context.UserHasRole.Any(p => p.RoleId == q.Id)

And there exists an entry in UserHasRole with a roleId matching the current Role...

select q

Select that role.

I have a feeling this logic is not what you desired, as it is independently checking to see if there is a UserHasRole entry with the userId and a UserHasRole entry with the role Id. But not that there is a UserHasRole entry with both the userId and the role Id, which is probably the desired effect:

//Get the roles 
var roles = (from q in context.Roles
             where context.UserHasRole.Any(o => o.UserId == userId
                                             && o.RoleId == q.Id)
             select q).ToList();

return roles;

score:2

I can't say for sure without looking at your data, but I would expect your query to give you many more roles back than you are looking for.

Here is pseduo-code for your existing query:

Get all roles
From those roles, accept only those where these are true:
  Any mapping exists that matches the user ID
  Any mapping exists that matches the role ID

The problem is that you don't ensure that those Any clauses boil down to the same mapping. The user doesn't have to have that specific role - the user just has to have some role, and the role has to be mapped to some user.

Here's what you really want your query to do:

Get all roles
From those roles, accept only those where this is true:
  Any mapping exists that matches *both* the user ID and the role ID

To implement this query:

var roles = (from r in context.Roles
             where context.UserHasRole.Any(uhr => uhr.UserId == userId
                 && uhr.RoleId == r.Id)
             select r)
            .ToList();

Related Articles