Two tables with composite key are joined in Entity Framework

Question

I have four tables in SQL Server, the image the tables and relations.

The problem is when I use Entity Framework the GroupAdmins and GroupMembers tables is joined into one table, the image show the situation:

I want to get the four tables in Entity Framework. What should I do ?

Update

The real problem is that I can't add or delete items for GroupAdmins and GroupMembers tables in the same way as I do with other tables. For example, if I want to add item to AspNetUsers, I use this :

using (var db=new DatabaseContext())
      {
        AspNetUsers user = new AspNetUsers{Id=1,Email="[email protected]"};
        db.AspNetUsers.add(user);
        db.SaveChanges();
      }

While I can't do the same for GroupAdmins and GroupMembers, because GroupAdmins and GroupMembers are not exist.

Answer 1

If you have a many-to-many relationship between two tables, in the relational database, you need a "link" table to connect the two base tables.

EF handles this automatically for you - it doesn't explicitly surface that link table - it handles those inserts and deletes internally, behind the scenes. You don't need to do anything special at all! Use this default behavior - don't fight it! It works to your advantage!

What you do is basically add new entries in one of the tables into the collection-based navigation property, and EF will automatically insert the necessary entries into the relevant link table.

So for instance, you could do something like this:

using (var db = new DatabaseContext())
{
    AspNetUsers user = new AspNetUsers { Id = 1, Email = "[email protected]" };
    db.AspNetUsers.add(user);

    // get a group to add the new user to
    Groups g = db.Groups.FirstOrDefault(g => g.Name = "SomeGroupName");

    if(g != null)
    {
        // since I don't have your EDMX model here, I don't know if "AspNetUsers" for the "Groups"
        // refers to the "GroupMembers" or the "GroupAdmins" connection - *YOU* can see this when
        // you look at the EDMX model designer!
        g.AspNetUsers.Add(user);
    }

    // now when you save, you'll get a new user in "AspNetUsers", AND ALSO: you'll get an entry
    // in either "GropuMembers" or "GroupAdmins" that contains the two ID's - AspNetUsers.Id 
    // and the Id for the group with the name of "SomeGroupName"
    db.SaveChanges();

}