Entity splitting when key column has different nam

问题:

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.

回答1:

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.

回答2:

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]

回答3:

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

回答4:

No Visual Studio here, but try this with the 1-to-1 approach:

this.HasRequired(e => e.ExtendedProperties).HasConstraint((e, m) => e.Id == m.Id);

Update:
Here are some links that might help (could not find a real reference link)

How to declare one to one relationship using Entity Framework 4 Code First (POCO)
Entity Framework 4 CTP 4 Code First: how to work with unconventional primary and foreign key names

回答5:

And just to provide (as I promised) a 1-to-1 (two entities, two tables) mapping, for what it's worth.
Here is what works for me and should in your case...

public class MainTable
{
    public int ThePrimaryKeyId { get; set; }
    public string Name { get; set; }
}
public class ExtendedTable
{
    public int NotTheSameNameID { get; set; }
    public string AnotherTableColumn { get; set; }
    public MainTable MainEntry { get; set; }
}
public class MainDbContext : DbContext
{
    public DbSet<MainTable> MainEntries { get; set; }
    public DbSet<ExtendedTable> ExtendedEntries { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MainTable>()
            .HasKey(x => new { x.ThePrimaryKeyId });

        modelBuilder.Entity<ExtendedTable>()
            .HasKey(x => new { x.NotTheSameNameID });

        // Extended To Main 1 on 1
        modelBuilder.Entity<ExtendedTable>()
            .HasRequired(i => i.MainEntry)
            .WithRequiredDependent();
    }
}

...and a test code something like...

using (var db = new UserDbContext())
{
    foreach (var userid in Enumerable.Range(1, 100))
    {
        var main = new MainTable { Name = "Main" + userid };
        db.MainEntries.Add(main);

        var extended = new ExtendedTable { AnotherTableColumn = "Extended" + userid, MainEntry = main };
        db.ExtendedEntries.Add(extended);
    }
    int recordsAffected = db.SaveChanges();
    foreach (var main in db.MainEntries)
        Console.WriteLine("{0}, {1}", main.Name, main.ThePrimaryKeyId);
    foreach (var extended in db.ExtendedEntries)
        Console.WriteLine("{0}, {1}, {2}, {3}", extended.AnotherTableColumn, extended.NotTheSameNameID, extended.MainEntry.Name, extended.MainEntry.ThePrimaryKeyId);
}

That creates the following SQL script, tables...

CREATE TABLE [MainTables] (
    [ThePrimaryKeyId] [int] NOT NULL IDENTITY,
    [Name] [nvarchar](4000),
    CONSTRAINT [PK_MainTables] PRIMARY KEY ([ThePrimaryKeyId])
)
CREATE TABLE [ExtendedTables] (
    [NotTheSameNameID] [int] NOT NULL,
    [AnotherTableColumn] [nvarchar](4000),
    CONSTRAINT [PK_ExtendedTables] PRIMARY KEY ([NotTheSameNameID])
)
CREATE INDEX [IX_NotTheSameNameID] ON [ExtendedTables]([NotTheSameNameID])
ALTER TABLE [ExtendedTables] ADD CONSTRAINT [FK_ExtendedTables_MainTables_NotTheSameNameID] FOREIGN KEY ([NotTheSameNameID]) REFERENCES [MainTables] ([ThePrimaryKeyId])

And a note, as per our discussion above...
This ain't the 'splitting' - but
(a) code first IMO doesn't allow anything like that (I tried that first and also modifying the migrations manually but it's 'internally' all based on the expected column names being the same and there seems to be no way around it, for this version of EF at least.
(b) table structure wise - the tables could be made to look exactly what you need (as I said before I used it to relate the existing aspnet membership tables (which I could not change) into my user-table which has an own user-id pointing to outside/aspnet table and id.
True, you cannot make it using one C# model class - but the C# side is much more flexible and if you can control the C# that should give the same effect, to my opinion at least (like in the test, you can access it always through the extended entity, both extended and the main columns and they're always matched 1 to 1 and stay 'in sync'.
Hope this helps some
NOTE: you don't have to worry about the fk id etc. - just always access and add the Main entry via MainEntry, and id-s will be fine.

EDIT:
You could also do the following, to gain the appearance of having to deal with just one class (i.e. sort of a split)

public class ExtendedTable
{
    public int NotTheSameNameID { get; set; }
    public string AnotherTableColumn { get; set; }

    public string Name { get { return MainEntry.Name; } set { MainEntry.Name = value; } }
    // public int MainID { get { return MainEntry.ThePrimaryKeyId; } set { MainEntry.ThePrimaryKeyId = value; } }
    internal MainTable MainEntry { get; set; }

    public ExtendedTable()
    {
        this.MainEntry = new MainTable();
    }
}

...and use it like this...

var extended = new ExtendedTable { AnotherTableColumn = "Extended" + userid, Name = "Main" + userid };  

...also you can revert the direction of the fk by doing the WithRequiredPrincipal instead of dependent.
(also all references have to be w/o 'virtual' if you have required one-to-one)
(and MainTable can be made 'internal' as it's here, so it's not visible from outside - it cannot be nested as that EF doesn't allow - is treated like NotMapped)
...well, that's the best I could do:)

回答6:

Looks like it's been fixed in Entity Framework 6. See this issue http://entityframework.codeplex.com/workitem/388

回答7:

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

回答8:

I faced this issue, and solved by add Column attribute to match the both column names. [Key] [Column("Id")] public int GroupId { get; set; }