CODEWITHSUNDEEP
c#entity-frameworkentity-framework-5
Fen
Fen

Reputation: 943

Entity Framework - Possible to have part of compound primary key used in foreign key

I am trying to fit Entity Framework 5 to a legacy database where 2 tables both define a compound primary key. 

Product(ProductId, VersionId, Name, StrengthId) <- ProductId and VersionId form the PK
Strength(StrengthId, VersionId, Description) <- StrengthId and VersionId form the PK

There is a foreign key between the 2 tables defined as Product.StrengthId and Product.VersionID relates to Strength.StrengthId and Strength.VersionId which means that the foreign key uses part of the compound primary key of both tables.

I set up my mappings as follows:

    public ProductConfiguraton()
    {
        ToTable("t_Product");
        HasKey(p => new { p.Id, p.VersionId });

        HasRequired(p => p.Strength)
            .WithMany(b => b.Products)
            .Map(m => m.MapKey("iStrengthID", "iVersionID"));

        Property(p => p.Id).HasColumnName("iProductId");
        Property(p => p.Name).HasColumnName("sName");
        //Property(p => p.VersionId).HasColumnName("iVersionID");

    }

    public StrengthConfiguration()
    {
        ToTable("t_Strength");
        HasKey(p =>  new { p.Id, p.VersionId });

        Property(p => p.Id).HasColumnName("iStrengthID");
        Property(p => p.VersionId).HasColumnName("iVersionID");
        Property(p => p.Description).HasColumnName("sStrengthText");
    }

That produces the following create table script fragments:

CREATE TABLE IF NOT EXISTS t_LIVE_Product (
  iD INTEGER NOT NULL,
  VersionId INTEGER NOT NULL,
  sName text NULL,
  iStrengthID INTEGER NOT NULL,
  iVersionID INTEGER NOT NULL,
  PRIMARY KEY (iD, VersionId),
  FOREIGN KEY (iStrengthID, iVersionID) REFERENCES t_LIVE_Strength (iStrengthID, iVersionID) ON DELETE CASCADE
);

-- Index for foreign key FK_Product_Strength
CREATE INDEX IX_FK_Product_Strength ON t_LIVE_Product (iStrengthID, iVersionID);

CREATE TABLE IF NOT EXISTS t_LIVE_Strength ( 
  iStrengthID INTEGER NOT NULL,
  iVersionID INTEGER NOT NULL,
  sStrengthText text NULL,
  PRIMARY KEY (iStrengthID, iVersionID)
);

The Product table has created 2 columns for VersionId (VersionId and iVersionID), which is not what I want. If I uncomment the last line in my ProductConfiguration which I get the following error:

error 0019: Each property name in a type must be unique. Property name 'iVersionID' was already defined.

This makes sense, but leaves me no closer to solving my problem.

Is what I am attempting to do possible or does anyone know of any workarounds to try and fit this schema into EF?

Upvotes: 0

Views: 328

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

I'm not sure, but you can try this.

public ProductConfiguraton()
{
    ToTable("t_Product");
    HasKey(p => new { p.Id, p.VersionId });

    HasRequired(p => new {p.VersionId, p.StrengthId})
        .WithMany(b => new {b.VersionId, b.Id})
        .HasForeignKey(p => p.VersionIdStrengthId);

    Property(p => p.Id).HasColumnName("iProductId");
    Property(p => p.VersionId).HasColumnName("iVersionID");
    Property(p => p.Name).HasColumnName("sName");
    Property(p => p.StrengthId).HasColumnName("iStrengthId");

}

UPDATE

Or try this:

    HasRequired(p => new {p.VersionId, p.StrengthId})
        .WithMany()
        .HasForeignKey(b => new {b.VersionId, b.Id});

Upvotes: 1

Related Questions