Many-to-many insert with Entity Framework

Question

I'm two entities in my Data Model, for example User and Role, both having the ID field as primary key. There's a many-to-many relationship between them. In the database there are three tables: Users, Roles and UsersRoles junction table.

I'm trying to add a new user to the Users table:

using(var myContext = new MyContext)
{
   var user = new User() { ... };
   user.Roles.Add(new Role() { ID = 1 });
}

The same role can be already be used by another Users, so, when I try to add a new User with the same Role, I become Primary key violation as EF tries to add a new record to the Roles table.

Is there any way to tell the Entity Framework not to add a new record to the Roles table, when such role already exists, but only update the Users and UserRoles tables? EF version 1.0

Thanks in advance.

Answer 1

If the role already exists in the DB you must EF tell that by attaching the role to the context:

using(var myContext = new MyContext)
{
    var role1 = new Role() { ID = 1 };
    myContext.Roles.Attach(role1);

    var user = new User() { ... };
    user.Roles.Add(role1);

    myContext.Users.AddObject(user);

    myContext.SaveChanges();
}

(I hope that this also works the same way in EF 1.)

Answer 2

Found a working solution, though it is not perfect:

using(var _context = new MyContext())
{
    var user = ... //User we're adding to db 
    var roles = ...//List of roles
    foreach(var role in roles)
    {
        var ro = role;
        var roleAlreadyInDB = _context.Roles.Where(r => r.ID == ro.ID).FirstOrDefault();
        if (roleAlreadyInDB != null)
            user.Roles.Add(roleAlreadyInDB);
        else user.Roles.Add(new Role() { ID = ro.ID });
    }
}

This doesn't violate Primary keys in the db.