Use foreign keys as primary key

Question

I have a database with a lot of relationships. Unfortunately it's an old maintained database which did not seem to understand the concept of association or keys, of which it sometimes lacks. It's main use is in another application, thus I am most unwilling to change the database (but rather configure Entity Framework)

The issue I have is trying to use the foreign keys as primary keys, as (I guess) association tables use it.

My tables are called

Operation{    Criteria {    Parameter{    CriteriaSelection {
  ID            ID,           ID,           ID,
  ..            ..            ..            ..
}             }             }             }

CheckpointOperation {
  ID,
  Criteria_ID,
  ..
}

CheckpointParameter {
  // missing primary key
  CheckpointOperation_ID,  // a CheckpointOperation has many CheckpointParameters
  CriteriaSelection_ID,    // a CheckpointParameter has one CriteriaSelection
  Parameter_ID,            // .. and one Parameter
  ..
}

The issue here is that CheckpointParameter does not have any primary key defined specifically, but rather the Primary key of { CheckpointOperation_ID, CriteriaSelection_ID, Parameter_ID }. Now I am trying to map that in EntityFramework with a class CheckpointParameterMap : EntityTypeConfiguration<CheckpointParameter>.

public class CheckpointOperation
{
    public int Id { get; set; }
    public virtual Operation Operation { get; set; }

    public virtual ICollection<CheckpointParameter> Parameters { get; set; }
}

public class CheckpointParameter
{
    public int OperationId { get; set; }

    public virtual CheckpointOperation CheckpointOperation { get; set; }
    public virtual CriteriaSelection Criteria { get; set; }
    public virtual QualityParameter Parameter { get; set; }
}

public class CheckpointParameterMap : EntityTypeConfiguration<CheckpointParameter>
{
    public CheckpointParameterMap()
    {
        HasKey(p => new { p.OperationId });

        Property(p => p.OperationId)
            .HasColumnName("CheckpointOperation_ID");

        HasRequired(p => p.CriteriaSelection)
            .WithRequiredDependent()
            .Map(map => map.MapKey("CriteriaSelection_ID"));

        HasRequired(p => p.Parameter)
            .WithRequiredDependent()
            .Map(map => map.MapKey("Parameter_ID"));

        HasRequired(p => p.CheckpointOperation)
            .WithMany(o => o.CheckpointParameters)
            .HasForeignKey(p => p.OperationId);
    }
}

But this gives me an error as of

Multiplicity is not valid in Role 'CheckpointParameter_CheckpointOperation_Source' in relationship 'CheckpointParameter_CheckpointOperation'. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be '1'.\r\n"}

So I tried to remove the HasKey, but that gives me an error of

EntityType 'CheckpointParameter' has no key defined. Define the key for this EntityType.\r\n\tSystem.Data.Entity.Edm.EdmEntitySet: EntityType: EntitySet 'CheckpointParameters' is based on type 'CheckpointParameter' that has no keys defined.\r\n"}

So then I tried to use the foreign CriteriaSelectionId as well in the primary key, since I have the column name:

public class CheckpointParameter
{
    ...
    public int CriteriaSelectionId { get; set; }
    ...
}

public CheckpointParameterMap()
{
    HasKey(p => new { p.OperationId, p.CriteriaSelectionId });
    //...
    Property(checkpointParameter => checkpointParameter.CriteriaSelectionId)
        .HasColumnName("CEC_ID");
}

But this gives me the following error:

{"Schema specified is not valid. Errors: \r\n(48,6) : error 0019: Each property name in a type must be unique. Property name 'CriteriaSelection_ID' was already defined."}

What is the proper way of defining the primary key, which are the foreign keys, without modifying the database?

Have you run a distinct query on those columns in a copy of the production data? It sounds like you are confident that the combination of those keys forms a natural key of the table. If it does, then go ahead and create a SQL primary key on those columns. — Keith Payne
I do have a copy of the production data, yes. I have access to the Oracle Database where the tables are defined, which contains the following (which for me validates that this is the key): CREATE UNIQUE INDEX "X"."I$CEPOQ$UNIQUE" ON "X"."CEPOQ" ("CEC_ID", "CEPO_ID", "Q_ID", "FRQ"), where cec is Criteria, cepo is Ch.Operation and q is Parameter. Do please correct me if I am wrong — Default
In this answer stackoverflow.com/a/15704189/2516770 it is stated that Oracle does not consider a unique index to also be a unique constraint or key constraint. Going off the first error message, I'm guessing that creating a unique key constraint will tell the Oracle EF provider that it is dealing with a distinct value for the 1 side of the (1:n) relationship. This is just a guess, though. — Keith Payne
@KeithPayne That is a very good point. However, the second message tells me that I am not allowed to not have a primary key.. I think I read somewhere else on Stackoverflow that the best solution for that was to add a primary key in the database.. Maybe I should revisit that path of troubleshooting. — Default
@KeithPayne just as a FYI, I found that CREATE UNIQUE INDEX does not allow duplicates (due to the UNIQUE keyword). So it's kind of like a primary key (but it allows null values). — Default

Santin Santin · Accepted Answer · 2013-10-08T14:23:18

You need in Mapping's Criteria and Operation add what field will be the reference like this:

this.HasKey(x => x.OperationId); // In Operation Mapping

this.HasKey(x => x.CriteriaId); // In Criteria Mapping

And in Criteria or Operation you should map some like this:

this.HasMany(b => b.Criterias)
    .WithMany( x=> x.Operations)
    .Map(x =>
    {
        x.ToTable("CheckpointParameter");
        x.MapLeftKey("CriteriaId");
        x.MapRightKey("OperationId");
    });

Use foreign keys as primary key

1 Answers