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.

Craig Stuntz · Accepted Answer · 2010-01-04 16:41:23Z

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

edited Jan 4, 2010 at 16:41

answered Jan 4, 2010 at 14:55

Craig Stuntz

127k12 gold badges257 silver badges275 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

bobwah Over a year ago

Hi Craig, just had a go with this and on the line: "predicate = predicate.Or (p => p.roles.Any(r => r.Id == role.Id));" I am not able to access role.Id as role is a System.Data.Objects.DataClasses.EntityCollection object rather than a System.Data.Objects.ObjectQuery<roles>. I guess this is due to how I am selecting within rolesForUser though I'm not sure how to get it to come back as this.

Craig Stuntz Over a year ago

What is the exact type (with parameters) of rolesForUser?

bobwah Over a year ago

(local variable) IQueryable<System.Data.Objects.DataClasses.EntityCollection<roles>> rolesForUser

Craig Stuntz Over a year ago

Oh, I see. You have a list of lists. I took a stab at flattening it in the edit.

bobwah Over a year ago

Thanks Craig this looks like it's cracked it. I'm going to have a go at making this simpler if I can but this has answered the question. I had to change the line "from r in roles.Role" to be "from r in roles" but other than that it worked.

Collectives™ on Stack Overflow

entity framework multiple many to many queries

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related