EF Code First 4.1 - How to configure one to many relationship with default
I have a Customer entity which references a collection of Addresses. The complication here is that I want to be able to identify a particular address as the default address.
If possible I would like to hold the FK of the default address in the Customer table. This seems more elegant than having a column in the addresses table to identify the default.
I am having difficulty with the fluent API in terms of defining this relationship. When I run the following code I get an exception which says: "An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details." "Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values."
I created a console app to show the precise problem. In this test app I have a Customer entity, an Address and the flient api configuration.
Any help would be much appreciated:
using System;
using System.Collections.Generic;
using System.Data.Entity.ModelConfiguration;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
namespace OneToManyWithDefault
{
public class Customer
{
private ICollection<Address> m_Addresses;
public Customer()
{
Addresses = new List<Address>();
}
public int Id { get; set; }
public string CompanyName { get; set; }
public virtual ICollection<Address> Addresses
{
get
{
if (m_Addresses == null)
{
m_Addresses = new List<Address>();
}
return m_Addresses;
}
set
{
m_Addresses = value;
}
}
public Address DefaultAddress { get; set; }
public int DefaultAddressId { get; set; }
}
public class Address
{
public int Id { get; set; }
public string Town { get; set; }
public Customer Customer { get; set; }
}
public class MyContext
: DbContext
{
public DbSet<Customer> Customers { get; set; }
public MyContext(string connectionString)
: base(connectionString)
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new CustomerConfiguration());
modelBuilder.Configurations.Add(new AddressConfiguration());
base.OnModelCreating(modelBuilder);
}
}
public class CustomerConfiguration
: EntityTypeConfiguration<Customer>
{
public CustomerConfiguration()
: base()
{
HasKey(p => p.Id);
Property(p => p.Id)
.HasColumnName("Id")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
.IsRequired();
Property(p => p.CompanyName)
.HasColumnName("Name")
.IsRequired();
// Configure the mapping for the Default Address (this is likely to be wrong!):
HasRequired(p => p.DefaultAddress).WithMany()
.Map(x => x.MapKey("DefaultAddressId"))
.WillCascadeOnDelete(false);
HasRequired(p => p.DefaultAddress)
.WithMany()
.HasForeignKey(x => x.DefaultAddressId);
ToTable("Customers");
}
}
public class AddressConfiguration
: EntityTypeConfiguration<Address>
{
public AddressConfiguration()
: base()
{
HasKey(p => p.Id);
Property(p => p.Id)
.HasColumnName("Id")
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
.IsRequired();
Property(p => p.Town)
.HasColumnName("Town")
.IsRequired();
HasRequired(p => p.Customer)
.WithMany(c => c.Addresses)
.Map(x => x.MapKey("CustomerId"));
ToTable("Addresses");
}
}
class Program
{
private const string ConnectionString =
@"Server=.\sql2005;Database=OneToManyWithDefault;integrated security=SSPI;";
static void Main(string[] args)
{
Customer headOffice = new Customer();
headOffice.CompanyName = "C1";
Address address = new Address();
address.Town = "Colchester";
headOffice.Addresses.Add(address);
address = new Address();
address.Town = "Norwich";
headOffice.Addresses.Add(address);
headOffice.DefaultAddress = address;
MyContext context = new MyContext(ConnectionString);
context.Customers.Add(headOffice);
context.SaveChanges();
Console.WriteLine("Done.");
Console.ReadLine();
}
}
}
Many thanks,
Paul.
Answer
I don't understand what EF is talking there about "not exposed foreign keys" in the exception. I would consider the inner exception as the important part:
Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.
I think the problem in your model is that you have a mutual dependency between Customer and Address: An address needs a customer (you have marked it as required in your mapping code) and on the other hand a customer needs an address (the default address is required both due to the non-nullable foreign key and due to your mapping code). So, EF doesn't know which entity to save first in your example code - the default address or the customer? Both entities need the primary key of the other to be saved with valid FK contraints.
The easiest solution I can see is to make the default address optional in your model and then save twice (I omit the mappings which work by convention anyway):
public class Customer
{
private ICollection<Address> m_Addresses;
public Customer() { Addresses = new List<Address>(); }
public int Id { get; set; }
public string CompanyName { get; set; }
public virtual ICollection<Address> Addresses { get { ... } set { ... } }
public Address DefaultAddress { get; set; }
public int? DefaultAddressId { get; set; } // FK for optional relationship
}
public class Address
{
public int Id { get; set; }
public string Town { get; set; }
public Customer Customer { get; set; }
}
// ...
public class CustomerConfiguration : EntityTypeConfiguration<Customer>
{
public CustomerConfiguration() : base()
{
Property(p => p.CompanyName)
.HasColumnName("Name")
.IsRequired();
HasMany(c => c.Addresses)
.WithRequired(a => a.Customer)
.Map(x => x.MapKey("CustomerId"));
}
}
public class AddressConfiguration : EntityTypeConfiguration<Address>
{
public AddressConfiguration() : base()
{
Property(p => p.Town)
.HasColumnName("Town")
.IsRequired();
}
}
And then your program would look like this:
static void Main(string[] args)
{
Customer headOffice = new Customer();
headOffice.CompanyName = "C1";
Address address = new Address();
address.Town = "Colchester";
headOffice.Addresses.Add(address);
address = new Address();
address.Town = "Norwich";
headOffice.Addresses.Add(address);
//headOffice.DefaultAddress = address;
//We don't set the default address here as SaveChanges would throw an
//exception. But because it is optional now we are allowed to leave it null.
MyContext context = new MyContext(ConnectionString);
context.Customers.Add(headOffice);
context.SaveChanges();
headOffice.DefaultAddress = address; // headoffice and address have now PKs
context.SaveChanges(); // Updates headoffice in the DB with default address
}
This double SaveChanges is ugly, but I don't see another way.