Enterprise .Net

• Part 1 – Introduction and Basic Concepts
• Part 2 – Complex Types
• Part 3 – Shared Primary Key Associations
• Part 4 – Table Splitting
• Part 5 – One-to-One Foreign Key Associations
• Part 6 – Many-valued Associations

Many-valued entity associations

Introducing the OnlineAuction Domain Model

The Object Model

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

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

class ItemConfiguration : EntityTypeConfiguration<Item>
{
    internal ItemConfiguration()
    {
        this.HasOptional(i => i.Buyer)
            .WithMany(u => u.BoughtItems)
            .HasForeignKey(i => i.BuyerId);
    }
}

A Parent/Child Relationship

Many-to-Many Associations

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

Get the Code First Generated SQL DDL

using (var context = new Context())
{
    string script = ((IObjectContextAdapter)context).ObjectContext.CreateDatabaseScript();
}

Get the Runtime EDM

using (var context = new Context())
{
    XmlWriterSettings settings = new XmlWriterSettings();
    settings.Indent = true;
 
    using (XmlWriter writer = XmlWriter.Create(@"Model.edmx", settings))
    {
        EdmxWriter.WriteEdmx(context, writer);
    }                            
}

Source Code

Summary

• Part 1 – Introduction and Basic Concepts
• Part 2 – Complex Types
• Part 3 – Shared Primary Key Associations
• Part 4 – Table Splitting
• Part 5 – One-to-One Foreign Key Associations
• Part 6 – Many-valued Associations

Introducing the Revised Model

One-to-One Foreign Key Association

The Object Model

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

Configuring Foreign Keys With Fluent API

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

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

Creating a SQL Server Schema

What's a Multiple Cascade Path Anyway?

Overriding The Code First Convention To Resolve the Problem

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

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

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

SQL Schema

Source Code

Summary

• Part 1 – Introduction and Basic Concepts
• Part 2 – Complex Types
• Part 3 – Shared Primary Key Associations
• Part 4 – Table Splitting
• Part 5 – One-to-One Foreign Key Associations
• Part 6 – Many-valued Associations

The Motivation Behind this Mapping: A Complex Type That Can be Lazy Loaded

Splitting a Single Table into Multiple Entities

An Example From the Northwind Database

The Object Model

public class Employee
{
    public int EmployeeID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string Title { get; set; }
    public string TitleOfCourtesy { get; set; }
    public DateTime? BirthDate { get; set; }
    public DateTime? HireDate { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Region { get; set; }
    public string PostalCode { get; set; }
    public string Country { get; set; }
    public string HomePhone { get; set; }
    public string Extension { get; set; }        
    public string Notes { get; set; }
    public int? ReportsTo { get; set; }        
 
    public virtual EmployeePhoto EmployeePhoto { get; set; }
}
 
public class EmployeePhoto
{
    [Key]
    public int EmployeeID { get; set; }
    public byte[] Photo { get; set; }
    public string PhotoPath { get; set; }
}
 
public class NorthwindContext : DbContext
{        
    public DbSet<Employee> Employees { get; set; }
    public DbSet<EmployeePhoto> EmployeePhoto { get; set; }     
}

How to Create a Table Splitting with Fluent API?

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>()
                .HasRequired(e => e.EmployeePhoto)
                .WithRequiredPrincipal();
        
    modelBuilder.Entity<Employee>().ToTable("Employees");
    modelBuilder.Entity<EmployeePhoto>().ToTable("Employees");
}

See the Lazy Loading of the Dependent Entity in Action

using (var context = new NorthwindContext())
{
    Employee employee = context.Employees.First();
    byte[] photo = employee.EmployeePhoto.Photo;
}

Where to Use this Mapping?

Summary

• Part 1 – Introduction and Basic Concepts
• Part 2 – Complex Types
• Part 3 – Shared Primary Key Associations
• Part 4 – Table Splitting
• Part 5 – One-to-One Foreign Key Associations
• Part 6 – Many-valued Associations

Introducing the Revised Model

Shared Primary Key Associations

How to Implement a One-to-One Primary Key Association with Code First

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
 
    public virtual Address BillingAddress { 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 Shipment
{
    public int ShipmentId { get; set; }     
    public string State { get; set; }
 
    public virtual Address DeliveryAddress { get; set; }
}
 
public class Context : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Address> Addresses { get; set; }
    public DbSet<Shipment> Shipments { get; set; }
}

How Code First Sees the Associations in our Object Model: One-to-Many

How to Change the Multiplicity of the Associations to One-to-One by Using the Conventions

Should We Make the Associations Bidirectional?

How to Change the Multiplicity to One-to-One with Fluent API

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasOptional(u => u.BillingAddress)
                               .WithRequired();
    
    modelBuilder.Entity<Shipment>().HasRequired(u => u.DeliveryAddress)
                                   .WithOptional();
}

Database Schema

Referential Integrity

How Code First Determines the Principal and Dependent Ends in an Association?

modelBuilder.Entity<User>().HasOptional(u => u.BillingAddress).WithRequired();

Second Result of Code First's Principal/Dependent Decision: Database Identity

What about Cascade Deletes?

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasOptional(u => u.BillingAddress)
                               .WithRequired()
                               .WillCascadeOnDelete();
    
    modelBuilder.Entity<Shipment>().HasRequired(u => u.DeliveryAddress)
                                   .WithOptional()
                                   .WillCascadeOnDelete();
}

What the Additional Methods Like WithRequiredDependent are for?

modelBuilder.Entity<User>().HasRequired(u => u.BillingAddress).WithRequiredDependent();

public class RequiredNavigationPropertyConfiguration<TEntityType, TTargetEntityType>
{
    public DependentNavigationPropertyConfiguration<TEntityType, TTargetEntityType> WithMany();
    public CascadableNavigationPropertyConfiguration WithOptional();
    public CascadableNavigationPropertyConfiguration WithRequiredDependent();
    public CascadableNavigationPropertyConfiguration WithRequiredPrincipal();
}

Working with the Model

using (var context = new Context())
{
    Address billingAddress = new Address()
    {
        Street = "Main St.",
        City   = "Seattle"
    };
                
    User user = new User()
    {
        Name = "Morteza",                    
        BillingAddress = billingAddress
    };
 
    context.Users.Add(user);
    context.SaveChanges();
}

using (var context = new Context())
{
    Address deliveryAddress = new Address()
    {
        AddressId = 1,
        Street = "Main St.",                    
    };
 
    Shipment shipment = new Shipment()
    {
        ShipmentId = 1,
        State = "Shipped",                    
        DeliveryAddress = deliveryAddress
    };
 
    context.Shipments.Add(shipment);
    context.SaveChanges();
}

Limitations of This Mapping

• Difficulty in Saving Related Objects
The main difficulty with this approach is ensuring that associated instances are assigned the same primary key value when the objects are saved. For example, when adding a new Address object, it's our responsibility to provide a unique AddressId that is also valid (a User can be found with such a value as UserId.)
• Multiple Addresses for User is Not Possible
With this mapping we cannot have more than one Address for User. At the beginning of this post, when we introduce our model, we assumed that the user has the same address for billing and delivery. But what if that's not the case? What if we also want to add a Home address to User for the deliveries? In the current setup, each row in the User table has at most one corresponding row in the Address table. Two addresses would require an additional address table, and this mapping style therefore wouldn’t be adequate.

Summary

References

• ADO.NET team blog
• Java Persistence with Hibernate book

• Part 1 – Introduction and Basic Concepts
• Part 2 – Complex Types
• Part 3 – Shared Primary Key Associations
• Part 4 – Table Splitting
• Part 5 – One-to-One Foreign Key Associations
• Part 6 – Many-valued Associations

Introducing the Model

Fine-grained Domain Models

Complex Types are Objects with No Identity

Complex Type Discovery

How to Implement a Complex Type with EF Code First

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    
    public Address Address { get; set; }
}
 
public class Address
{
    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; }
}

Complex Types: Splitting a Table Across Multiple Types

Complex Types are Required

using (var context = new Context())
{
    User user = new User()
    {
        Name = "Morteza"
    };
 
    context.Users.Add(user);
    context.SaveChanges();
}

Explicitly Register a Type as Complex

[ComplexType]
public class Address
{
    public string Id { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string ZipCode { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.ComplexType<Address>();
}

Best Practices When Working with Complex Types

• Always Initialize the Complex Type:
Because of the problem we saw, I recommended always initialize the complex type inside its owning entity's constructor.
• Add a Read Only Property to the Complex Type for Null Value Checking:
Defining a non-persistent read only property like HasValue will help to test for null values.
• Consider Always Explicitly Registering a ComplexType:
Even if your class is automatically detected as a complex type by Code First, I still recommend to mark it with ComplexTypeAttribute. Not only that helps your object model to be more readable but also ensures that your complex types will stay as complex as your model evolves in your project. Of course if you have a domain layer then you should use the fluent API's ComplexType method instead since coupling your POCO domain model to the EntityFramework assembly (where ComplexTypeAttribute lives) is the last thing you want to do in your layered architecture!

public class User
{
    public User()
    {
        Address = new Address();
    }
 
    public int UserId { get; set; }        
    public string Name { get; set; }
        
    public Address Address { get; set; }
}
 
[ComplexType]
public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string ZipCode { get; set; }
 
    public bool HasValue
    {
        get
        {
            return (Street != null || ZipCode != null || City != null);
        }
    }
}

Customize Complex Type's Property Mappings at Entity Level

public class Address
{
    [Column("Street")]
    public string Street { get; set; }
    public string City { get; set; }        
    public string PostalCode { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.ComplexType<Address>()
                .Property(a => a.Street)
                .HasColumnName("Street");        
}

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
 
    public Address Address { get; set; }
}
    
public class Customer
{
    public int CustomerId { get; set; }
    public string PhoneNumber { get; set; }
            
    public Address Address { get; set; }
}
 
[ComplexType]
public class Address
{
    [Column("Street")]
    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<Customer> Customers { get; set; }
 
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
                    .Property(c => c.Address.Street)
                    .HasColumnName("Customer_Street");
    }
}

Complex Types and the New Change Tracking API

using (var context = new Context())
{
    var user = context.Users.Find(1);
 
    Address originalValues = context.Entry(user)
                                    .ComplexProperty(u => u.Address)
                                    .OriginalValue;    
    
    Address currentValues = context.Entry(user)
                                   .ComplexProperty(u => u.Address)
                                   .CurrentValue;
}

string city = context.Entry(user)
                     .ComplexProperty(u => u.Address)
                     .Property(a => a.City)
                     .CurrentValue;

string city = context.Entry(user)
                     .Property(u => u.Address.City)
                     .CurrentValue;

Limitations of This Mapping 

Summary

References

• ADO.NET team blog
• Java Persistence with Hibernate book

• Associations in EF Code First CTP5: Part 1 – Complex Types
• Associations in EF Code First CTP5: Part 2 – Shared Primary Key Associations
• Associations in EF Code First CTP5: Part 3 – One-to-One Foreign Key Associations
• Inheritance with EF Code First CTP5: Part 1 – Table per Hierarchy (TPH)
• Inheritance with EF Code First CTP5: Part 2 – Table per Type (TPT)
• Inheritance with EF Code First CTP5: Part 3 – Table per Concrete Type (TPC)

A Note For Those Who are New to EF and Code-First

Code First And Associations

• Part 1 – Introduction and Basic Concepts
• Part 2 – Complex Types
• Part 3 – Shared Primary Key Associations
• Part 4 – Table Splitting
• Part 5 – One-to-One Foreign Key Associations
• Part 6 – Many-valued Associations

Why Starting with Association Mappings?

What is Mapping?

What is Relationship Mapping?

Types of Relationships

• One-to-one relationships: This is a relationship where the maximums of each of its multiplicities is one.
• One-to-many relationships: Also known as a many-to-one relationship, this occurs when the maximum of one multiplicity is one and the other is greater than one.
• Many-to-many relationships: This is a relationship where the maximum of both multiplicities is greater than one.

• Uni-directional relationships: when an object knows about the object(s) it is related to but the other object(s) do not know of the original object. To put this in EF terminology, when a navigation property exists only on one of the association ends and not on the both.
• Bi-directional relationships: When the objects on both end of the relationship know of each other (i.e. a navigation property defined on both ends).

How Object Relationships are Implemented in POCO Object Models?

How Relational Database Relationships are Implemented?

References

• ADO.NET team blog
• Mapping Objects to Relational Databases
• Java Persistence with Hibernate book

• Part 1 – Complex Types
• Part 2 – Shared Primary Key Associations

Introducing the Revised Model

One-to-One Foreign Key Association

The Object Model

public class User
{
    public int UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { 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 PostalCode { get; set; }   
}
 
public class EntityMappingContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Address> Addresses { get; set; }
}

Configuring Foreign Keys With Fluent API

protected override void OnModelCreating(ModelBuilder 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);
}

public class User
{
    public int UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { 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; }
}

Creating a SQL Server Schema

SQL Server and Multiple Cascade Paths

Overriding Code First Convention To Resolve the Problem

protected override void OnModelCreating(ModelBuilder 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

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

protected override void Seed(EntityMappingContext context)
{
    context.Database.SqlCommand("ALTER TABLE Users ADD CONSTRAINT uc_Billing UNIQUE(BillingAddressId)");
    context.Database.SqlCommand("ALTER TABLE Users ADD CONSTRAINT uc_Delivery UNIQUE(DeliveryAddressId)");
}

SQL Schema

Summary

References

• ADO.NET team blog
• Java Persistence with Hibernate book

• Part 1 – Table per Hierarchy (TPH)
• Part 2 – Table per Type (TPT)

TPC and Entity Framework in the Past

Table per Concrete Type (TPC)

TPC Implementation in Code First

public abstract class BillingDetail
{
    public int BillingDetailId { get; set; }
    public string Owner { get; set; }
    public string Number { get; set; }
}
        
public class BankAccount : BillingDetail
{
    public string BankName { get; set; }
    public string Swift { get; set; }
}
        
public class CreditCard : BillingDetail
{
    public int CardType { get; set; }
    public string ExpiryMonth { get; set; }
    public string ExpiryYear { get; set; }
}
    
public class InheritanceMappingContext : DbContext
{
    public DbSet<BillingDetail> BillingDetails { get; set; }
        
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BankAccount>().Map(m =>
        {
            m.MapInheritedProperties();
            m.ToTable("BankAccounts");
        });
 
        modelBuilder.Entity<CreditCard>().Map(m =>
        {
            m.MapInheritedProperties();
            m.ToTable("CreditCards");
        });            
    }
}

TPC Configuration is Not Done Yet!

using (var context = new InheritanceMappingContext())
{
    BankAccount bankAccount = new BankAccount();
    CreditCard creditCard = new CreditCard() { CardType = 1 };
                
    context.BillingDetails.Add(bankAccount);
    context.BillingDetails.Add(creditCard);
 
    context.SaveChanges();
}

How to Solve The Identity Problem in TPC

Switching Off Identity in Code First

public abstract class BillingDetail
{
    [DatabaseGenerated(DatabaseGenerationOption.None)]
    public int BillingDetailId { get; set; }
    public string Owner { get; set; }
    public string Number { get; set; }
}

modelBuilder.Entity<BillingDetail>()
            .Property(p => p.BillingDetailId)
            .HasDatabaseGenerationOption(DatabaseGenerationOption.None);

Working With The Object Model

using (var context = new InheritanceMappingContext())
{
    BankAccount bankAccount = new BankAccount() 
    { 
        BillingDetailId = 1                     
    };
    CreditCard creditCard = new CreditCard() 
    { 
        BillingDetailId = 2,
        CardType = 1
    };
                
    context.BillingDetails.Add(bankAccount);
    context.BillingDetails.Add(creditCard);
 
    context.SaveChanges();
}

Polymorphic Associations with TPC is Problematic

Schema Evolution with TPC is Complex

Generated SQL

var query = from b in context.BillingDetails select b;

TPC's SQL Queries are Union Based

Choosing Strategy Guidelines

• If you don’t require polymorphic associations or queries, lean toward TPC—in other words, if you never or rarely query for BillingDetails and you have no class that has an association to BillingDetail base class. I recommend TPC (only) for the top level of your class hierarchy, where polymorphism isn’t usually required, and when modification of the base class in the future is unlikely.
• If you do require polymorphic associations or queries, and subclasses declare relatively few properties (particularly if the main difference between subclasses is in their behavior), lean toward TPH. Your goal is to minimize the number of nullable columns and to convince yourself (and your DBA) that a denormalized schema won’t create problems in the long run.
• If you do require polymorphic associations or queries, and subclasses declare many properties (subclasses differ mainly by the data they hold), lean toward TPT. Or, depending on the width and depth of your inheritance hierarchy and the possible cost of joins versus unions, use TPC.

Summary

References

• ADO.NET team blog
• Java Persistence with Hibernate book