Entity Framework updating entity with lookup table

Question

So, I have built a repository/service application which I have been using successfully for a while. I have started a new project and in this project I have a lookup table attached to one of my models/entities. The model looks like this:

public class Team
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Sport { get; set; }

    public IList<Colour> Colours { get; set; }
}

I also have a binding ViewModel which looks like this:

public class TeamBindingViewModel
{
    public int Id { get; set; }
    [Required] public string Name { get; set; }
    [Required] public string Sport { get; set; }

    public IList<ColourBindingViewModel> Colours { get; set; }
}

public class ColourBindingViewModel
{
    public int Id { get; set; }
    [Required] public string Name { get; set; }
    [Required] public string Hex { get; set; }
}

In my dbContext class I have set up this:

public class DatabaseContext : DbContext
{

    // Define our tables
    public DbSet<User> Users { get; set; }
    public DbSet<Role> Roles { get; set; }

    public DbSet<Colour> Colours { get; set; }
    public DbSet<Team> Teams { get; set; }

    /// <summary>
    /// static constructor (only gets called once)
    /// </summary>
    static DatabaseContext()
    {

        // Create the database and insert our records
        //Database.SetInitializer<DatabaseContext>(new DatabaseInitializer());
    }

    /// <summary>
    /// Default constructor
    /// </summary>
    public DatabaseContext()
        : base("DefaultConnection")
    {

        // Disable Lazy Loading
        base.Configuration.LazyLoadingEnabled = false;
    }

    /// <summary>
    /// Overrides the inherited OnModelCreated method.
    /// </summary>
    /// <param name="modelBuilder">The DbModelBuilder</param>
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        // Remove Cascading Delete
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

        // Map the TeamColours table
        modelBuilder.Entity<Team>()
            .HasMany(m => m.Colours)
            .WithMany()
            .Map(m =>
            {
                m.MapLeftKey("TeamId");
                m.MapRightKey("ColourId");
                m.ToTable("TeamColours");
            });
    }
}

(nb: I have stripped this down to make it easier to read)

So, the problem I have is when I try to do an update, if I try to add colours, I get an error. Here is a look at my base repository class:

public class Repository<T> : IDisposable, IRepository<T> where T : class
{
    private readonly DbContext context;
    private readonly DbSet<T> dbEntitySet;

    public Repository(DbContext context)
    {
        if (context == null)
            throw new ArgumentNullException("context");

        this.context = context;
        this.dbEntitySet = context.Set<T>();
    }

    public IQueryable<T> GetAll(params string[] includes)
    {
        IQueryable<T> query = this.dbEntitySet;
        foreach (var include in includes)
            query = query.Include(include);

        return query;
    }

    public void Create(T model)
    {
        this.dbEntitySet.Add(model);
    }

    public void Update(T model)
    {
        this.context.Entry<T>(model).State = EntityState.Modified;
    }

    public void Remove(T model)
    {
        this.context.Entry<T>(model).State = EntityState.Deleted;
    }

    public void Dispose()
    {
        this.context.Dispose();
    }
}

I then have a base service class like this:

public class Service<T> where T : class
{
    private readonly IRepository<T> repository;

    protected IRepository<T> Repository
    {
        get { return this.repository; }
    }

    public Service(IUnitOfWork unitOfWork)
    {
        if (unitOfWork == null)
            throw new ArgumentNullException("unitOfWork");

        this.repository = unitOfWork.GetRepository<T>();
    }
}

and finally here is my TeamService class:

/// <summary>
/// Team service handles all team related functions
/// </summary>
public class TeamService : Service<Team>
{
    /// <summary>
    /// Default constructor
    /// </summary>
    /// <param name="unitOfWork"></param>
    public TeamService(IUnitOfWork unitOfWork) 
        : base(unitOfWork)
    {

    }

    /// <summary>
    /// Get all teams asynchronously
    /// </summary>
    /// <param name="includes">Eager loading includes</param>
    /// <returns>A list of colours</returns>
    public async Task<IList<Team>> GetAllAsync(params string[] includes)
    {
        return await this.Repository.GetAll(includes).ToListAsync();
    }

    /// <summary>
    /// Get a team by id
    /// </summary>
    /// <param name="id">The id of the colour</param>
    /// <param name="includes">Eager loading includes</param>
    /// <returns>A colour</returns>
    public async Task<Team> GetAsync(int id, params string[] includes)
    {
        var models = await this.GetAllAsync(includes);

        return models.Where(model => model.Id == id).SingleOrDefault();
    }

    /// <summary>
    /// Create a team
    /// </summary>
    /// <param name="model">The team model</param>
    public void Create(Team model)
    {

        // Create a team
        this.Repository.Create(model);
    }

    /// <summary>
    /// Update a team
    /// </summary>
    /// <param name="model">The team model</param>
    public void Update(Team model)
    {

        // Update a team
        this.Repository.Update(model);
    }

    /// <summary>
    /// Delete a team
    /// </summary>
    /// <param name="model">The team model</param>
    public void Remove(Team model)
    {

        // Remove a team
        this.Repository.Remove(model);
    }
}

I know there is a lot of code here, but I need to give you my entire process if anyone can help me :) So, If I create an update method in my controller like this:

private async Task<IHttpActionResult> Save(TeamBindingViewModel model)
{

    // If our model is invalid, return the errors
    if (!ModelState.IsValid)
        return BadRequest(ModelState);

    // Create a list of colours
    var colours = new List<Colour>();

    // For each colour in our model, add to our list
    foreach (var colour in model.Colours)
        colours.Add(new Colour()
        {
            Id = colour.Id,
            Name = colour.Name,
            Hex = colour.Hex
        });

    // If there is an id
    if (model.Id > 0)
    {

        // Update our team
            await this.Update(model, colours);
    }
    else
    {

        // Create our team
        this.Create(model, colours);
    }

    // Save the database changes
    await this.unitOfWork.SaveChangesAsync();

    // Return Ok
    return Ok(model);
}

private void Create(TeamBindingViewModel model, IList<Colour> colours)
{

    // Create our new model
    var team = new Team()
    {
        Id = model.Id,
        Name = model.Name,
        Sport = model.Sport
    };

    // Assign our colours to our team
    team.Colours = colours;

    // Otherwise, create a new team
    this.service.Create(team);
}

private async Task Update(TeamBindingViewModel model, IList<Colour> colours)
{

    // Create our new model
    var team = new Team()
    {
        Id = model.Id,
        Name = model.Name,
        Sport = model.Sport
    };

    // Update the team
    this.service.Update(team);
}

private IList<Colour> GetDifference(IList<Colour> firstList, IList<Colour> secondList)
{

    // Create a new list
    var list = new List<Colour>();

    // Loop through the first list
    foreach (var first in firstList)
    {

        // Create a boolean and set to false
        var found = false;

        // Loop through the second list
        foreach (var second in secondList)
        {

            // If the first item id is the same as the second item id
            if (first.Id == second.Id)
            {

                // Mark it has being found
                found = true;
            }
        }

        // After we have looped through the second list, if we haven't found a match
        if (!found)
        {

            // Add the item to our list
            list.Add(first);
        }
    }

    // Return our differences
    return list;
}

The update will process and everything works. But if I change my update method to this:

private async Task Update(TeamBindingViewModel model, IList<Colour> colours)
{

    // Create our new model
    var team = new Team()
    {
        Id = model.Id,
        Name = model.Name,
        Sport = model.Sport
    };

    // Get our current model
    var current = await this.service.GetAsync(model.Id, "Colours");
    var currentColours = current.Colours;

    // Assign our original colours to our team
    team.Colours = currentColours;

    // Get our colours to remove and add
    var coloursToRemove = GetDifference(currentColours, colours);
    var coloursToAdd = GetDifference(colours, currentColours);

    // Loop through our colours to remove and remove them
    if (coloursToRemove.Count > 0)
        foreach (var colour in coloursToRemove)
            team.Colours.Remove(colour);

    // Loop through the colours to add and add them
    if (coloursToAdd.Count > 0)
        foreach (var colour in coloursToAdd)
            team.Colours.Add(colour);

    // Update the team
    this.service.Update(team);
}

I get this error:

"exceptionMessage": "Attaching an entity of type 'Models.Team' failed because another entity of the same type already has the same primary key value. This can happen when using the 'Attach' method or setting the state of an entity to 'Unchanged' or 'Modified' if any entities in the graph have conflicting key values. This may be because some entities are new and have not yet received database-generated key values. In this case use the 'Add' method or the 'Added' entity state to track the graph and then set the state of non-new entities to 'Unchanged' or 'Modified' as appropriate."

I am not sure why I get this error, but I assume it has something to do with trying to add / remove colours from the lookup table. Can anyone provide me with a solution to this problem?

Answer 1

your prolblem occurs because you are trying to update "team" while you have the "current" variable holdes the entity in the same scope try this:

private async Task Update(TeamBindingViewModel model, IList<Colour> colours)
{

    // Create our new model
    //var team = new Team()
    //{
    //    Id = model.Id,
    //    Name = model.Name,
    //    Sport = model.Sport
    //};

    // Get our current model
    var current = await this.service.GetAsync(model.Id, "Colours");

    current.Name = model.Name;
    current.Sport =  model.Sport;

    var currentColours = current.Colours;

    // Assign our original colours to our team
    //team.Colours = currentColours;

    // Get our colours to remove and add
    var coloursToRemove = GetDifference(currentColours, colours);
    var coloursToAdd = GetDifference(colours, currentColours);

    // Loop through our colours to remove and remove them
    if (coloursToRemove.Count > 0)
        foreach (var colour in coloursToRemove)
            current.Colours.Remove(colour);

    // Loop through the colours to add and add them
    if (coloursToAdd.Count > 0)
        foreach (var colour in coloursToAdd)
            current.Colours.Add(colour);

    // Update the team
    this.service.Update(current);
}