Entity Framework making incorrect PK-FK mapping on Code First migration

Question

I have the following 3 classes set up to be created in a SQL Server database using Entity Framework Code First migrations. The Survey object is the main table.

public class Survey
{
    public int SurveyId {get; set;} //Primary Key
    public string Description {get; set;}
    public bool HasDevice {get; set;}
    public bool HasProcess {get; set;}

    public virtual Process Process {get; set;} 
    public virtual ICollection<Device> Devices {get; set;} 
}

Each Survey can have multiple Devices (1-to-many)

public class Device
{
    public int DeviceId {get; set;} //Primary Key
    public string DeviceType {get; set;}

    public int SurveyId {get; set;} //Foreign Key
    public virtual Survey Survey {get; set;}
}

Each Survey should have only one Process (1-to-0..1)

public class Process
{
    public int ProcessId {get; set;} //Primary Key
    public string ProcessInfo {get; set;}

    public int SurveyId {get; set;} //Foreign Key
    public virtual Survey Survey {get; set;}
}

The Fluent API mapping for these classes looks like this.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.HasDefaultSchema("Survey");
    modelBuilder.Entity<Survey>().HasOptional(x => x.Process).WithRequired(x => x.Survey);
    modelBuilder.Entity<Survey>().HasMany(x => x.Devices).WithRequired(x => x.Survey);
}

The problem is that when I apply the code first migration, the ForeignKey property in the Process table (1-to-0..1) keeps getting set to the ProcessId field rather than the SurveyId. This means that every time I try to add a new Process record, I get the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Survey.Processes_Survey.Surveys_ProcessId". The conflict occurred in database "Backflow", table "Survey.Surveys", column 'SurveyId'.

The 1-to-many mapping for Device works just fine.

I thought initially that this was due to having all my PK fields just say Id, but even after adding in the additional label part, it still makes the incorrect PK-FK link. I have also tried avoiding the Fluent API by adding the DataAnnotation [Key, ForeignKey("xyz")] instead but it has the same result. Recompiling the project, restarting Visual Studio, and even creating a new project and a new database do not help.

Is there something in the Fluent API or DataAnnotations that I am missing to get this to join correctly? Also, manually fixing the FK in the database does make it work, but that kind of defeats the purpose of doing everything in Code First with migrations.

`Process.ProcessId` is supposed to be both PK and FK when it's in a [1:1 relationship](http://stackoverflow.com/a/26425364/861716). Also when it's 1-0..1. So you should remove the `SurveyId` property. — Gert Arnold, Mar 09 '17 at 21:45
@Gert OK, I'm confused. If the `SurveyId` is removed from the Process object, what is it ForeignKey-ing to? Will the ProcessID field just get the same ID as the Survey record has? — techturtle, Mar 09 '17 at 21:56
Yes, that's the idea, this uniquely ties it to the `Survey` it belongs to. This also means that `Process.ProcessId` is not an identity column. — Gert Arnold, Mar 09 '17 at 21:58
@Gert I removed `Process.SurveyId` but still getting the same error as above. I'm guessing that is because the `Process.ProcessId` field is empty, so there is nothing to match to `Survey.SurveyId`. I thought that EF would handle that automatically like it does for the 1-to-many table. Do I have to plug the SurveyId number into the ProcessId field manually, or is there something special I need to do to get it to pull from the existing survey? — techturtle, Mar 09 '17 at 22:19
I only modified the models and related code to remove the `Process.SurveyId` field. Since this is just the stripped down "MCVE" version of my app, I removed and recreated the database. I didn't recreate all my views and controllers, just removed references to that field. — techturtle, Mar 10 '17 at 12:46
Of course existing Processes won't have primary keys that refer to Surveys (or at best only by coincidence to the right Survey). This requires a conversion of Process.SurveyId values to Process.ProcessId, which means: rebuild the table. Migrations isn't going to do that for you. — Gert Arnold, Mar 10 '17 at 12:53
@Gert I passed the SurveyId value to the view and added a hidden field to hold it as the ProcessId and it works now. If you would add your comment(s) as an answer I can accept it. Thanks! — techturtle, Mar 10 '17 at 16:07

score 4 · Accepted Answer · answered Mar 10 '17 at 21:21

The fluent mapping of the 1-0..1 relationship is correct:

modelBuilder.Entity<Survey>()
    .HasOptional(s => s.Process)
    .WithRequired(p => p.Survey);

But Process shouldn't have a SurveyID property (and column). In EF6, the dependent part of a 1-0..1 relationship (here: Process) is supposed to have a primary key that also refers to its principal (here: Survey) as foreign key. So Process.ProcessID is both primary key and foreign key. Thus, one Process is uniquely tied to one Survey.

By the way, in the other mapping, I would also mention the foreign key: if configuration is chosen over convention, it better be complete.

modelBuilder.Entity<Survey>()
    .HasMany(s => s.Devices)
    .WithRequired(d => d.Survey)
    .HasForeignKey(d => d.SurveyId);

Entity Framework making incorrect PK-FK mapping on Code First migration

1 Answers1