Associations in EF Code First: Part 6 – Many-valued Associations

41 Comments

• Hi
  
  Regarding the ItemCategory relationship. Say you wanted to add the possibility to avoid displaying child categories, only in specific relationships.
  
  In the database you'd have a bit column in the ItemCategory table that could be named DisplayChildCategories.
  
  How do you include this column in the relationship?
  And where would it be mapped, which class?
  

• Great posts. Thank you for sharing.
  

• I have been eagerly waiting for Part 6 for a long time. Thanks for the great post.
  

• Thank you. Very informative, very helpful post.
  

• Hello,
  
  
  Thanks for all your posts, they have helped me to understand a lot about code first!
  
  
  Could you help me understand what is the best way to map something like this to an existing database/tables?
  
  
  Model:
  
  
  public class Item
  {
      public int ItemId { get; set; }
      public string Name { get; set; }
      public virtual IDictionary<int, Attribute> Attributes { get; set; }
  }
  
  
  public class Attribute
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public virtual IDictionary<int, ExpectedValue> ExpectedValues { get; set; }
  }
  
  
  public class ExpectedValue
  {
      public int Id { get; set; }
      public string Name { get; set; }
  }
  
  
  Attribute Table:
  
  
  id int
  Name nvarchar(64)
  Description nvarchar(128)
  
  
  ExpectedValue Table:
  
  
  id int
  Name nvarchar(64)
  Value int
  MonitoredItemAttributeMapID int 
  
  
  MonitoredItemAttributeMap table (Many to Many Mapping table):
  
  
  id int
  MonitoredItemID int
  AttributeID int
  
  
  
  Thank you for taking the time to look at this and any solution is helpful!
  
  
  
  
  Cheers,
  
  
  DJ
  
  

• @Martin H. Normark: Like I described in the post, many-to-many associations cannot have a payload, and if that’s the case then you always have to break it down to two many-to-one association to an intervening class. For example, the following is going to be the object model for the scenario you described:
  
  
  
  
  public class Item
  {
      public int ItemId { get; set; }       
      public virtual ICollection<ItemCategory> ItemCategories { get; set; }
  }
  public class Category
  {
      public int CategoryId { get; set; }       
      public virtual ICollection<ItemCategory> ItemCategories { get; set; }
  }
  
  public class ItemCategory
  {
      public int ItemCategoryId { get; set; }
      public bool DisplayChildCategories { get; set; }
      public int CategoryId { get; set; }
      public int ItemId { get; set; }
  
      public virtual Item Item { get; set; }
      public virtual Category Category { get; set; }
  }
  
  
  Hope this helps.
  
  

• @DJRodriguez: Like I described in the post a many-to-many association cannot have a payload and in your case, even the Id as a primary key on the MonitoredItemAttributeMap table is considered to be a payload and therefore you can’t create a many-to-many association between the Item and Attribute classes. In this case, like I described in the post, you have to break it down to two many-to-one association to an intervening class. I named this intermediate association class as MonitoredItemAttributeMap and created the following object model for your existing database:
  
  
  
  public class Item
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public virtual ICollection<MonitoredItemAttributeMap> ItemAttributes { get; set; }
  }
  
  public class Attribute
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public string Description { get; set; }
  
      public virtual ICollection<MonitoredItemAttributeMap> ItemAttributes { get; set; }
  }
  
  public class ExpectedValue
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public int Value { get; set; }
      public int MonitoredItemAttributeMapID { get; set; }
  
      public MonitoredItemAttributeMap ItemAttribute { get; set; }
  }
  
  public class MonitoredItemAttributeMap
  {
      public int Id { get; set; }
      public int MonitoredItemID { get; set; }
      public int AttributeID { get; set; }
  
      public Item Item { get; set; }
      public Attribute Attribute { get; set; }
  }
  
  public class Context : DbContext
  {
      public DbSet<Item> Items { get; set; }
      public DbSet<Attribute> Attributes { get; set; }
      public DbSet<ExpectedValue> ExpectedValues { get; set; }
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          modelBuilder.Entity<MonitoredItemAttributeMap>()
                      .HasRequired(m => m.Item)
                      .WithMany(i => i.ItemAttributes)
                      .HasForeignKey(m => m.MonitoredItemID);
      }
  }
  
  
  Thanks for your comment and I hope you find this helpful :)
  
  

• Keep up your good work!
  Baba khafan. Baba inkare :)
  

• All very good articles. Thank you!
  

• merci aghaye manafi babate in post
  

• When will other types of collections be supported?
  At least, IEnumerable... sometimes, we don't want a collection to be changed.
  

• Awesome post! To me its the best article on EF :)
  

• Insert doesn't work if i want to insert a Category with Categories children :(
  

• @Alessandro Antonio de Brito: The following object model is all you need:
  
  public class Address
  {
      public int Id { get; set; }
      public string Description { get; set; }
      public virtual int PersonId { get; set; }
  }
  
  
  public class Person
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public virtual ICollection<Address> Addresses { get; set; }
  }
  
  
  public class Context : DbContext
  {
      public DbSet<Person> Persons { get; set; }
      public DbSet<Address> Addresses { get; set; }
  
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          modelBuilder.Entity<Person>().Property(p => p.Id).HasColumnName("ID_PERSON");
          modelBuilder.Entity<Person>().Property(p => p.Name).HasColumnName("DC_PERSON").IsRequired();
          modelBuilder.Entity<Person>().ToTable("TB_PERSON");                      
  
  
          modelBuilder.Entity<Address>().Property(p => p.Id).HasColumnName("ID_ADDRESS");
          modelBuilder.Entity<Address>().Property(p => p.Description).HasColumnName("DC_ADDRESS").IsRequired();
          modelBuilder.Entity<Address>().Property(p => p.PersonId).HasColumnName("ID_PERSON");
      }
  }
  
  
  As you can see above, you don’t really need to configure the association between Person and Address entities as it will be picked up by convention and Code First will automatically configure it for you. Having a foreign key like PersonId on the Address class is not required but is recommended. Having the foreign key along with the navigation properties will create a foreign key association as opposed to an independent association that have been introduced in the first version of EF.
  
  
  
  Note that it is not necessary to define a PersonA property on the Address class. You should do that only if you want to make your association bidirectional and looks like you don’t have such a requirement in your domain model so I set up a unidirectional association between the two entities. Hope this helps.
  

• When did we start changing the associations/configurations from:
  
  modelBuilder.Entity<Item>()
              .HasOptional(i => i.Buyer)
              .WithMany(u => u.BoughtItems)
              .HasForeignKey(i => i.BuyerId);
  
  To:
  
  class ItemConfiguration : EntityTypeConfiguration<Item>
  {
      internal ItemConfiguration()
      {
          this.HasOptional(i => i.Buyer)
              .WithMany(u => u.BoughtItems)
              .HasForeignKey(i => i.BuyerId);
      }
  }
  
  Are these the same thing?
  

• @craigfreeman74: Yes, they are exactly the same thing and have the same effect. Having the fluent API code in a separate class that inherits from EntityTypeConfiguration<T> allows a cleaner way of writing fluent API code and is recommended especially when you have too many entities in your domain that you want to write fluent API code for.
  

• Excellent Series!!! Thank you.
  Will you be doing anything in relation to sprocs and views?
  
  Thanks again.
  Mark
  

• Mark Phillips: You’re very welcome. I don’t really have any plans to write about stored procedures or views but if you have any questions, feel free to post them here. I might be able to help :)
  

• Hi
  How the data is populated into the the mapping table. eg categoryItem
  

• @Farid: The join table (e.g. ItemCategory) is populated when you define new relationships between the related objects (e.g. Item and Category) in your application. For example, the following code will result in a submission of an INSERT INTO command by EF to add a new record to the ItemCategory table:
  
  
  
  Category category = new Category();
  Item item = new Item();
  
  category.Items.Add(item);
                                     
  context.Categories.Add(category);
  context.SaveChanges();
  
  
  And the following code will result in a DELETE command to remove the inserted record:
  
  
  
  category.Items.Remove(item);
  context.SaveChanges();
  
  

• @Jonpa: The code you showed should (and will) work for a many-many association like the one I introduced in this post. Can you please post your object model along with the fluent API codes involving Entity and Entity2? I particularly like to see the Entity2 class definition.
  

• The relationship between Entity and Entity2 is an one to many relationship. I think the problem lies in that my foreign key in Entity2 is non nullable (as I want to be).
  
  Well here's the code (note that in my production code, the entities have better name :) )
  
  public class Entity : EntityBase
  {
    private ICollection<Entity2> _entities;
    public string Name { get; set; }
    public virtual ICollection<Entity2> Entities
    {
        get { return (_entities ?? (_entities = new HashSet<Entity2>())); }
    }
  }
  public class Entity2 : EntityBase
  {
     public string Name { get; set;}       
  }
  public abstract class EntityBase
  {
     public Guid Id { get; set; }
     public DateTime Created { get; set; }
     public byte[] Version { get; set; }
     public EntityBase()
     {
         Id = Guid.NewGuid();
         Created = DateTime.Now;
     }
  }
  And my fluent API code:
  public class EntityBaseConfiguration<T> : EntityTypeConfiguration<T> where T : EntityBase
  {
     public EntityBaseConfiguration()
     {
         HasKey(e => e.Id);
         Property(e => e.Version).IsRowVersion();
     }
  }
  class EntityConfiguration : EntityBaseConfiguration<Entity>
  {
     public EntityConfiguration()
     {
         Map(e => { e.ToTable("Entiteter"); e.MapInheritedProperties(); });
         Property(e => e.Name).IsRequired().HasMaxLength(100);
         HasMany(e => e.Entities).WithRequired().WillCascadeOnDelete();
     }
  }
  class EntityConfiguration2 : EntityBaseConfiguration<Entity2>
  {
     public EntityConfiguration2()
     {
         Map(e => { e.ToTable("Entiteter2"); e.MapInheritedProperties(); });
         Property(e => e.Name).IsRequired().HasMaxLength(100);
     }
  }
  
  

• @Jonpa: Knew it cannot be a many-to-many association since the exception you were getting was something that usually comes out of a required one-to-many association. Yes, you thought is correct, your code (entity.Entities.Remove(entity2)) would cause Entity’s foreign key in Entity2’s table to become Null but your fluent API code (HasMany(e => e.Entities).WithRequired()) made that FK column Not Nullable and that’s where the problem happens. To fix this problem I suggest you first add a FK property to your Entity2 class like the following:
  public class Entity2 : EntityBase
  {
      public string Name { get; set;}
      public Guid? EntityId { get; set; }
  }
  
  
  And then change your fluent API code in EntityConfiguration where you setup the one-to-many association to this:
  
  
  HasMany(e => e.Entities)
          .WithOptional()
          .HasForeignKey(e => e.EntityId)
          .WillCascadeOnDelete();
  
  
  And you should be good to go. Hope this helps.
  

• @Jonpa: OK, I thought having an Entity2 without an Entity makes sense based on your domain model but if that’s not the case then don’t do it since like you mentioned, your model should exactly reflect your business domain. Based on what you described, the correct way of mapping this association would be as follows:
  
  public class Entity : EntityBase
  {
      public string Name { get; set; }
      public virtual ICollection<Entity2> Entities { get; set; }     
  }
  
  
  public class Entity2 : EntityBase
  {
      public string Name { get; set;}
      public Guid EntityId { get; set; }
      public Entity Entity { get; set; }       
  }
  
  
  And the fluent API code to configure the associaion:
  
  
  HasMany(e => e.Entities)
          .WithRequired(e => e.Entity)
          .HasForeignKey(e => e.EntityId);
  
  
  Note how I turn the association into a bidirectional one so that we can take advantage of it when removing an Entity2 from the Entity. Entities collection:
  
  // Assuming entity and entity2 are loading from database and that they are related:
  entity.Entities.Remove(entity2);
  entity2.Entity = new Entity();               
  context.SaveChanges();
  
  
  
  This way, you can have your required relationship in place without getting any exception when changing the associations between your objects.
  

• Ok, but if I want to remove the Entity2 object, or rather delete it. I dont want to assign a new Entity object to the Entity2 object, I only want to delete it. Then I will get the same exception again or am I missing something? Like in the Order -> OrderLine example. How will I design my model to be able to remove an OrderLine from an Order?
  
  /Jonpa
  

• @Jonpa: Then just delete the Entity2 object. For example, in the case of an OrderLine object:
  
  
  context.OrderLines.Remove(orderLine);
  
  
  
  Please note that you don’t need to be worried about the existing association between the Order and OrderLine objects, when you delete the OrderLine object, EF automatically updates the OrderLines collection on the related Order. The bottom line is that an OrderLine object can never exists without an Order, you have to either delete it or replace its Order property with another Order be it new or existing.
  

• My context only exposes Entity (or Order on the Order, OrderLine example). So the only way to "mingle" with Entity2 (OrderLine) is through Entity. Note that this is exactly what I want. I dont want my context to expose Entity2 because Entity is my aggregate root. So is there a way to achieve this?
  

• @Jonpa: An aggregate root is a concept that has to be applied on your Repository classes and not on the definition of the DbContext class. By removing the non-aggregate root DbSets from your DbContext (e.g. DbSet<OrderLine>) you make simple tasks very hard like the way you had to delete the orphan OrderLines by overriding the SaveChanges method. What you are looking for is implemented in some other ORM frameworks though. For example, in NHibernate, associations have a setting called cascade which you can set to delete-orphan. As a result, NHibernate deletes any persistent entity instance that has been removed (dereferenced) from the association (e.g. any persistent OrderLine should be deleted if it’s removed from the OrderLines collection of a persistent Order.). I am also hoping that EF implements these features on the associations but to be honest, I don’t see that happening in the near future.
  

• thanks for this great series, very very helpful~ looking forward to your new posts!
  

• Never mind my previous comment... The setup worked when I put it in your sample application. Turned out the entity was detached. Setting it to Modified and calling SaveChanges, caused it not save the navigational properties. Perfectly logical ;-)
  
  Thanks,
  Twan
  

• @Jim Shaw: I think you are in the right path. An optional entity association, be it one-to-one or one-to-many, is best represented in an SQL database with a join table. We always try to avoid nullable columns in a relational database schema since information that is unknown degrades the quality of the data you store. That said, EF unfortunately does not have a good support for this type of mapping which means you need to create new entities to represent the join tables for your optional one-to-many relationships. For example, the associations between TextBlock and BlockGroup entities could be represented with the following object model:
  
  
  
  public class TextBlock
  {
      public int TextBlockId { get; set; }
      public string Text { get; set; }
      public string Style { get; set; }
  }
     
  public class BlockGroup
  {
      public int BlockGroupId { get; set; }
      public TextBlock Title { get; set; }
      public ICollection<BlockGroupTextBlock> GroupList { get; set; }
  }
  
  
  public class BlockGroupTextBlock
  {
      public int Id { get; set; }
      public int Index { get; set; }
      public int BlockGroupId { get; set; }
  
  
      public TextBlock TextBlock { get; set; }
  }
     
  public class Context : DbContext
  {
      public DbSet<TextBlock> TextBlocks { get; set; }
      public DbSet<BlockGroup> BlockGroups { get; set; }
      public DbSet<BlockGroupTextBlock> BlockGroupTextBlocks { get; set; }
  
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          modelBuilder.Entity<BlockGroup>()
                  .HasMany(b => b.GroupList)
                  .WithOptional()
                  .HasForeignKey(b => b.BlockGroupId);
  
  
          modelBuilder.Entity<BlockGroup>()
                  .HasOptional(b => b.Title)
                  .WithRequired();
             
          modelBuilder.Entity<BlockGroupTextBlock>()
                  .HasRequired(b => b.TextBlock)
                  .WithOptional()
                  .WillCascadeOnDelete();
      }
  }
  
  
  
  Hope this helps.
  
  
  

• @Ran Davidovitz: Yes, you can define an ItemCategory class and it won’t create a new one for you. The catch is that you will have to change the navigation properties on both sides as well. Something like the following will do the trick:
  
  
  public class Item
  {
      public int ItemId { get; set; }
      public virtual ICollection<ItemCategory> ItemCategories { get; set; }
  }
  
  
  public class Category
  {
      public int CategoryId { get; set; }
      public virtual ICollection<ItemCategory> ItemCategories { get; set; }
  }
  
  
  public class ItemCategory
  {
      public int ItemId { get; set; }
      public int CategoryId { get; set; }
  }
     
  public class Context : DbContext
  {
      public DbSet<Item> Items { get; set; }
      public DbSet<Category> Categories { get; set; }
  
  
      protected override void OnModelCreating(DbModelBuilder modelBuilder)
      {
          modelBuilder.Entity<ItemCategory>()
                      .HasKey(ic => new { ic.CategoryId, ic.ItemId });
      }
  }
  
  That being said, I think you should NOT do this. Not sure how it helps you with data entry, but for that you can write a custom initializer class and then override its Seed method where you can then use the ExecuteSqlCommand to execute raw SQL statements for your data entry. Hope this helps.
  

• Thanks for your articles, i learned a lot from them. I'm doing my first steps in EF, and still dont know many things about it. Can you give me a hint how to solve my problem?
  
  I have classes:
  
  [Table("Firmy")]
  public class Firma
  {
     [Key]
     [DatabaseGenerated(DatabaseGeneratedOption.None)]
     public int FirmaId{ get; set; }
    
     [MaxLength(150), Required]
     public string Nazwa { get; set; }
     [MaxLength(50), Required]
     [DisplayName("Nazwa skrócona")]
     public string NazwaKrotka { get; set; }
     public int? Telefon { get; set; }
     public int? Fax { get; set; }
     [MaxLength(150)]
     public string Ulica { get; set; }
     //[MaxLength(5)]
     public int Kod { get; set; }
     [MaxLength(150)]
     public string Miasto { get; set; }
     [MaxLength(150), Required]
     [DisplayName("E-mail")]
     public string Email { get; set; }
     public bool Aktywny { get; set; }
     public virtual ICollection<FirmaOddzialHistoria> Oddzialy { get; set; }
  }
  [Table("FirmyOdddzialyHistoria")]
  public class FirmaOddzialHistoria
  {
     [Key]
     [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
     public int FirmaOddzialHistoriaID { get; set; }
     public virtual Firma Firma { get; set; }
     public virtual FirmaOddzial FirmaOddzial { get; set; }
     public DateTime DataStart { get; set; }
     public DateTime DataKoniec { get; set; }
  }
  [Table("FirmyOddzialy")]
  public class FirmaOddzial
  {
     [Key]
     [DatabaseGenerated(DatabaseGeneratedOption.None)]
     public int OddzialId{ get; set; }
     public int? Telefon { get; set; }
     public int? Fax { get; set; }
     [MaxLength(150)]
     public string Ulica { get; set; }
     [MaxLength(5)]
     public string Kod { get; set; }
     [MaxLength(150)]
     public string Miasto { get; set; }
     [MaxLength(150), Required]
     public string Email { get; set; }
     public bool Aktywny { get; set; }
     public virtual ICollection<FirmaOddzialHistoria> Firmy { get; set; }
     public virtual ICollection<UzytkownikHistoria> Uzytkownicy { get; set; }
     public Limit Limit { get; set; }
  }
  
  and i want do something like this (this code don't work):
  
  repository.FirmyOddzialy.Where(p => p.Firmy.DataStart<=DateTime.Now && p.Firmy.DataStop>=DateTime.Now && p.Firmy.Firma.FirmaId==1)
  
  Best regards
  
  

• @JohneBee: Something like this will do the trick:
  
  
  
  var query = (from o in repository.FirmyOddzialy
                      from f in o.Firmy
                      where f.DataStart <= DateTime.Now && f.DataStop >= DateTime.Now && f.Firma.FirmaId == 1
                      select o);
  
  

• Thanks for your articles, i learned a lot from them.
  
  How can i make an Unidirrectional 1 to many relation.<b> and make it  cascade on delete </b>
  
  Here is my models :
  
   public class User
  {
      public int UserId { get; set; }
      public virtual ICollection<Shipment> Shipments { get; set; }
  }
  public class Shipment
  {
      public int ShipmentId { get; set; }
  }
  
  Any User May have 0~many shipments.I want to casccade the corresponded shipments and delete them automaticly when i delete  an user.
  
  I dont want make the relation BiDirrectional.and eneable DeleteOnCascade from other side by make the Virtual User Property [Requierd].
  
  thanks.
  
  

• @Rasul: Your code pretty much creates your desired object model, except that you need to add a UserId to the Shipment class as the foreign key property:
  
  public class User
  {
      public int UserId { get; set; }
      public virtual ICollection<Shipment> Shipments { get; set; }
  }
  
  public class Shipment
  {
      public int ShipmentId { get; set; }
      public int UserId { get; set; }
  }
  
  class Context : DbContext
  {
      public DbSet<User> Users { get; set; }
      public DbSet<Shipment> Shipments { get; set; }
  }
  
  

• @fengyj: The exception that you are getting is very normal and pretty much explains what is going on. Think about it, you're asking EF to add two objects for you in one transaction: an Item object who has a Bid object as its SuccessfulBid which happens to have the very same Item object as its Item. EF first tries to insert the Bid object in order to get a SuccessfulBidId for the Item object to insert afterwards but then it notices that the Bid object also needs the very same Item object as its Item which means the Item has to be inserted first to give its id to the Bid. Which one is going to go first? It’s obvious that this circular dependency makes it impossible to have them both inserted in one single transaction.
  Therefore, the only way to make it work is to use two transactions to add the objects like the following code:
  
  
  
  using (UnitOfWork db = new UnitOfWork())
  {
      Item item = db.Items.Add(new Item() { ItemId = 1 });
      db.SaveChanges();
  
  
  
      item.SuccessfulBid = new Bid()
      {
          BidId = 1,
          Item = item
      };
      db.SaveChanges();
  }
  

• Great posts! I've just started to work with EF code first model and after 5 blog parts all pieces come into place.
  

• Thank you.
  This is great post.
  

• Why are the navigation IDs exposed in the object model (e.g. BuyerId, SuccessfulBidId, etc.). Shouldn't these be encapsulated? These appear to be implimentation details (that could change) and should be hidden from the outside world. A user should never need to know the BuyerId, only the Buyer.
  

• I'm still not clear on how all those navigation IDs get set correctly. Do the owning objects have to have explicit code to set or maintain all those navigation ids or does EF handle them somehow? If it does, how does it determine which object owns another (it could appear in the collections, or details, of more than one owner object)?
  
  Consider a User that sells an Item to another User. How is the BuyerId FK set on the Item?