entity framework multiple many to many queries

Question

I have a simple model for security where there are:

• Users
• Roles
• Paths

and many to many links between these tables, so a user to roles, and roles to paths. I am trying to write a function so that from a username and a path it will return a bool value based on whether the user has access to that path. How can I do this with the entity framework? I currently have:

var rolesForUser = _entities.Users
         .Include("Roles")
         .Where(u => u.Login.Equals(username))
         .Select(u => u.Roles);

if(rolesForUser.Count() == 0) return false;

var authentications = _entities.WebPaths
         .Where(p => p.Path == path)
         .WhereIn(p => p.Roles, rolesForUser);

return (authentications.Count() > 0);

which uses an extension method WhereIn, however this can only compare on primatives so this doesn't work at the moment. Any suggestions welcome.

Answer 1

You could probably do it with PredicateBuilder.

Off the top of my head:

var predicate = PredicateBuilder.False<WebPath>();
foreach (var role in from roles in rolesForUser 
                     from r in roles.Role
                     select r)
{
  predicate = predicate.Or (p => p.roles.Any(r => r.Id == role.Id));
}

var authentications = _entities.WebPaths.AsExpandable()
         .Where(p => p.Path == path)
         .Where(predicate);
return (authentications.Count() > 0);