1

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?

user2030579
  • 41
  • 1
  • 4
  • 14

1 Answers1

0

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;
Jim Wooley
  • 10,169
  • 1
  • 25
  • 43
  • Thank you sooo much. I have been at this for days!!! I used the 2nd option. This solved so many problems. Thanks again... – user2030579 Feb 15 '13 at 19:56
  • Can you tell me how I am able to add data to the User_Security_Role table (the Association Set) such as: _imEntities.AddObject ("User_Security_Role", user1); _imEntities.SaveChanges(); – user2030579 Feb 18 '13 at 20:18
  • You don't add data directly to the middle table. Think about this from an OOP perspective. In that case, you would select a User `currentUser = (_imEntities.Users.First(u => u.UserId = 123)` and then add an existing role to that user. `currentUser.Roles.Add(role)` – Jim Wooley Feb 22 '13 at 16:28
  • Thanks very much. I actually figured out that part already. I am having trouble now removing a role from a selected user. My code is: _imEntities.Detach(role); role.User_Identity.Remove(usr); _imEntities.SaveChanges(); – user2030579 Feb 22 '13 at 19:45
  • Don't use Detach. That removes the object tracking. As a result the .Remove won't track the change. As a semantic alternative, if you want to remove a role from a user, consider `user.Remove(role); _imEntities.SaveChanges();` – Jim Wooley Feb 25 '13 at 14:46