c# - Advanced linq expression with multiple joins -

i have follwing ef entities :

candidate

1. user (user)
2. candidateid (guid)
3. candidatenumber (integer)

user

1. userid (guid)
2. username (guid)
3. roles (set)
4. candidates (set)

role

1. roleid (guid)
2. rolename (string)
3. users (set)

as see each candidate has userid - foreign key of creator of specific candidate. need retrieve candidates relevent creator , other users in same roles current user.

for example, current user - foo, , foo in role my_role. user boo in same role my_role. need linq query (c#) retrieve candidates created foo , boo , filtered current user foo.

in tsql - simple do. in linq...it's more complicated me.

here sql:

select candidates.* usersinroles      inner join users on usersinroles.userid = users.userid         inner join candidates on users.userid = candidates.userid      inner join roles on usersinroles.roleid = roles.roleid      ( roles.roleid in                    ( select usersinroles.roleid usersinroles                       usersinroles.userid = 'c6b9f0ef-7f23-4bb7-a7db-da614b389b13'                    )                 )  

thank you.

to start think want this.

var usernames = new [] {"foo", "boo"};  var candidates = user.where(p => usernames.contains(p.username)).select(p => p.candidates);