views:

562

answers:

4

With the .Net Entity Framework and Linq, I'm having a problem finding the best (i.e. easiest to read/understand) way to implement a search for whether or not an entity collection contains any of several possible values.

Consider a basic membership/roles implementation, where User has a Roles collection.

What would be the "best" way to say "does this user have any of the following roles: role1, role2, or role3?"

I can do it with 1 role to check, as in:

if myUser.Roles.Contains(role1) { // do something }

Is there a simple way to add more roles to this check?

+2  A: 

If the list of roles is known at compile-time, then you can do something like this:

if (myUser.Roles.Count(r => r.Id == role1.Id || r.Id == role2.Id) > 0)
{ 
    // do something 
}

If you want to check against a dynamically-built list of roles, it gets trickier. Let me know if this is what you need.

EDITED changed Any() to Count() > 0 -- I had misremembered this limitation in L2E vs. L2SQL.

Ben M
.Any() doesn't seem to be valid in Linq to Entities.... but this looks like what I'm looking to accomplish....
Scott Mayfield
+2  A: 

EDIT: I just ran some tests.. interesting that the perf is pretty identical for IN vs. OR, so besides the SQL looking ugly, they perform pretty much the same. I've edited my response to reflect this.

"IN" style queries are not inherently support by EF (yet) I believe what you are trying to accomplish has been covered here

You can obtain matching entities with something like this:

        var roleNamesToMatch = {"Admin","Manager","Associate"};
        var expression = BuildOrExpression<Role, name>(r => r.Name, roleNamesToMatch);
        var matchingRoles = context.RoleSet.Where(expression);

Based on the Expression Tree, EF would create SQL which looks something like this:

select r.ID,r.Name from t_Role where r.Name = 'Admin' OR r.Name = 'Manager' 
OR r.Name = 'Associate'

instead of what one would normally expect

select r.ID,r.Name from t_Role where r.Name in ('Admin','Manager','Associate')
Abhijeet Patel
There is no SQL performance difference between IN(...) and a series of OR statements. In terms of query planning, they are the same.
Ben M
Yup,you're right... I edited my response to reflect this.
Abhijeet Patel
A: 
var checkForRoles = new Role[] { Role1, Role2, Role3 };

if (myUser.Roles.Any(r => search.Contains(checkForRoles))) {
   //the user is in one of the roles. 
}
Nick Riggs
The entity framework doesn't support Contains(), and at any rate Contains() doesn't take an array or an IEnumerable as its parameter--so I'm not sure what this is supposed to do.
Ben M
You are correct. I wasn't aware that EF didn't support Contains. I was looking for a way to have a dynamic list to test against. I was doing some testing this morning, EF doesn't seem to work with Intersect either - I've become spoiled by L2S.
Nick Riggs
The only way to achieve a Contains style behavior in EF is to use an ExpressionTree
Abhijeet Patel
A: 

Using Count() will do more work than necessary. You can combine the role ID checks with the Any() method to do an existence check:

if(myUser.Roles.Any(role => role.Id == role1.Id || role.Id == role2.Id)
{
    // ...
}
Bryan Watts