Entity Framework - Linq to query where value in a list

Question

I'm using Entity Framework on .NET 3.5 and I can't for the life of me figure out how to write some Linq to traverse the following design:

Basically I am trying to figure out if a user has permission (EntityAction) for a specific EntityType. Now Users and Roles are maintained in Active Directory - but the system can do a lookup to find which Role/Groups a user belongs.

Let's assume I have the following data:

EntityType

EntityTypeId: 1

Name: Entity One

User

UserId: 1

AccountName: Andez

Role

RoleId: 1

AccountName: MyRole

EntityAction

EntityActionId: 1

Name: Just do something

RoleEntityActionAssociation

(association between Role and EntityAction)

EntityActionId: 1

RoleId: 1

I am storing group names for the user (from Active Directory) in a List:

List<string> groupNames = new List<string>();

Question

However I am trying to piece together some Linq to find out whether a User (or one of the Roles he is assigned in List groupNames) is associated with a particular EntityAction given an EntityType.

// get reference to the user
User user = context.Users.Where(x => x.AccountName == "Andez").FirstOrDefault();

// get reference to the entity type we want to query
EntityType et = context.EntityTypes.Where(x => x.Name == "Entity One").FirstOrDefault();

// get list of all entity actions for the user
var result = from ea in et.EntityActions
             where ea.EntityActionId == 1 && (ea.Users.Contains(user) || ea.Roles.Count(r => groupNames.Contains(r.AccountName)) > 0)
             select ea;

Of course my query does not work above - it does not return any results (result.ToList().Count == 0)

I need pointers on this please.

Thanks

EF Design

Avoid using FirstOrDefault when you expect one and only one result and an error will ensue if it is not found, use .Single() — gareththegeek
– gareththegeek, Commented May 8, 2013 at 13:40

Vyacheslav Volkov · Accepted Answer · 2013-05-08 14:13:20Z

4

Maybe you need to check User by Id, replace ea.Users.Contains(user) to ea.Users.Any(us => us.UserId == user.UserId):

var result = from ea in et.EntityActions
                     where ea.EntityActionId == 1 && ea.Users.Any(us => us.UserId == user.UserId)
                     select ea;

UPDATE 0

Try this code, it should work:

var result = from ea in et.EntityActions
                     where ea.EntityActionId == 1 &&
                         (ea.Users.Any(us => us.UserId == user.UserId) || ea.Any(r => groupNames.Contains(r.AccountName)))
                     select ea;

edited May 8, 2013 at 14:13

answered May 8, 2013 at 13:45

Vyacheslav Volkov

4,7601 gold badge24 silver badges20 bronze badges

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

6 Comments

Andez Over a year ago

Just tried that. My initial code did work to get the user's EntityActions - I'd just missed some configuration in database doh! But I still cannot figure out the Roles part

Andez Over a year ago

Thanks for that... I am suspecting it is down to my objects not being loaded. Given that I am using .NET 3.5, there is no lazy loading. I broke the query down into different lines of code until I forcefully loaded the Roles for the EntityAction that I know it should use by calling Load. Thing is, I don't know how to ensure that the objects I need will be loaded for queries. where ea.EntityActionId == 1 && ((ea.Users.Any(us => us.UserId == user.UserId) || ea.Roles.Any(r => groupNames.Contains(r.AccountName)))) does work. Any thoughts to load nicely?

Vyacheslav Volkov Over a year ago

Read this article.

Andez Over a year ago

OK thanks for that Linq! ;-) I've now got my queries being loaded and have just purchased LinqPad linqpad.net to help me.

Andez Over a year ago

PS I still cannot get the groupNames.Contains working. Perhaps as I am .NET 3.5 SP1 which is EF v1. I get LINQ to Entities does not recognize the method 'Boolean Contains(System.String)' method, and this method cannot be translated into a store expression.

|

Collectives™ on Stack Overflow

Entity Framework - Linq to query where value in a list

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related