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

This is the sixth and last post in a series that explains entity association mappings with EF Code First. I've described these association types so far: Support for many-valued associations is an absolutely basic feature of an ORM solution like Entity Framework. Surprisingly, we’ve managed to get this far without needing to talk much about these types of associations. Even more surprisingly, there is not much to say on the topic—these associations are so easy to use in EF that we don’t need to spend a lot of effort explaining it. To get an overview, we first consider a domain model containing different types of associations and will provide necessary explanations around each of them. Since this is the last post in this series, I'll show you two tricks at the end of this post that you might find them useful in your EF Code First developments.

Many-valued entity associations

A many-valued entity association is by definition a collection of entity references. One-to-many associations are the most important kind of entity association that involves a collection. We go so far as to discourage the use of more exotic association styles when a simple bidirectional many-to-one/one-to-many will do the job. A many-to-many association may always be represented as two many-to-one associations to an intervening class. This model is usually more easily extensible, so we tend not to use many-to-many associations in applications.

Introducing the OnlineAuction Domain Model

The model we introducing here is related to an online auction system. OnlineAuction site auctions many different kinds of items. Auctions proceed according to the “English auction” model: users continue to place bids on an item until the bid period for that item expires, and the highest bidder wins. A high-level overview of the domain model is shown in the following class diagram:
Each item may be auctioned only once, so we have a single auction item entity named Item. Bid is associated directly with Item.

The Object Model

The following shows the POCO classes that form the object model for this domain:
public class User
{
    public int UserId { getset; }
    public string Name { getset; }
 
    public virtual ICollection<Item> BoughtItems { getset; }
}
 
public class Item
{
    public int ItemId { getset; }
    public string Name { getset; }
    public double InitialPrice { getset; }
    public DateTime StartDate { getset; }
    public DateTime EndDate { getset; }
    public int? BuyerId { getset; }
    public int? SuccessfulBidId { getset; }
 
    public virtual User Buyer { getset; }
    public virtual Bid SuccessfulBid { getset; }
    public virtual ICollection<Bid> Bids { getset; }
    public virtual ICollection<Category> Categories { getset; }
}
 
public class Bid
{
    public int BidId { getset; }
    public double Amount { getset; }
    public DateTime CreatedOn { getset; }
    public int ItemId { getset; }
    public int BidderId { getset; }
 
    public virtual Item Item { getset; }
    public virtual User Bidder { getset; }
}
 
public class Category
{
    public int CategoryId { getset; }
    public string Name { getset; }
    public int? ParentCategoryId { getset; }
 
    public virtual Category ParentCategory { getset; }
    public virtual ICollection<Category> ChildCategories { getset; }
    public virtual ICollection<Item> Items { getset; }
}

The Simplest Possible Association

The association from Bid to Item (and vice versa) is an example of the simplest possible kind of entity association. You have two properties in two classes. One is a collection of references, and the other a single reference. This mapping is called a bidirectional one-to-many association. The property ItemId in the Bid class is a foreign key to the primary key of the Item entity, something that we call a Foreign Key Association in EF 4. We defined the type of the ItemId property as an int which can't be null because we can’t have a bid without an item—a constraint will be generated in the SQL DDL to reflect this. We use HasRequired method in fluent API to create this type of association:
class BidConfiguration : EntityTypeConfiguration<Bid>
{
    internal BidConfiguration()
    {
        this.HasRequired(b => b.Item)
            .WithMany(i => i.Bids)
            .HasForeignKey(b => b.ItemId);
    }
}

An Optional One-to-Many Association Between User and Item Entities

Each item in the auction may be bought by a User, or might not be sold at all. Note that the foreign key property BuyerId in the Item class is of type Nullable<int> which can be NULL as the association is in fact to-zero-or-one. We use HasOptional method to create this association between User and Item (using this method, the foreign key must be a Nullable type or Code First throws an exception):
class ItemConfiguration : EntityTypeConfiguration<Item>
{
    internal ItemConfiguration()
    {
        this.HasOptional(i => i.Buyer)
            .WithMany(u => u.BoughtItems)
            .HasForeignKey(i => i.BuyerId);
    }
}

A Parent/Child Relationship

In the object model, the association between User and Item is fairly loose. We’d use this mapping in a real system if both entities had their own lifecycle and were created and removed in unrelated business processes. Certain associations are much stronger than this; some entities are bound together so that their lifecycles aren’t truly independent. For example, it seems reasonable that deletion of an item implies deletion of all bids for the item. A particular bid instance references only one item instance for its entire lifetime. In this case, cascading deletions makes sense. In fact, this is what the composition (the filled out diamond) in the above UML diagram means. If you enable cascading delete, the association between Item and Bid is called a parent/child relationship, and that's exactly what EF Code First does by default on associations created with the HasRequired method.

In a parent/child relationship, the parent entity is responsible for the lifecycle of its associated child entities. This is the same semantic as a composition using EF complex types, but in this case only entities are involved; Bid isn’t a value type. The advantage of using a parent/child relationship is that the child may be loaded individually or referenced directly by another entity. A bid, for example, may be loaded and manipulated without retrieving the owning item. It may be stored without storing the owning item at the same time. Furthermore, you reference the same Bid instance in a second property of Item, the single SuccessfulBid (take another look at the Item class in the object model above). Objects of value type can’t be shared.

Many-to-Many Associations

The association between Category and Item is a many-to-many association, as can be seen in the above class diagram. a many-to-many association mapping hides the intermediate association table from the application, so you don’t end up with an unwanted entity in your domain model. That said, In a real system, you may not have a many-to-many association since my experience is that there is almost always other information that must be attached to each link between associated instances (such as the date and time when an item was added to a category) and that the best way to represent this information is via an intermediate association class (In EF, you can map the association class as an entity and map two one-to-many associations for either side.).

In a many-to-many relationship, the join table (or link table, as some developers call it) has two columns: the foreign keys of the Category and Item tables. The primary key is a composite of both columns. In EF Code First, many-to-many associations mappings can be customized with a fluent API code like this:
class ItemConfiguration : EntityTypeConfiguration<Item>
{
    internal ItemConfiguration()
    {
        this.HasMany(i => i.Categories)
            .WithMany(c => c.Items)
            .Map(mc =>
            {
                mc.MapLeftKey("ItemId");
                mc.MapRightKey("CategoryId");
                mc.ToTable("ItemCategory");
            });
    }
}

SQL Schema

The following shows the SQL schema that Code First creates from our object model:

Get the Code First Generated SQL DDL

A common process, if you’re starting with a new application and new database, is to generate DDL with Code First automatically during development; At the same time (or later, during testing), a professional DBA verifies and optimizes the SQL DDL and creates the final database schema. You can export the DDL into a text file and hand it to your DBA. CreateDatabaseScript on ObjectContext class generates a data definition language (DDL) script that creates schema objects (tables, primary keys, foreign keys) for the metadata in the the store schema definition language (SSDL) file (in the next section, you'll see where this metadata come from):
using (var context = new Context())
{
    string script = ((IObjectContextAdapter)context).ObjectContext.CreateDatabaseScript();
}
You can then use one of the classes in the .Net File IO API like StreamWriter to write the script on the disk.
Note how Code First enables cascade deletes for the parent/child relationship between Item and Bid

Get the Runtime EDM

One of the benefits of Code First development is that we don't need to deal with the Edmx file, however, that doesn't mean that the concept of EDM doesn't exist at all. In fact, at runtime, when the context is used for the first time, Code First derives the EDM (CSDL, MSL, and SSDL) from our object model and this EDM is even cached in the app-domain as an instance of DbCompiledModel. Having access to this generated EDM is beneficial in many cases. At the very least, we can add it to our solution and use it as a class diagram for our domain model. More importantly, we can use this EDM for debugging when there is a need to look at the model that Code First creates internally. This EDM also contains the conceptual schema definition language (CSDL) something that drives the EF runtime behavior. The trick is to use the WriteEdmx Method from the EdmxWriter class like the following code:
using (var context = new Context())
{
    XmlWriterSettings settings = new XmlWriterSettings();
    settings.Indent = true;
 
    using (XmlWriter writer = XmlWriter.Create(@"Model.edmx", settings))
    {
        EdmxWriter.WriteEdmx(context, writer);
    }                            
}
After running this code, simply right click on your project and select Add Existing Item... and then browse and add the Model.edmx file to the project. Once you added the file, double click on it and visual studio will perfectly show the edmx file in the designer:
Also note how cascade delete is also enabled in the CSDL for the parent/child association between Item and Bid.

Source Code

Click here to download the source code for the OnlineAuction site that we have seen in this post.

Summary

In this series, we focused on the structural aspect of the object/relational paradigm mismatch and discussed one of the main ORM problems relating to associations. We explored the programming model for persistent classes and the EF Code First fluent API for fine-grained classes and associations. Many of the techniques we’ve shown in this series are key concepts of object/relational mapping and I am hoping that you'll find them useful in your Code First developments.
Published Tuesday, May 17, 2011 5:16 AM by mortezam

Comments

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

Tuesday, May 17, 2011 2:20 AM by Martin H. Normark

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?

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

Tuesday, May 17, 2011 11:09 AM by Horizon_Net

Great posts. Thank you for sharing.

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

Tuesday, May 17, 2011 4:15 PM by haitao

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

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

Wednesday, May 18, 2011 7:03 AM by Dencio

Thank you. Very informative, very helpful post.

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

Wednesday, May 18, 2011 8:30 AM by DJRodriguez

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

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

Saturday, May 21, 2011 4:48 PM by mortezam
@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.

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

Saturday, May 21, 2011 6:02 PM by mortezam
@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 :)

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

Tuesday, May 24, 2011 3:28 AM by Mohammad Tajari

Keep up your good work!

Baba khafan. Baba inkare :)

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

Wednesday, May 25, 2011 12:50 PM by Scott

All very good articles.  Thank you!

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

Thursday, June 9, 2011 2:43 AM by Sasan

merci aghaye manafi babate in post

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

Tuesday, July 12, 2011 11:20 AM by Ricardo Peres

When will other types of collections be supported?

At least, IEnumerable<T>... sometimes, we don't want a collection to be changed.

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

Saturday, August 27, 2011 6:26 PM by Bilal

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

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

Tuesday, August 30, 2011 10:52 AM by julien

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

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

Thursday, September 1, 2011 10:40 PM by mortezam
@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.

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

Wednesday, September 14, 2011 6:35 AM by craigfreeman74

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?

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

Wednesday, September 14, 2011 9:55 PM by mortezam
@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.

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

Tuesday, September 20, 2011 1:02 PM by Mark Phillips

Excellent Series!!!  Thank you.

Will you be doing anything in relation to sprocs and views?

Thanks again.

Mark

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

Tuesday, September 20, 2011 10:54 PM by mortezam
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 :)

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

Wednesday, September 21, 2011 5:55 AM by Farid

Hi

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

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

Friday, September 23, 2011 12:43 PM by mortezam
@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();

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

Thursday, November 3, 2011 9:18 PM by mortezam
@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.

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

Friday, November 4, 2011 7:05 AM by Jonpa

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);
   }
}

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

Monday, November 7, 2011 11:06 AM by mortezam
@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.

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

Monday, November 7, 2011 10:18 PM by mortezam
@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.

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

Tuesday, November 8, 2011 12:06 PM by Jonpa

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

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

Thursday, November 10, 2011 8:31 PM by mortezam
@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.

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

Saturday, November 12, 2011 7:37 AM by Jonpa

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?

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

Friday, November 18, 2011 11:12 AM by mortezam
@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.

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

Wednesday, November 30, 2011 9:57 AM by arkhanwu

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

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

Monday, December 12, 2011 1:45 PM by AroglDarthu

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

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

Sunday, December 18, 2011 6:49 PM by mortezam
@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.

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

Wednesday, December 28, 2011 5:35 PM by Ran Davidovitz

Lets say (i know its bad but i just want it while i filling data - to make it fast), to directly write to the ItemCategory table, can i add a new class for the ItemCategory and add it to the context as DBSET or will it try to create another ItemCategory table ?

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

Thursday, January 5, 2012 4:04 PM by mortezam
@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.

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

Sunday, January 15, 2012 10:23 AM by JohneBee

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

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

Tuesday, January 17, 2012 2:23 PM by mortezam
@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);

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

Wednesday, March 7, 2012 4:38 PM by Rasul

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.

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

Sunday, March 18, 2012 10:11 PM by mortezam
@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; }
}

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

Sunday, March 18, 2012 10:41 PM by mortezam
@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();
}

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

Tuesday, June 5, 2012 1:53 AM by Yegor

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

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

Saturday, June 16, 2012 12:31 AM by choiman

Thank you.

This is great post.

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

Friday, August 17, 2012 9:18 AM by missingLINQ

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.

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

Friday, August 17, 2012 10:51 AM by missingLINQ

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?