Entity Framework Does not Import Table with Super Key

I have 4 tables:

but when I create an entity framework model, why is tblRoleInProfile not generated?

I have a Linq TO SQL model that want to convert it to EF and now my开发者_StackOverflow table is not generated. How can I solve that?

UPDATE 1:

You consider we have some profiles and some roles. If we want Profile A has role 1 Insert a record in tblRoleInProperty and if we want Profile B has not Role 2 (If it exists) delete it's record from tblRoleInProperty. I don't want delete a profile. another problem is select new projection. Can any body guide me to write this query in EF:

var prs = from p in dc.tblProfiles
          join rp in dc.tblRoleInProfiles
              on p.ProfileId equals rp.ProfileId
          join r in dc.tblRoles
              on rp.RoleId equals r.RoleId
          select new
          {
              ProfileName = p.ProfileName,
              ProfileId = p.ProfileId,
              RoleName = r.RoleName,
              RoleId = r.RoleId
          };

Thanks

This is how EF works. EF is ORM tool - it tries to hide persistance details and junction table in many-to-many relation is exactly that detail you don't want to see in your object model.

You can rewrite your query simply to:

var prs = from p in dc.tblProfiles
          from r in p.tblRoles
          select new
              {
                  ProfileName = p.ProfileName,
                  ProfileId = p.ProfileId,
                  RoleName = r.RoleName,
                  RoleId = r.RoleId
              };

Updating and deleting relations also works through navigation properties.

Inserting role to profile:

// Dummy objects so you do not need to load them from DB first. 
// These objects must exist in database
var p = new Profile { ProfileId = ... };
var r = new Role { RoleId = ... };

context.tblProfiles.Attach(p);
context.tblRoles.Attach(r);

p.tblRoles.Add(r);

context.SaveChanges();

Deleting role from profile:

// Dummy objects so you do not need to load them from DB first. 
// These objects must exist in database
var p = new Profile { ProfileId = ... };
var r = new Role { RoleId = ... };

p.tblRoles.Add(r);

context.tblProfiles.Attach(p);
context.tblRoles.Attach(r);

p.tblRoles.Remove(r);
// another approach: 
// context.ObjectStateManager.ChangeRelationshipState(p, r, x => x.tblRoles, EntityState.Deleted);

context.SaveChanges();

but when I create an entity framework model Why tblRoleInProfile not generated?

Entity Framework correctly identified the table representing a pure many-to-many relationship between tblProfile and tblRole. This relationship is now expressed through the navigation properties in those two tables. When you access the navigation property, EF will do a join for you internally to return the right related entities to you - in the end this will lead to much cleaner queries since you don't need to express the join explicitly anymore.

As for your example I would reconsider the tbl prefix on your tables / entities - It really hurts readability.

You can change your table to hold an extra ID column instead of the superkey. I know it's not neccessary, but that way EF will definitely import it.

In general, you shouldn't do anything. Your entities are Role and Profile. You'd want to use the relationships created between the entities.

var db = new Entities(); //whatever your context name is
var r = new Role{RoleName="Rtest"};
var p = new Profile {ProfileName = "PTest"};
p.Roles.Add(r);
db.Profiles.AddObject(p);
db.SaveChanges();

EF will take care of the rest. I know you have work invested in L2S, but you may find your life easier if you follow the EF happy path and makes some changes instead of forcing EF to look like LINQ to SQL.

You can query like this:

var qu = from r in dc.tblRoles 
         where r.tblProfiles.Any(p=> p.ProfileId == 42)
         select r;

foreach (var r in qu) {
    Console.WriteLine(r.RoleName)
    foreach (var p in r.tblProfiles) {
       Console.WriteLine(p.ProfileName)
   }
 }

My Solution was to insert a column to the relationship table:

tblRoleInProfile (
    ProfileId int not null,
    RoleId int not null,
    UpdateDate DateTime not null
)...

dont actually needed the column in my case, but it serves the purpose