LINQ Query across Associations

Question

I have an Entity Framework that is generated from my database. Within this database is a table called User_Security_Role that is composed of two fields: Employee_ID and User_Name. Both fields act as its primary keys and are also foreign keys. The fields are from two tables Sytem_Role (with Role_ID as its PK) and User_Identity (with User_Name as its PK). When the .edmx file is generated, the User_Security_Role table is not generated as a table but rather as an association set between the two entities: System_Role and User_Identity.

I would like to query the EF with the query:

var usr = from u in _imEntities.User_Security_Role
          where u.Role_ID == 3
          select u.User_Name;

However, the "User_Security_Role" is not showing up as an entity in the entity set and I am not sure how to query it now that it is an Association Set. How can I write a linq query to select data from an association rather than an entity?

Answer 1

It sounds like EF has identified the User_Security_Role table as an unneeded table in a many-many relationship (which it probably is). In this case, you would write your query as one of the following:

var usr = From u in _imEntities.User_Identities
          where u.Roles.Any(r => r.Role_ID == 3)
          select u;

OR

var usr = from r in _imEntities.System_Roles
          where r.Role_ID == 3
          from u in r.UserSecurities
          select u;