CODEWITHSUNDEEP
entity-frameworkasp.net-web-apilambdalinq-to-sql
Rob
Rob

Reputation: 3

Filtering on Many to Many relationship

I have 3 related entities with 1 to many relationships. Account 1-* Collection 1-* items

Each of these has a many to many relationship with Users, (with join tables AccountManagers , CollectionManagers, ItemManagers)

I'm trying to get a list of Collections for a user if he's in any of the join tables. I was thinking create 3 expressions and then merge the 3 results at the end and somehow remove duplicates. It seemed like Lambda expressions are the way to go, but I'm still learning them.

I think the middle one is easier , like db.Collections.where(C => C.CollectionManagers.UserID == CurrentUserID)

But how would you gather a list of collections from the users account manager and item manager entries?

Thanks in advance

Upvotes: 0

Views: 1904

Answers (1)

NetMage
NetMage

Reputation: 26917

Using LINQ, the Union operator will return only the unique Collection rows, so assembling each category and combining them should work.

For Items, I thought it would be most efficient to find all Items managed by the current user and then find all collections they belong to:

var iCollections = Items.Where(i => i.ItemManagers.Any(im => im.UserId == CurrentUserID)).SelectMany(i => Collections.Where(c => c.Items.Contains(i)));

It is also possible to do this the other way, e.g. find all Collections that contain an Item managed by the current user:

var iCollections = Collections.Where(c => c.Items.Any(i => i.ItemManagers.Any(im => im.UserId == CurrentUserID)));

For Collections, as you pointed out, you just need to find all collections where the current user manages the collection:

var cCollections = Collections.Where(c => c.CollectionManagers.Any(cm => cm.UserId == CurrentUserID));

For Accounts, we find all accounts managed by the current user and then all collections owned by the account:

var aCollections = Accounts.Where(a => a.AccountManagers.Any(am => am.UserId == CurrentUserID)).SelectMany(a => a.Collections);

Then you can Union the results together:

var CurrentUserCollections = iCollections.Union(cCollections).Union(aCollections);

Upvotes: 1

Related Questions