LINQ query help - many-to-many related
In my database, I have a user table and a workgroup table, and a many-to-many relationship. A user can belong to one or more workgroups. I am using entity framework for my ORM (EF 4.1 Code First).
User Table has users:
1,2,3,4,5,6,7,8,9,10
Workgroup table has workgroups:
A,B,C, D
WorkgroupUser table has entries
A1, A2, A3, A4, A5
B1, B3, B5, B7, B9
C2, C4, C6, C8, C10
D1, D2开发者_高级运维, D3, D9, D10
What I would like to do is: Given user 4, it belongs to workgroups A,C and has common users
1,2,3,4,5 (from workgroup A) and
2,4,6,8,10 from workgroup C
and the distinct set of users in common is 1,2,3,4,5,6,8,10
How do I write a LINQ statement (preferably in fluent API) for this?
Thank you,
Here's the general idea (since I don't know the properties of User and WorkGroup entity)
var result = context.users.Where(u => u.ID == 4)
.SelectMany(u => u.WorkGroups.SelectMany(wg => wg.Users))
.Distinct()
精彩评论