4

Closed

Wrong key mapping in the Reverse Engineer Code First

description

Ok, my Database is made on the Brazil originally in the IBM Informix, and i trying to migrate to SQL Server, i've copy the structure data (table, primary and foreign keys) via SQL script, and i doing a .net console application to migrate the data, so the table and column names is not nice, and the relational keys are problematic, don't try understand what each table means.

So here is the thing; many columns are primary keys in the table one and consequently in the table two many columns are foreign keys, if the reverse engineer code first does not map correctly the primary keys or change the original database order of primary keys mapping, so the code first entity table mapping is wrong, and if you execute the application without change the key mapping to the correct form you will get an ModelValidation Exception, because the foreign key mapping does not match to the primary key mapping.

The wrong key map is on the tabel notes_alim, it's inverting the columns t.alim_versao, t.alim_tp_documento

i'm not certain but i think there other tables mapping with the same behavior

file attachments

Closed Feb 27 at 8:32 PM by jemartti

comments

mvieira wrote Feb 14, 2013 at 10:30 AM

Here is SQL Schema script that i'm using:

mvieira wrote Feb 14, 2013 at 11:24 AM

Sending the project with the wrong map.

moozzyk wrote Feb 14, 2013 at 8:49 PM

Seems to be similar what I hit some time ago - see for more details c8dc315. I wonder if it is the issue I fixed or just similar.

RoMiller wrote Feb 14, 2013 at 10:10 PM

EF Team Triage: Assigning to a member of the EF team for further investigation. Once we have investigated the issue we will decide whether to fix it and if so, which release to fix it in.

BriceLambson wrote Feb 18, 2013 at 10:55 PM

It looks like the reverse engineer code in System.Data.Entity.Design.dll assumes that the order of primary key members matches the order of the columns on the table. Thus, when the HasKey call is generated, the correct order of the primary key members is lost and gets out-of-sync with the order on the foreign keys.

We should be able to work around this by making the HasForeignKey calls match the order of the referenced entity's HasKey call. This can be done in the Power Tools' T4 file.

RoMiller wrote Feb 21, 2013 at 10:15 PM

EF Team Triage: We should fix this in EF6 when we do the tooling consolidation

BriceLambson wrote Dec 26, 2013 at 7:39 PM

Fixed in my private repo.

bazil749 wrote Jan 24 at 3:11 AM

@BriceLambson, any chance I can get a hold of your fix?

I have a rather large legacy database and this issue is causing some major problems. I've have reduced my issues slightly, by modifying the template to wrap the call for HasKey and HasForeignKey in an .OrderBy(x => x.Name), but that's still not 100% correct.

BriceLambson wrote Jan 24 at 5:13 PM

This was checked into the main repo as commit ffceb55944b0. It will be part of One EF (See Work Item 407). We intend to ship a preview soon that will include this work.

jemartti wrote Feb 19 at 2:23 AM

Verified as fix. Great job, Brice!

** Closed by jemartti 02/18/2014 7:23PM

bazil749 wrote Feb 19 at 5:52 PM

I'm confused, is this part of this beta release?
http://www.microsoft.com/en-us/download/details.aspx?id=41928

I see that we went to DataAnnotations instead of mapping files and we're putting all the mapping into one DbContext file?

Am I looking at the right build?

Since this doesn't give me the option to use: "HasKey" (relying on "KeyAttribute") I'm having the same issue.

Even Column(Order = ?) seems to be based on the order in the database, rather than the primary key.

BriceLambson wrote Feb 21 at 4:53 PM

Reopening since the customer is still reporting an issue.

BriceLambson wrote Feb 24 at 9:41 PM

Here is a simplified repro. Start with this database...
CREATE TABLE A (
    Id1 int NOT NULL,
    Id2 int NOT NULL,
    PRIMARY KEY (Id2, Id1) -- Reversed
)

CREATE TABLE B (
    Id int NOT NULL PRIMARY KEY,
    A_Id1 int NOT NULL,
    A_Id2 int NOT NULL,
    FOREIGN KEY (A_Id2, A_Id1) REFERENCES A (Id2, Id1) -- Also reversed
)
Reverse engineer gives you these classes.
public partial class Test : DbContext
{
    public Test()
        : base("name=Test")
    {
    }

    public virtual DbSet<A> A { get; set; }
    public virtual DbSet<B> B { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<A>()
            .HasMany(e => e.B)
            .WithRequired(e => e.A)
            .HasForeignKey(e => new { e.A_Id2, e.A_Id1 }) // Still reversed
            .WillCascadeOnDelete(false);
    }
}

public partial class A
{
    public A()
    {
        B = new HashSet<B>();
    }

    [Key]
    [Column(Order = 0)] // No longer reversed
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id1 { get; set; }

    [Key]
    [Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id2 { get; set; }

    public virtual ICollection<B> B { get; set; }
}

BriceLambson wrote Feb 24 at 10:05 PM

Root cause: The provider model asks for the order of columns in the table, and the order of foreign key columns, but not the order of primary key columns. As a consequence, the reverse engineer code uses the column table order for primary keys. Then, when we use the order of the foreign key columns, it is now out-of-sync with the actual order of the primary key columns.

BriceLambson wrote Feb 26 at 6:48 PM

Fixed in changeset 57257193e6408e14e9e400fb819c3adc40ada65b