Entity splitting when key column has different names?

Question

I'm using Entity Framework 4.3.1 Code-First and I need to split an entity between two tables. The tables have a primary key shared, and it is 1-to-1, but the columns are not named the same on each table.

I don't control the data layout, nor can I request any changes.

So for example, the SQL tables could be

And this would be my entity...

public class MyEntity
{
    public int Id {get; set;}
    public string Name {get;set}
    public string FromAnotherTable {get;set;}
}

And here is the mapping I have.

public class MyEntityMapping : EntityTypeConfiguration<MyEntity>
{
    public MyEntityMapping()
    {
        this.Property(e => e.Id).HasColumnName("ThePrimaryKeyId");
        this.Property(e => e.Name).HasColumnName("MyDatabaseName");
        this.Property(e => e.FromAnothertable).HasColumnName("AnotherTableColumn");
        this.Map(m =>
            {
                m.Properties(e =>
                     {
                         e.Id,
                         e.Name
                     });
                m.ToTable("MainTable");
            });
        this.Map(m =>
            {
                m.Properties(e =>
                     {
                         e.Id,
                         e.FromAnotherTable
                     });
                m.ToTable("ExtendedTable");
            });
}

Since the key shared between them has a different column name, I'm not sure how to map it. This mapping will compile, but fails at runtime because EF emits SQL looking for the "ThePrimaryKeyId" column on the "ExtendedTable" table, which doesn't exist.

EDIT To clarify, what I have defined above can (and does) work if the PK on the "ExtendedTable" followed naming conventions. But it doesn't and I can't change the schema.

Basically, what I need EF to emit is a SQL statement like

SELECT
    [e1].*,   /*yes, wildcards are bad. doing it here for brevity*/
    [e2].*
FROM [MainTable] AS [e1]
INNER JOIN [ExtendedTable] AS [e2]  /*Could be left join, don't care. */
    ON  [e1].[ThePrimaryKeyId] = [e2].[NotTheSameName]

But the only thing it seems to want to emit is

 SELECT
        [e1].*,
        [e2].*
    FROM [MainTable] AS [e1]
    INNER JOIN [ExtendedTable] AS [e2]
        ON  [e1].[ThePrimaryKeyId] = [e2].[ThePrimaryKeyId] /* this column doesn't exist */

Edit I tried the 1-to-1 approach again at NSGaga's suggestion. It didn't work, but here are the results. Entities

public class MyEntity
{
    public int Id { get; set; }
    public int Name { get; set; }
    public virtual ExtEntity ExtendedProperties { get; set; }
}
public class ExtEntity
{
    public int Id { get; set; }
    public string AnotherTableColumn { get; set; }
    public virtual MyEntity MainEntry { get; set; }
}

Here are the mapping classes

public class MyEntityMapping : EntityTypeConfiguration<MyEntity>
{
    public MyEntityMapping()
    {
        this.Property(e => e.Id).HasColumnName("ThePrimaryKeyId");
        this.Property(e => e.Name).HasColumnName("MyDatabaseName");
        this.ToTable("MainTable");
        this.HasKey(e => e.Id);
        this.HasRequired(e => e.ExtendedProperties).WithRequiredPrincipal(f => f.MainEntry);
    }
}

public class ExtEntityMapping : EntityTypeConfiguration<ExtEntity>
{
    public ExtEntityMapping()
    {
        this.Property(e => e.Id).HasColumnName("NotTheSameName");
        this.Property(e => e.AnotherTableColumn).HasColumnName("AnotherTableColumn");
        this.ToTable("ExtendedTable");
        this.HasKey(e => e.Id);
        this.HasRequired(e => e.MainEntry).WithRequiredDependent(f => f.ExtendedProperties);
    }
}

This setup gets the message

"Column or attribute 'MyEntity_ThePrimaryKeyId' is not defined in 'ExtendedTable'"

Changing the final map line to

this.HasRequired(e => e.MainEntry).WithRequiredDependent(f => f.ExtendedProperties).Map(m => M.MapKey("NotTheSameName"));

Returns this message

"Each property name in a type must be unique. property name 'NotTheSameName' was already defined."

Changing the mapped key to use the column from the parent table, MapKey("ThePrimaryKeyId"). returns this message

"Column or attribute 'ThePrimaryKeyId' is not defined in 'ExtendedTable'"

Removing the Id property from the ExtEntity class throws an error because then the entity doesn't have a defined key.

Answer 1

I would like to suggest using some data annotations like this:

MainTable
---------
MainTableId
DatabaseName

ExtendedTable
----------
NotTheSameName
AnotherColumn

public class MainTable
{
 [Key]
 public int MainTableId { get; set; }
 public string DatabaseName { get; set; }

 [InverseProperty("MainTable")]
 public virtual ExtendedTable ExtendedTable { get; set; }
}

public class ExtendedTable
{
 [Key]
 public int NotTheSameName { get; set; }

 public string AnotherColumn { get; set; }

 [ForeignKey("NotTheSameName")]
 public virtual MainTable MainTable { get; set; }
}

Answer 2

I can't find anything that specifically states that the name of the column has to be the same in both tables; but neither can I find anything that says it doesn't, or explains how you would map that scenario. Every example I can find has the key with the same name in both tables. It looks to me like this is a hole in the DbContext design.

Answer 3

I have been working on this very issue for a few days, what I finally did was to set the column name of the Id field within the context of the mapping fragment. This way you can give the Id (or the foreign key dependent on the Id) a different name from the Id of the main table.

this.Map(m =>
    {
        m.Property(p => p.Id).HasColumnName("NotTheSameName");
        m.Properties(e =>
             {
                 e.Id,
                 e.FromAnotherTable
             });
        m.ToTable("ExtendedTable");
    });

If you run and debug this, you would find that it would give you something like what you want:

[e1].[ThePrimaryKeyId] = [e2].[NotTheSameName]

Answer 4

Move the HasColumnName to within the mapping:

this.Property(e => e.FromAnothertable).HasColumnName("AnotherTableColumn");
this.Map(m =>
    {
        m.Properties(e => new
             {
                 e.Id,
                 e.Name
             });
             m.Property(e => e.Id).HasColumnName("ThePrimaryKeyId");
             m.Property(e => e.Name).HasColumnName("MyDatabaseName");

           m.Property(e => e.Id).HasColumnName("ThePrimaryKeyId");
        m.ToTable("MainTable");
    });
this.Map(m =>
    {
        m.Properties(e => new
             {
                 e.Id,
                 e.FromAnotherTable
             });
        m.ToTable("ExtendedTable");
    });
}