Entity Framework repository pattern create or update many to many

Question

I know there have been several topics about this, but I'm having difficulties with inserting and updating many to many relationships with Entity Framework 6, wrapped with a repository layer around it.

Removing and adding records from a navigation property (being a collection) doesn't result in any changes to the database (as monitored in the database log interceptor):

Resource newResource = resourceForAppointment.FirstOrDefault(x => x.ResourceId == schedulerEvent.ResourceId);
existingAppointment.Resources.Add(newResource);

Resource oldResource = resourceForAppointment.FirstOrDefault(x => x.ResourceId == schedulerEvent.PreviousResourceId);
existingAppointment.Resources.Remove(oldResource);

await this.Repository.UpdateAsync(existingAppointment);

Reading the data works perfectly, so I doubt it has something to do with the configuration,:

[Table("Appointment")]
public partial class Appointment
{
    public Appointment()
    {        
        Resources = new HashSet<Resource>();
    }
    ...

    public virtual ICollection<Resource> Resources { get; set; }
}

Here's what I have got in the repository's async method:

 public virtual async Task<TEntity> UpdateAsync(TEntity entity)
 {
      this.Context.Set<TEntity>().Attach(entity);
      this.Context.Entry(entity).State = EntityState.Modified;
      await this.SaveChangesAsync();

      return entity;        
 }

Updating simple properties and 1 to 1 navigation properties is not a problem, it's just the 1 to many or many to many relationships that fail.

For the moment as a workaround, I'm using the following code and I absolutely need to get rid of this piece of bad coding:

   await this.ResourcesRepository.ExecuteSqlAsync($"DELETE FROM AppointmentResource WHERE AppointmentId = {existingAppointment.AppointmentID} AND ResourceID = {schedulerEvent.PreviousResourceId}");
   await this.ResourcesRepository.ExecuteSqlAsync($"INSERT INTO AppointmentResource VALUES({existingAppointment.AppointmentID},{schedulerEvent.ResourceId}) ");

Other noteworthy remarks here include that I use Unity MVC as the bootstrapper for my repository, hereby using the PerRequestLifeTimeManager. This DbContext is then injected into a unit of work class which creates the repository with predefined DbContext. So there's only 1 active DbContext present during the lifetime of a request.

Does anybody have an idea how to tackle this issue?

UPDATE:

I wasn't entirely accurate when I said inserting or updating doesn't work. When creating a new Appointment, I'm able to add a new record to the Resources collection, as you can see from this code excerpt:

// Map scheduler event to appointment
 Appointment newAppointment = Mapper.Map<Appointment>(schedulerEvent);

// Lookup resource by ID and add to new appointment      
Resource resourceForAppointment = await this.ResourcesRepository.FindOneAsync(x => x.ResourceId == schedulerEvent.ResourceId);
newAppointment.Resources.Add(resourceForAppointment);

 // Save to database
 Appointment freshAppointment = await this.Repository.CreateAsync(newAppointment);


public virtual async Task<TEntity> CreateAsync(TEntity entity)
{
   this.Context.Entry(entity).State = EntityState.Added;
   TEntity createdItem = Context.Set<TEntity>().Add(entity);
   await this.SaveChangesAsync();

   return createdItem;
}

I can conclude from this that the repository pattern doesn't necessarily block the one-to-many or many-to-many relationships, but that I'm missing something else. Hope this makes more sense.

Answer 1

I have provided a simple solution for this: I created a new class that combined the IDs of the many to many relationship. Together with some fluent api work and job was done.