Entity Framework: Many To Many Inserts Duplicates

Question

I'm currently learning to work with Entity Framework by using it in a new MVC application. I had some struggles setting up a Many-To-Many relation between 2 tables, but got it working for showing data. However, when updating an entity, EF inserts duplicate records for the linked table.

My setup is as follows:

With BusinessUnit presenting an entity that groups WindowsLogins and WindowsGroups together for usage through the BusinessUnit. BusinessUnitWindowsLogin and BusinessUnitWindowsGroup serve as junction tables for the Many-To-Many relationship.

The entities are defined in C# as follows:

BusinessUnit

public class BusinessUnit
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsPersonal { get; set; }
    public virtual IList<WindowsGroup> WindowsGroups { get; set; }
    public virtual IList<WindowsLogin> WindowsLogins { get; set; }
}

WindowsGroup (WindowsLogin being similar)

public class WindowsGroup
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Domain { get; set; }
    public string Name { get; set; }
    public ICollection<BusinessUnit> BusinessUnits { get; set; }
}

I wrote this inside the OnModelCreating method of my DbContext to register the junction tables and foreign keys:

    modelBuilder.Entity<BusinessUnit>()
        .HasMany(businessUnit => businessUnit.WindowsGroups)
        .WithMany(windowsGroup => windowsGroup.BusinessUnits)
        .Map(mc =>
        {
            mc.MapLeftKey("BusinessUnitId");
            mc.MapRightKey("WindowsGroupId");
            mc.ToTable("BusinessUnitWindowsGroup", "Config");
        });

    modelBuilder.Entity<BusinessUnit>()
        .HasMany(businessUnit => businessUnit.WindowsLogins)
        .WithMany(windowsLogin => windowsLogin.BusinessUnits)
        .Map(mc =>
        {
            mc.MapLeftKey("BusinessUnitId");
            mc.MapRightKey("WindowsLoginId");
            mc.ToTable("BusinessUnitWindowsLogin", "Config");
        });

I wrote my update like this:

public void Update(BusinessUnit businessUnit)
        {
            var oldBusinessUnit = _unitOfWork.BusinessUnits.GetById(businessUnit.Id);

            oldBusinessUnit.Name = businessUnit.Name;
            oldBusinessUnit.WindowsGroups.Clear();
            oldBusinessUnit.WindowsLogins.Clear();
            oldBusinessUnit.WindowsGroups = businessUnit.WindowsGroups;
            oldBusinessUnit.WindowsLogins = businessUnit.WindowsLogins;

            _unitOfWork.Complete();
        }

I had to clear both lists of WindowsGroups and WindowsLogins to correctly update the junction table, which now works. But as soon as I assign the new list of WindowsGroups or WindowsLogins, duplicate WindowsGroups or WindowsLogins are inserted by Entity Framework. The junction table is updated with the new Id's, so it looks correct in the application, but it's wrong in the database.

I'm open for any suggestions and feedback. Thank you in advance!

J. Michiels · Accepted Answer · 2018-03-13T14:08:24.967

Reading a bit more into the change tracking that Entity Framework does to the entities, I figured out a way to solve my problem. I altered my update statement as follows:

 public void Update(BusinessUnit businessUnit)
    {
        var oldBusinessUnit = _unitOfWork.BusinessUnits.GetById(businessUnit.Id);

        oldBusinessUnit.Name = businessUnit.Name;
        oldBusinessUnit.WindowsGroups.Clear();
        oldBusinessUnit.WindowsLogins.Clear();

        if (businessUnit.WindowsGroups != null)
        {
            var windowsGroupIds = businessUnit.WindowsGroups.Select(x => x.Id).ToList();

            foreach (var winGroup in _unitOfWork.WindowsGroups.Find(winGroup => windowsGroupIds.Contains(winGroup.Id)).ToList())
            {
                oldBusinessUnit.WindowsGroups.Add(_unitOfWork.WindowsGroups.GetById(winGroup.Id));
            }
        }

        if (businessUnit.WindowsLogins != null)
        {
            var windowsLoginIds = businessUnit.WindowsLogins.Select(x => x.Id).ToList();

            foreach (var winLogin in _unitOfWork.WindowsLogins.Find(winLogin => windowsLoginIds.Contains(winLogin.Id)).ToList())
            {
                oldBusinessUnit.WindowsLogins.Add(_unitOfWork.WindowsLogins.GetById(winLogin.Id));
            }
        }

        _unitOfWork.Complete();
    }

I'll first check if both list actually contain something. Then I loop over the list that I got back from my view and use the Id's to fetch the entities directly from my context (via UoW). I don't know if it's the best solution, but it works.

Assuming `_unitOfWork` is your `DbContext` instance, this will make a database call per item in each collection, which will be inefficient for large collection sets. You may want to look into modifying the entity state on the `DbContext` class for working in a detached context state: https://stackoverflow.com/questions/30987806/dbset-attachentity-vs-dbcontext-entryentity-state-entitystate-modified — Max, Mar 12 '18 at 13:10
Thanks for you reply Max. I've changed my code a little bit to produce less db calls. I now do one call for WindowsGroups and one for WindowsLogins to fetch all of them at once based on the Id's that I want to link. This already seems better than calling the db for every iteration :) — J. Michiels, Mar 13 '18 at 14:09

Entity Framework: Many To Many Inserts Duplicates

1 Answers1