entity framework update many to many relationship: virtual or not

Question

I've been using EF4 (not code-first) since a year, so I'm not really an expert with it. I've a doubt in using many-to-many relationship regarding save n update.

I read somewhere on stackoverflow (i can't find the url anymore) that one solution - to update an existing many-to-many relation - is to not declare "virtual" property; but, if i do this way, the engine can't load dataas with easy loading.

Can you pls explain me the reason? Otherwire, could you please help me in finding some cool docs on this theme?

thx

Answer 1

You can update a many-to-many relationship this way (as an example which gives user 3 the role 5):

using (var context = new MyObjectContext())
{
    var user = context.Users.Single(u => u.UserId == 3);
    var role = context.Roles.Single(r => r.RoleId == 5);

    user.Roles.Add(role);

    context.SaveChanges();
}

If the User.Roles collection is declared as virtual the line user.Roles.Add(role); will indeed trigger lazy loading which means that all roles for the user are loaded first from the database before you add the new role.

This is in fact disturbing because you don't need to load the whole Roles collection to add a new role to the user.

But this doesn't mean that you have to remove the virtual keyword and abandon lazy loading altogether. You can just turn off lazy loading in this specific situation:

using (var context = new MyObjectContext())
{
    context.ContextOptions.LazyLoadingEnabled = false;

    var user = context.Users.Single(u => u.UserId == 3);
    var role = context.Roles.Single(r => r.RoleId == 5);

    user.Roles = new List<Role>(); // necessary, if you are using POCOs
    user.Roles.Add(role);

    context.SaveChanges();
}

Edit

If you want to update the whole roles collection of a user I would prefer to load the original roles with eager loading ( = Include). You need this list anyway to possibly remove some roles, so you don't need to wait until lazy loading fetches them from the database:

var newRolsIds = new List<int> { 1, 2, 5 };
using (var context = new MyObjectContext())
{
    var user = context.Users.Include("Roles")
        .Single(u => u.UserId == 3);
    // loads user with roles, for example role 3 and 5

    var newRoles = context.Roles
        .Where(r => newRolsIds.Contains(r.RoleId))
        .ToList();

    user.Roles.Clear();
    foreach (var newRole in newRoles)
        user.Roles.Add(newRole);

    context.SaveChanges();
}

Instead of loading the new roles from the database you can also attach them since you know in the example the key property value. You can also remove exactly the missing roles instead of clearing the whole collection and instead of re-adding the exisiting roles:

var newRolsIds = new List<int> { 1, 2, 5 };
using (var context = new MyObjectContext())
{
    var user = context.Users.Include("Roles")
        .Single(u => u.UserId == 3);
    // loads user with roles, for example role 3 and 5

    foreach (var role in user.Roles.ToList())
    {
        // Remove the roles which are not in the list of new roles
        if (!newRoleIds.Contains(role.RoleId))
            user.Roles.Remove(role);
        // Removes role 3 in the example
    }

    foreach (var newRoleId in newRoleIds)
    {
        // Add the roles which are not in the list of user's roles
        if (!user.Roles.Any(r => r.RoleId == newRoleId))
        {
            var newRole = new Role { RoleId = newRoleId };
            context.Roles.Attach(newRole);
            user.Roles.Add(newRole);
        }
        // Adds roles 1 and 2 in the example
    }
    // The roles which the user was already in (role 5 in the example)
    // have neither been removed nor added.

    context.SaveChanges();
}

Answer 2

Slaumas answer is really good but I would like to add how you can insert a many to many relationship without loading objects from database first. If you know the Ids to connect that extra database call is redundant. The key is to use Attach().

More info about Attach:

https://stackoverflow.com/a/3920217/3850405

public class ConnectBToADto
{
    public Guid AId { get; set; }
    public Guid BId { get; set; }
}

public void ConnectBToA(ConnectBToADto dto)
{
    var b = new B() { Id = dto.BId };
    Context.B.Attach(b);

    //Add a new A if the relation does not exist. Redundant if you now that both AId and BId exists     
    var a = Context.A.SingleOrDefault(x => x.Id == dto.AId);
    if(a == null)
    {
        a = new A() { Id = dto.AId };
        Context.A.Add(a);
    }

    b.As.Add(a);
}

Answer 3

I am using db-first approach and automapper to map between model and entity (MVC 5) and also using eager loading.

In my scenario, there are equipments and there can be multiple users as equipment operators:

    public void Create()
    {
        using (var context = new INOBASEEntities())
        {
            // first i need to map model 'came from the view' to entity 
           var _ent = (Equipment)Mapper.Map(this, typeof(EquipmentModel), typeof(Equipment));

            context.Entry(_ent).State = System.Data.Entity.EntityState.Added;


            // I use multiselect list on the view for operators, so i have just ids of users, i get the operator entity from user table and add them to equipment entity
            foreach(var id in OperatorIds)
            {
                AspNetUsersExtended _usr = context.AspNetUsersExtended.Where(u => u.Id == id).FirstOrDefault();
                // this is operator collection
                _ent.AspNetUsersExtended2.Add(_usr);
            }

            context.SaveChanges();
            Id = _ent.Id;
        }
    }


    public void Update()
    {
        using (var context = new INOBASEEntities())
        {
            var _ent = (Equipment)Mapper.Map(this, typeof(EquipmentModel), typeof(Equipment));

            context.Entry(_ent).State = System.Data.Entity.EntityState.Modified;


            var parent = context.Equipment
                        .Include(x => x.AspNetUsersExtended2)//include operators
                        .Where(x => x.Id == Id).FirstOrDefault();
            parent.AspNetUsersExtended2.Clear(); // get the parent and clear child collection

            foreach (var id in OperatorIds)
            {
                AspNetUsersExtended _usr = context.AspNetUsersExtended.Where(u => u.Id == id).FirstOrDefault();
                parent.AspNetUsersExtended2.Add(_usr);
            }

            // this line add operator list to parent entity, and also update equipment entity 
            context.SaveChanges();
        }
    }