Many to Many Relationships not saving

Question

I have two entities with a fairly standard Many to Many relationship that I created in EF 5 Code First. These are Service and ServiceItem. The Service entity contains a collection of ServiceItems and the ServiceItem contains a collection of Services. I can create, change and save data to either of the entities basic properties with no problems. When I try to add a ServiceItem to a Service or a Service to a ServiceItem it seems to work, but nothing is saved. I have verified that all the proper database tables are created, including a ServiceItemService table with the cross keys. The database ServiceItemService table doesn't get any entry when I add the items. There is no error and everything else seems to work perfectly.

I am a bit stumped and could use some help. Below are the classes.

The Service class;

public class Service
{
    //Default constructor
    public Service()
    {
        //Defaults
        IsActive = true;
        ServicePeriod = ServicePeriodType.Monthly;
        ServicePeriodDays = 0;

        ServiceItems = new Collection<ServiceItem>();
    }

    public int ServiceID { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public ICollection<ServiceItem> ServiceItems { get; set; }
    public string TermsOfService { get; set; }
    public ServicePeriodType ServicePeriod { get; set; }
    public int ServicePeriodDays { get; set; }
    public bool IsActive { get; set; }
}

The ServiceItem class;

public class ServiceItem
{
    public ServiceItem()
    {
        IsActive = true;
    }

    public int ServiceItemID { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public ICollection<Service> Services { get; set; }
    public string UserRole { get; set; }
    public bool IsActive { get; set; }

}

This is the Fluent mapping I did while trying to debug this issue. The same problem happened before and after adding this mapping.

    public DbSet<Service> Services { get; set; }
    public DbSet<ServiceItem> ServiceItems { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Service>()
           .HasMany(p => p.ServiceItems)
           .WithMany(r => r.Services)
           .Map(mc =>
           {
               mc.MapLeftKey("ServiceItemID");
               mc.MapRightKey("ServiceID");
               mc.ToTable("ServiceItemService");
           });
    }

Here is the code I use to save the Service item that includes 2-3 ServiceItems in the Service.ServiceItems collection. I have carefully verified that the ServiceItems were in the proper collection.

                db.Entry(dbService).State = EntityState.Modified;
                db.SaveChanges();

The dbService object doesn't seem to get affected in any way. The ServiceItems are still in the proper collection, but no update are made to the ServiceItemService database table. Any advice would be very welcome.

-Thanks

Answer 1

It is expected that nothing happens.

What you want to change or add is a relationship between the entities Service and ServiceItem. But you cannot manipulate relationships by setting the state of an entity to Modified. This only updates scalar and complex properties but no navigation properties (= relationships). (For example setting the state of a Service entity to Modified will mark Service.Title and Service.Description, etc. as modified and ensure that those properties are saved to the database. But it doesn't care about the content of Service.ServiceItems.)

The only exception where you can change a relationship by setting the state to Modified are Foreign Key Associations. These are associations that have foreign key properties exposed in your model entity and they can only occur for one-to-many or one-to-one associations. Many-to-many relationships are always Independent Associations which means they can never have a foreign key property in an entity. (Because the FKs are in the join table, but the join table is not an entity and "hidden" from your model classes.)

There is a way to directly manipulate relationships for a many-to-many association but it requires to go down to the ObjectContext and its RelationshipManager which is - in my opinion - pretty advanced and tricky.

The usual and straight-forward way to add and remove relationship entries to/from a many-to-many association is by just adding items to and removing items from the collections while the entities are attached to the context. EF's change tracking mechanism will recognize the changes you have done and generate the appropriate INSERT, UPDATE and DELETE statements when you call SaveChanges.

The exact procedure depends on if you also want to save Service and/or ServiceItem as new entities or if you only want to add relationships between existing entities. Here are a few examples:

• service should be INSERTed, all serviceItems should be INSERTed and the relationships between the entities should be INSERTed into the join table as well:

  using (var context = new MyContext())
  {
      var service = new Service();
      var serviceItem1 = new ServiceItem();
      var serviceItem2 = new ServiceItem();
      service.ServiceItems.Add(serviceItem1);
      service.ServiceItems.Add(serviceItem2);
  
      context.Services.Add(service);
  
      context.SaveChanges();
  }
  

  Adding the "root" service of the object graph is enough because EF will recognize that all other entities in the graph are not attached to the context and assume that they have to be INSERTed into the database.

• service already exists and should NOT be INSERTed, all serviceItems should be INSERTed and the relationships between the entities should be INSERTed into the join table as well:

  using (var context = new MyContext())
  {
      var service = new Service { ServiceID = 15 };
      context.Services.Attach(service);
  
      var serviceItem1 = new ServiceItem();
      var serviceItem2 = new ServiceItem();
      service.ServiceItems.Add(serviceItem1);
      service.ServiceItems.Add(serviceItem2);
  
      context.SaveChanges();
  }
  

  EF recognizes here (when SaveChanges is called) that service is attached but the other entities are not. No INSERT for service happens but the serviceItem1/2 will be INSERTed together with the relationship entries.

• service already exists and should NOT be INSERTed, all serviceItems already exist and should NOT be INSERTed, but the relationships between the entities should be INSERTed into the join table:

  using (var context = new MyContext())
  {
      var service = new Service { ServiceID = 15 };
      context.Services.Attach(service);
  
      var serviceItem1 = new ServiceItem { ServiceItemID = 23 };
      context.ServiceItems.Attach(serviceItem1);
  
      var serviceItem2 = new ServiceItem { ServiceItemID = 37 };
      context.ServiceItems.Attach(serviceItem2);
  
      service.ServiceItems.Add(serviceItem1);
      service.ServiceItems.Add(serviceItem2);
  
      context.SaveChanges();
  }
  

• For completeness: How to remove relationships between existing entities?

  using (var context = new MyContext())
  {
      var service = context.Services
          .Include(s => s.ServiceItems) // load the existing Items
          .Single(s => s.ServiceID == 15);
  
      var serviceItem1 = service.ServiceItems
          .Single(s => s.ServiceItemID == 23); // query in memory, no DB query
      var serviceItem2 = service.ServiceItems
          .Single(s => s.ServiceItemID == 37); // query in memory, no DB query
  
      service.ServiceItems.Remove(serviceItem1);
      service.ServiceItems.Remove(serviceItem2);
  
      context.SaveChanges();
  }
  

  The two relationship rows in the join table that link service 15 with serviceItem 23 and 37 will be deleted.

Alternativly instead of calling Attach you can load the existing entities from the database. It will work as well:

var service = context.Services.Single(s => s.ServiceID == 15);

And the same for existing ServiceItems.