Map table with duplicated columns to two instances of same class

Question

I have a database that looks something like the following. Yeah, I know, I inherited a bad design, but it can't be changed right now. Basically, each customer can have primary and secondary contact information:

Customer

• id
• customer_number
• [other stuff]

ContactInfo

• customer_id
• primary_name
• primary_email
• primary_phone
• secondary_name
• secondary_email
• secondary_phone
• updated_at

I would like my Entity Framework 6 domain model to look like this:

public class Customer
{
    public int Id { get; set; }
    public string CustomerNumber { get; set; }

    public ContactInfo PrimaryContactInfo { get; set; }
    public ContactInfo SecondaryContactInfo { get; set; }
}

public class ContactInfo
{
    public int CustomerId { get; set; }

    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public DateTime? UpdatedAt { get; set; }

    public virtual Customer Customer { get; set; }
}

Is there a way to set up the mapping such that this is possible? In essence, map the columns "primary_" to one instance of ContactInfo, the columns "secondary_" to another instance, and map some of the columns to both instances?

Alternatively, can the fields be mapped to two instances of a complex type:

public class ContactInfo
{
    public int CustomerId { get; set; }

    public SingleContactInfo Primary { get; set; }
    public SingleContactInfo Secondary { get; set; }

    public virtual Customer Customer { get; set; }
}

public class SingleContactInfo
{
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
}

Answer 1

The first solution (inheritance) can't be done, because it's impossible to save two entities in an inheritance scheme to one database record.

The second solution (complex type) does work, but you have to sacrifice the ability to address ContactInfos as independent entities and to include updated_at in your model. (Because updated_at can't be mapped twice to one complex type property).

The only way I see to map entities to the data model while keeping ContactInfo as an independent entity set (almost) and the ability to read/write updated_at, is by table splitting.

In this mapping, table ContactInfo is split into two entities ContactInfoPrimary and ContactInfoSecondary. Customer has a 1:1 association to ContactInfoPrimary, which contains ContactInfoSecondary.

This is what it looks like:

The classes:

public partial class Customer
{
    public int Id { get; set; }
    public string CustomerNumber { get; set; }
    public virtual ContactInfoPrimary ContactInfoPrimary { get; set; }
}

public abstract class ContactInfo
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
}

public class ContactInfoPrimary : ContactInfo
{
    public DateTime UpdatedAt { get; set; }
    public virtual Customer Customer { get; set; }
    public virtual ContactInfoSecondary ContactInfoSecondary { get; set; }
}

public class ContactInfoSecondary : ContactInfo
{ }

The mappings:

class CustomerMap : EntityTypeConfiguration<Customer>
{
    public CustomerMap()
    {
        ToTable("Customer");
        HasKey(c => c.Id);
        Property(c => c.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
            .HasColumnName("id");
        Property(c => c.CustomerNumber)
            .HasColumnName("customer_number");
        HasOptional(c => c.ContactInfoPrimary).WithRequired(ci => ci.Customer);
    }    
}

class ContactInfoPrimaryMap : EntityTypeConfiguration<ContactInfoPrimary>
{
    public ContactInfoPrimaryMap()
    {
        ToTable("ContactInfo");
        HasKey(ci => ci.CustomerId);
        Property(ci => ci.CustomerId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
            .HasColumnName("customer_id");
        Property(ci => ci.UpdatedAt).HasColumnName("updated_at");
        Property(ci => ci.Name).HasColumnName("primary_name");
        Property(ci => ci.Email).HasColumnName("primary_email");
        Property(ci => ci.Phone).HasColumnName("primary_phone");

        // This, and the ToTable statements, define the table splitting.
        HasRequired(ci => ci.ContactInfoSecondary).WithRequiredPrincipal();
    }
}

class ContactInfoSecondaryMap : EntityTypeConfiguration<ContactInfoSecondary>
{
    public ContactInfoSecondaryMap()
    {
        ToTable("ContactInfo");
        HasKey(ci => ci.CustomerId);
        Property(ci => ci.CustomerId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
            .HasColumnName("customer_id");
        Property(ci => ci.Name).HasColumnName("secondary_name");
        Property(ci => ci.Email).HasColumnName("secondary_email");
        Property(ci => ci.Phone).HasColumnName("secondary_phone");
    }
}

And the context:

public partial class TestSO : DbContext
{
    public TestSO() : base("name=TestSO")
    { }

    public virtual DbSet<ContactInfoPrimary> ContactInfoes { get; set; }
    public virtual DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new CustomerMap());
        modelBuilder.Configurations.Add(new ContactInfoPrimaryMap());
        modelBuilder.Configurations.Add(new ContactInfoSecondaryMap());
    }
}

Now you can create a Customer with contact info like so:

var cis = new ContactInfoSecondary
            {
                Name = "Name2",
                Email = "email2",
                Phone = "phone2"
            };
var cip = new ContactInfoPrimary
            {
                Name = "Name1",
                Email = "email1",
                Phone = "phone1",
                UpdatedAt = DateTime.Today,
                ContactInfoSecondary = cis
            };
var cst = new Customer
            {
                ContactInfoPrimary = cip,
                CustomerNumber = "number-nine"
            };
context.Customers.Add(cst);
context.SaveChanges();

And this is how to fetch customers with all information:

context.Customers.Include(c => c.ContactInfoPrimary.ContactInfoSecondary)

Or contact information separately:

context.ContactInfoes.Include(c => c.ContactInfoSecondary)

You see why I said that you can almost get ContactInfo independently. Not entirely. You can only get the secondaries through the primaries.

Answer 2

If you are OK removing the CustomerID and Customer references from your ContactInfo class, then you could try the following:

Register ContactInfo as a ComplexType:

modelBuilder.ComplexType<ContactInfo>();

Define specific names for the columns as per each instance on the Customer object (I am just showing how to do this for the name property):

modelBuilder.Entity<Customer>().Property(p => p.PrimaryContactInfo.Name).HasColumnName("primary_name");
modelBuilder.Entity<Customer>().Property(p => p.SecondaryContactInfo.Name).HasColumnName("secondary_name");