Archives

Archives / 2011 / May

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

    41 Comments

    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 { get; set; }
    public string Name { get; set; }
 
    public virtual ICollection<Item> BoughtItems { get; set; }
}
 
public class Item
{
    public int ItemId { get; set; }
    public string Name { get; set; }
    public double InitialPrice { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public int? BuyerId { get; set; }
    public int? SuccessfulBidId { get; set; }
 
    public virtual User Buyer { get; set; }
    public virtual Bid SuccessfulBid { get; set; }
    public virtual ICollection<Bid> Bids { get; set; }
    public virtual ICollection<Category> Categories { get; set; }
}
 
public class Bid
{
    public int BidId { get; set; }
    public double Amount { get; set; }
    public DateTime CreatedOn { get; set; }
    public int ItemId { get; set; }
    public int BidderId { get; set; }
 
    public virtual Item Item { get; set; }
    public virtual User Bidder { get; set; }
}
 
public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
    public int? ParentCategoryId { get; set; }
 
    public virtual Category ParentCategory { get; set; }
    public virtual ICollection<Category> ChildCategories { get; set; }
    public virtual ICollection<Item> Items { get; set; }
}

    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.

    Read more...

  • Associations in EF Code First: Part 5 – One-to-One Foreign Key Associations

    26 Comments

    This is the fourth post in a series that explains entity association mappings with EF Code First. I've described these association types so far: In the third part of this series we saw the limitations of shared primary key association and argued that this type of association is relatively rare and in many schemas, a one-to-one association is represented with a foreign key field and a unique constraint. Today we are going to discuss how this is done by learning about one-to-one foreign key associations.

    Introducing the Revised Model

    In this revised version, each User always have two addresses: one billing address and another one for delivery. The following class diagram demonstrates the domain model:

    One-to-One Foreign Key Association

    Instead of sharing a primary key, two rows can have a foreign key relationship. One table has a foreign key column that references the primary key of the associated table (The source and target of this foreign key constraint can even be the same table: This is called a self-referencing relationship.). An additional constraint enforces this relationship as a real one to one. For example, by making the BillingAddressId column unique, we declare that a particular address can be referenced by at most one user, as a billing address. This isn’t as strong as the guarantee from a shared primary key association, which allows a particular address to be referenced by at most one user, period. With several foreign key columns (which is the case in our domain model since we also have a foreign key for DeliveryAddress), we can reference the same address target row several times. But in any case, two users can’t share the same address for the same purpose.

    The Object Model

    Let's start by creating an object model for our domain: 
    public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public int BillingAddressId { get; set; }
    public int DeliveryAddressId { get; set; }
        
    public Address BillingAddress { get; set; }
    public Address DeliveryAddress { get; set; }
}
 
public class Address
{
    public int AddressId { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string ZipCode { get; set; }
}
 
public class Context : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Address> Addresses { get; set; }
}
    As you can see, User class has introduced two new scalar properties as BillingAddressId and DeliveryAddressId as well as their related navigation properties (BillingAddress and DeliveryAddress).

    Configuring Foreign Keys With Fluent API

    BillingAddressId and DeliveryAddressId are foreign key scalar properties representing the actual foreign key values that the relationships are established on. However, Code First will not recognize them as the foreign keys for the associations since their names are not aligned with the conventions that it has to infer foreign keys. Therefore, we need to use fluent API (or Data Annotations) to let Code First know about the foreign key properties. The following fluent API code shows how: 
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
                .HasRequired(a => a.BillingAddress)
                .WithMany()
                .HasForeignKey(u => u.BillingAddressId);
 
    modelBuilder.Entity<User>()
                .HasRequired(a => a.DeliveryAddress)
                .WithMany()
                .HasForeignKey(u => u.DeliveryAddressId);
}
    Alternatively, we can use Data Annotations to achieve this. EF 4.1 introduced a new attribute in System.ComponentModel.DataAnnotations namespace called ForeignKeyAttribute. We can place this on a navigation property to specify the property that represents the foreign key of the relationship: 
    public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public int BillingAddressId { get; set; }
    public int DeliveryAddressId { get; set; }
 
    [ForeignKey("BillingAddressId")]
    public Address BillingAddress { get; set; }
 
    [ForeignKey("DeliveryAddressId")]
    public Address DeliveryAddress { get; set; }
}
    That said, we won't use this data annotation and will go with the fluent API way for a reason that you'll soon see.

    Creating a SQL Server Schema

    The object model seems to be ready to give us the desired SQL schema, however, if we try to create a SQL Server database from it, we will get an InvalidOperationException with this message:
    The database creation succeeded, but the creation of the database objects did not. See InnerException for details.
    The inner exception is a SqlException containing this message:
    Introducing FOREIGN KEY constraint 'User_DeliveryAddress' on table 'Users' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.
    As you can tell from the type of the inner exception (SqlException), it has nothing to do with EF or Code First; it has been generated purely by SQL Server when Code First was trying to create a database based on our object model.

    What's a Multiple Cascade Path Anyway?

    A Multiple Cascade Path happens when a cascade path goes from column col1 in table A to table B and also from column col2 in table A to table B. For example in our case Code First attempted to turn on cascade delete for both BillingAddressId and DeliveryAddressId columns in the Users table. In fact, Code First was trying to use Declarative Referential Integrity (DRI) to enforce cascade deletes and the problem is that SQL Server is not fully ANSI SQL-92 compliant when it comes to the cascading actions. In SQL Server, DRI forbids cascading updates or deletes in a multiple cascade path scenario.

    A KB article also explains why we received this error:
    "In SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree".
    And it exactly applies to our example: The User table appeared twice in a list of cascading referential actions started by a DELETE from the Addresses table. Basically, SQL Server does simple counting of cascade paths and, rather than trying to work out whether any cycles actually exist, it assumes the worst and refuses to create the referential actions (cascades). Therefore, depend on your database engine, you may or may not get this exception.

    Overriding The Code First Convention To Resolve the Problem

    As you saw, Code First automatically turns on cascade delete on a required one-to-many association based on the conventions. However, in order to resolve the exception that we got from SQL Server, we have no choice other than overriding this cascade delete behavior detected by convention. Basically we need to switch cascade delete off on at least one of the relationships and as of EF 4.1, there is no way to accomplish this other than using fluent API. Let's switch it off on DeliveryAddress association for example: 
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
                .HasRequired(a => a.BillingAddress)
                .WithMany()
                .HasForeignKey(u => u.BillingAddressId);
 
    modelBuilder.Entity<User>()
                .HasRequired(a => a.DeliveryAddress)
                .WithMany()
                .HasForeignKey(u => u.DeliveryAddressId).WillCascadeOnDelete(false);
}

    One-to-One Foreign Key Associations in EF Code First

    As you may have noticed, both associations in the fluent API code has been configured as a many-to-one—not one-to-one, as you might have expected. The reason is simple: Code First (and EF in general) does not natively support one-to-one foreign key associations. In fact, EF does not support any association scenario that involves unique constraints at all. Fortunately, in this case we don’t care what’s on the target side of the association, so we can treat it like a to-one association without the many part. All we want is to express “This entity (User) has a property that is a reference to an instance of another entity (Address)” and use a foreign key field to represent that relationship. EF (of course) still thinks that the relationship is many-to-one. This is a workaround for the current EF limitation which comes with two consequences: First, EF won't create any additional constraint for us to enforces this relationship as a one to one, we need to manually create it ourselves. The second limitation that this lack of support impose to us is more important: one to one foreign key associations cannot be bidirectional (e.g. we cannot define a property for the User on the Address class).

    Create a Unique Constraint To Enforce the Relationship as a One to One

    We can manually create unique constraints on the foreign keys in the database after Code First creates it for us but if you are like me and prefer to create your database in one shot then there is a way to have Code First create the constraints as part of its database creation process. For that we can take advantage of the new EF 4.1 ExecuteSqlCommand method on Database class which allows raw SQL commands to be executed against the database. The best place to invoke ExecuteSqlCommand method for this purpose is inside a Seed method that has been overridden in a custom initializer class: 
    protected override void Seed(Context context)
{
    context.Database.ExecuteSqlCommand("ALTER TABLE Users ADD CONSTRAINT uc_Billing UNIQUE(BillingAddressId)");
    context.Database.ExecuteSqlCommand("ALTER TABLE Users ADD CONSTRAINT uc_Delivery UNIQUE(DeliveryAddressId)");
}
    This code adds unique constraints to the BillingAddressId and DeliveryAddressId columns in the DDL generated by Code First.

    SQL Schema

    The object model is ready now and will result in the following database schema:
    It is also worth mentioning that we can still enforce cascade deletes for the Delivery Address relationship. SQL Server allows enforcing referential integrity in two different ways. DRI that we just saw is the most basic yet least flexible way. The other way is to use Triggers. We can write a Delete Trigger on the primary table that either deletes the rows in the dependent table(s) or sets all corresponding foreign keys to NULL (In our case the foreign keys are Non-Nullable so it has to delete the dependent rows).

    Source Code

    Click here to download the source code for the one-to-one foreign key association sample that we have built in this post.

    Summary

    In this post we learned about one-to-one foreign key associations as a better way to create one to one relationships. We saw some limitations such as the need for manual creation of unique constraints and also the fact that this type of association cannot be bidirectional, all due to the lack of unique constraint support in EF. The good news is that the ADO.NET team is working on enabling unique constraints in EF but support for unique constraints requires changes to the whole EF stack which won't happen until the next major release of EF (EF 4.1 is merely layered on top of the current .NET 4.0 functionality) and until then the workaround that I showed here is going to be the way to implement one-to-one foreign key associations in EF Code First.
     

    Read more...