EF 6 - Code first invalid one-to-one foreign key relationship

Question

DESIGN BACKGROUND:

I'm trying to create code-first EF6 mappings for the following database structure:

The database design is as follow: Instead of having "CustomerID" as foreign key on all related entities (Employment, expenses, income, etc...), we have a CustomerRelationship table, which will contain the CustomerID, and then a "RelatedID" column which will contain the key of the related entity. For instance, lets say I add an employment record for CustomerID=1, then the following will happen:

1. Create record in CustomerRelationship, setting CustomerID = 1 RelatedID = {new autogenerated EmploymentID, lets say 5} CustomerRelationshipTypeID = 55 (Id in lookup table which states that this record is of type employment)

2. Create record in Employment table (EmploymentID=5)

The above structure will work for all entities tied to a Customer.

I have relationship mappings working for Employment, here are my classes:

public abstract class EntityBase : IEntity
{
    #region IEntity Members
    public int Id { get; set; }

    public DateTime CreatedDate { get; set; }

    public int CreatedUserId { get; set; }

    public int CreatedSource { get; set; }

    public DateTime ModifiedDate { get; set; }

    public int ModifiedUserId { get; set; }

    public int? DataMigrationId { get; set; } 

    public bool IsActive { get; set; }                                    
    #endregion
}


public class Employment : EntityBase
{
    // ... all properties here.. removed most so easier to read
    public int EmploymentTypeId { get; set; }    

    **public virtual ICollection<EmploymentRelationship> EmploymentRelationships { get; set; }**
}

    public EmploymentMap()
    {
        this.HasKey(t => t.Id);
        ToTable("tblEmployment");
        Property(t => t.Id).HasColumnName("EmploymentID");    
        // Mapping for all properties follow       
    }

public abstract partial class CustomerRelationship : EntityBase
{
    public int CustomerId { get; set; }

    public decimal? PercentageShare { get; set; }

    public int CustomerRelationshipTypeId { get; set; }

    public int RelatedId { get; set; }
}

public class EmploymentRelationship : CustomerRelationship
{       
    public virtual Employment Employment { get; set; }
}

    public EmploymentRelationshipMap()
    {
        this.HasKey(t => t.Id);

        Map<EmploymentRelationship>(m =>
        {
            m.Requires("CustomerRelationshipTypeID").HasValue(55).IsRequired(); // Define lookup value for type of employment
            m.ToTable("tblCustomerRelationship");
        });

        Property(t => t.Id).HasColumnName("CustomerRelationshipID");
        Property(t => t.CustomerId).HasColumnName("CustomerID");
        Property(t => t.RelatedId).HasColumnName("RelatedID");

        HasRequired(t => t.Employment)
            .WithMany(t => t.EmploymentRelationships)
            .HasForeignKey(t => t.RelatedId);
    }

public class Customer : EntityBase
{
    // Customer Properties...
    public Customer()
    {
        EmploymentRelationships = new List<EmploymentRelationship>();
    }

    public virtual ICollection<EmploymentRelationship> EmploymentRelationships { get; set; }
}

    public CustomerMap()
    {
        this.HasKey(t => t.Id);

        ToTable("tblCustomer");

        Property(t => t.Id).HasColumnName("CustomerID");
    }


public class CustomerContext 
{
    public CustomerContext()
        : base(SymmetryCopy.context_connectionstring_main)
    {
    }

    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<Employment> Employments { get; set; }

    #region Customer Relationship entity mappings
    public virtual DbSet<EmploymentRelationship> EmploymentRelationships { get; set; }
    #endregion

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new CustomerMap());
        modelBuilder.Configurations.Add(new EmploymentMap());

        #region Customer Relationship entity mappings
        modelBuilder.Configurations.Add(new EmploymentRelationshipMap());
        #endregion
    }
}

CustomerRepo to query context and return results:

public class CustomerRepository : BaseRepository<Customer, CustomerContext>, ICustomerRepository
{
    public CustomerRepository() : 
        base(new CustomerContext())
    {

    }

    public async Task<List<Employment>> GetEmployments(int customerId)
    {
        List<Employment> employments = new List<Employment>();
        using (var context = new CustomerContext())
        {
            var employmentRelationships = context.EmploymentRelationships.Where(l => l.CustomerId == customerId).ToList();
            employments = employmentRelationships.Select(x => x.Employment).ToList();
        }
        return employments;
    }
}

The above method GetEmployments then returns all records matching CustomerID with CustomerRelationshipTypeID = 55 (Key value for Employments). See returns below.

Now to get to my actual questions:

When I try and hook up another Entity Type, ie: Expense, following the same approach as that of Employment, creating Expense.cs, ExpenseMap.cs, ExpenseRelationship.cs, ExpenseRelationshipMap.cs, having the following in ExpenseRElationshipMap.cs:

public class ExpenseRelationshipMap
{
    public ExpenseRelationshipMap()
    {
        HasKey(t => t.Id);

        Map<ExpenseRelationship>(m =>
        {
            m.Requires("CustomerRelationshipTypeID").HasValue(60).IsRequired();
            m.ToTable("tblCustomerRelationship");  // Define lookup value for type of Expense
        });

        Property(t => t.Id).HasColumnName("CustomerRelationshipID");
        Property(t => t.CustomerId).HasColumnName("CustomerID");
        Property(t => t.RelatedId).HasColumnName("RelatedID");
        Property(t => t.PercentageShare).HasColumnName("PercentageShare");

        HasRequired(t => t.Expense)
            .WithMany(t => t.ExpenseRelationships)
            .HasForeignKey(t => t.RelatedId);
    }
}

Once I Created the Map entry, as shown above, when quering the GetEmployments() method, I now get the following exception:

"The entity types 'ExpenseRelationship' and 'EmploymentRelationship' cannot share table 'tblCustomerRelationship' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them.",

What an I missing?

UPDATE

As per jjj comments, I have updated my mappings and created a CustomerRelationship.cs base class.

public class Employment : EntityBase
{      
    public string EmployerName { get; set; }

    public string EmployerContactFirstName { get; set; }

    public string EmployerContactSurname { get; set; }

    public virtual ICollection<EmploymentRelationship> EmploymentRelationships { get; set; }
}

public class Expense : EntityBase
{
    public string Description { get; set; }

    public virtual ICollection<ExpenseRelationship> ExpenseRelationships { get; set; }
}

public abstract class CustomerRelationship : EntityBase
{
    public int CustomerId { get; set; }

    public int? CustomerRelationshipTypeId { get; set; }

    public int RelatedId { get; set; }
}

public class EmploymentRelationship : CustomerRelationship
{
    public virtual Employment Employment { get; set; }
}

public class ExpenseRelationship: CustomerRelationship
{
    public virtual Expense Expense{ get; set; }
}

public class CustomerRelationshipMap : BaseMap<CustomerRelationship>
{
    public CustomerRelationshipMap()
    {
        ToTable("CustomerRelationship");

        Map<EmploymentRelationship>(m => m.Requires("CustomerRelationshipTypeID").HasValue(55));
        Map<ExpenseRelationship>(m => m.Requires("CustomerRelationshipTypeID").HasValue(60));

        Property(t => t.Id).HasColumnName("CustomerRelationshipID");            
        Property(t => t.CustomerId).HasColumnName("CustomerID");
        Property(t => t.RelatedId).HasColumnName("RelatedID");            
    }

public class EmploymentRelationshipMap : BaseMap<EmploymentRelationship>
{
    public EmploymentRelationshipMap()
    {
        HasRequired(t => t.Employment)
            .WithMany(t => t.EmploymentRelationships)
            .HasForeignKey(t => t.RelatedId);
    }
}

public class ExpenseRelationshipMap : BaseMap<ExpenseRelationship>
{
    public ExpenseRelationshipMap()
    {
        HasRequired(t => t.Expense)
            .WithMany(t => t.ExpenseRelationships)
            .HasForeignKey(t => t.RelatedId);
    }
}

public class CustomerContext : BaseContext
{
    public CustomerContext()
        : base(context_connectionstring_main)
    {
    }

    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<Employment> Employments { get; set; }

    public virtual DbSet<CustomerRelationship> CustomerRelationships { get; set; }
    public virtual DbSet<EmploymentRelationship> EmploymentRelationships { get; set; }
    public virtual DbSet<ExpenseRelationship> ExpenseRelationships { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new CustomerMap());
        modelBuilder.Configurations.Add(new EmploymentMap());

        modelBuilder.Configurations.Add(new CustomerRelationshipMap());
        modelBuilder.Configurations.Add(new EmploymentRelationshipMap());
        modelBuilder.Configurations.Add(new ExpenseRelationshipMap());
    }
}

When I query the customer context like this:

var relationships = context.CustomerRelationships.Where(l => l.CustomerId == customerId).ToList();

I get the following exception:

"The foreign key component 'RelatedId' is not a declared property on type 'EmploymentRelationship'. Verify that it has not been explicitly excluded from the model and that it is a valid primitive property.",

Answer 1

You need a base class configuration for all the shared properties (including the primary key).

public class CustomerRelationshipMap : EntityTypeConfiguration<CustomerRelationship>
{
    public CustomerRelationshipMap()
    {
        ToTable("tblCustomerRelationship");

        Map<EmploymentRelationship>(m => m.Requires("CustomerRelationshipTypeID").HasValue(55));
        Map<ExpenseRelationship>(m => m.Requires("CustomerRelationshipTypeID").HasValue(60));

        HasKey(t => t.Id);
        Property(t => t.Id).HasColumnName("CustomerRelationshipID");
        Property(t => t.CustomerId).HasColumnName("CustomerID");
        Property(t => t.RelatedId).HasColumnName("RelatedID");
    }
}

Then, you should be able to have the derived-class-specific configuration in the other configuration classes (though, this isn't something I've tried before).

Edit

Also, you can't have different foreign key associations for derived classes using the same base class property. There are a few options I can think of, but it would depend on your situation:

1. Separate foreign keys for the association between EmploymentRelationship-Employment and ExpenseRelationship-Expense.
2. Giving Employment and Expense a common base class as well -- though that might defeat the purpose of what you're trying to do....
3. Separate 1:0..1 relationships between CustomerRelationship and Employment/Expense (and getting rid of EmploymentRelationship and ExpenseRelationship)
4. TPT inheritance where Employment and Expense inherit from CustomerRelationship (and getting rid of EmploymentRelationship and ExpenseRelationship)

Sources

• Difficulty Concerning EF Code First Fluent API, TPH, and Foreign Keys
• How to map foreign keys between TPH TPT objects - Entity Framework Code First