entity framework update many to many relationship: virtual or not

by

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();
}

More Related Contents:

  1. summing values of a column based on id [closed]
  2. There is already an open DataReader associated with this Command which must be closed first
  3. MSSQL Error ‘The underlying provider failed on Open’
  4. Why does the EF 6 tutorial use asynchronous calls?
  5. Method cannot be translated into a store expression
  6. Implementing Zero Or One to Zero Or One relationship in EF Code first by Fluent API
  7. How can I log the generated SQL from DbContext.SaveChanges() in my Program? [duplicate]
  8. LINQ to Entities only supports casting EDM primitive or enumeration types with IEntity interface
  9. One to one optional relationship using Entity Framework Fluent API
  10. How to compare only Date without Time in DateTime types in Linq to SQL with Entity Framework?
  11. Implementing Audit Log / Change History with MVC & Entity Framework
  12. Filter all queries (trying to achieve soft delete)
  13. Entity Framework – Cannot convert lambda expression to type ‘string’ because it is not a delegate type
  14. Internal .NET Framework Data Provider error 1025
  15. Missing ProviderName when debugging AzureFunction as well as deploying azure function
  16. Can EF automatically delete data that is orphaned, where the parent is not deleted?
  17. How to filter data using Entity Framework in a way that DataGridView be editable and context track changes?
  18. get date part only from datetime value using entity framework
  19. Filtering include items in LINQ and Entity Framework [duplicate]
  20. ASP.NET Identity – Multiple object sets per type are not supported
  21. Entity Framework Code Only error: the model backing the context has changed since the database was created
  22. Getting all changes made to an object in the Entity Framework
  23. How are people unit testing with Entity Framework 6, should you bother?
  24. Update records using LINQ
  25. Duplicate key exception from Entity Framework?
  26. How do I specify that a property should generate a TEXT column rather than an nvarchar(4000)
  27. EF models. Navigation properties can only participate in a single relationship
  28. Seed Entities AND Users, Roles?
  29. Why is EF returning a proxy class instead of the actual entity?
  30. System.Data.Entity.Core.EntityCommandExecutionException Occurred in MVC app Using EF

Leave a Comment