L2E many to many query
- by 5YrsLaterDBA
I have four tables:
Users PrivilegeGroups rdPrivileges LinkPrivilege
----------- ---------------- --------------- ---------------
userId(pk) privilegeGroupId(pk) privilegeId(pk) privilegeId(pk, fk)
privilegeGroupId(fk) name code privilegeGroupId(pk, fk)
L2E will not create LinkPrivilege entity for me. So we only have Users, PrivilegeGroups and rdPrivileges entities. PrivilegeGroups and rdPrivileges are many to many relationship.
What I need to do is retrieve all code from rdPrivileges table based on a passed in userId. How can I do it?
EDIT
working code:
var acc = from u in db.Users
from pg in db.PrivilegeGroups
from p in pg.rdPrivileges
where u.UserId == userId
&& u.PrivilegeGroups.PrivilegeGroupId == pg.PrivilegeGroupId
select p.Code;
