Cannot save record to database with 1 : 1 mapping using EF Code First

Question

In my code, I have 2 "tables" which have a 1 to 1 or 1 to 0 mapping. A person table and a passport table (I'm showing code that replicates my issue, so please forgive how contrived these examples are). This means a person may not have a passport, but a passport must have a person associated.

The problem I have is when I save, I get the following error message

A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'Id'.

I must admit, I don't actually really understand the issue here! Yes I can read the words but I don't know why it is struggling with Id column!

This is what I have

[Table("Person")]
public partial class Person
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int Id { get; set; }

    public int PassportId {get;set;}

    public virtual Passport Passport {get;set;}
}

[Table("Passport")]
public partial class Passport 
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int Id { get; set; }

    public string PassportDetail { get; set; }

    public virtual Person Person { get; set; }
}

And in my Entities (DataContext) I have

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{ 
    modelBuilder.Entity<Person>()              //create for person
            .HasOptional(a => a.Passport)      //a person has optional passport
            .WithRequired(s => s.Person);      //a passport requires a person
}

Saving to the database should have been simple...

var p = new Person();
p.Passport = new Passport()
        {
              PassportDetail = "test"
        };
dataContext.Person.Add(p);
dataContext.SaveChanges();

But I am told off for the reason above.

What have I done wrong?

Posts like A dependent property in a ReferentialConstraint is mapped to a store-generated column seem to be very complex and about poorly defined tables where as I hope (famous last words) my situation is simple and I've avoided this)

The accepted answer in A dependent property in a ReferentialConstraint is mapped to a store-generated column error on 1-to-1 relationship seems shows what do!

The fix is update to

[Table("Passport")]
public partial class Passport 
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]  //changed here
    [Key]
    public int Id { get; set; }
….

However, if I do that, then when I save to my database, I'm being told off for inserting duplicates :(

Answer 1

I think the quick fix you found its not the proper way to deal with EF Code first ideolegy, in order to have a one-to-one relationship you have to match each table with the other table primary key , in your case its an int Id , but for the person you want this option to be optional so you mark it as nullable and EF will generate the right scripts to allow a creation of a Person without a Passport or with and a Passport is only allowed to be created if there is an association with a person

[Table("Person")]
public partial class Person
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int Id { get; set; }

    public int? PassportId {get;set;}

    public virtual Passport Passport {get;set;}
}

[Table("Passport")]
public partial class Passport 
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public int Id { get; set; }

    public string PassportDetail { get; set; }

    public int PersonId {get;set;}

    public virtual Person Person { get; set; }
}

Answer 2

In reality one-to-one relationship is not possible in sql server. It can be one-to-zero-or-one relationship. In your case one Person will have one or zero Passport, so can simply the relationship as follows where Person in the principal entity and Passport is the dependent entity.

[Table("Person")]
public partial class Person
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public PersonName {get;set;}

    public virtual Passport Passport {get;set;}
}

[Table("Passport")]
public partial class Passport 
{
    [Key,ForeignKey("Person")]
    public int PersonId { get; set; }  //<-- here `PersonId` the is both the Primary key and foreign key

    public string PassportDetail { get; set; }

    public virtual Person Person { get; set; }
}

Note: In this set up you don't need any Fluent API configuration.

And during insert:

var person = new Person()
{
    PersonName = "Test"
}

Passport passport = new Passport()
{
      PersonId = person.Id,
      PassportDetail = "test"
};

dataContext.Persons.Add(person);
dataContext.Passports.Add(passport);
dataContext.SaveChanges();