EF zero-to-one FLUENT API Foreign Key on a NON Key

Question

I have a legacy database which has broken all the rules of Codd. Here are the entities

class Item {
      [Key]         
      public int ItemId {get;set;}

      public string ItemNo {get;set; }

      [ForeignKey("ItemId")]
      public virtual NumericItem {get;set;} //navigation

}


class NumericItem {  //This is a subset of the Item entity

      [ForeignKey("ItemId")]
      public Item Item {get; set;}

      [Key]         
      public int ItemNo { get; set; } //this is a primary key, different type

      public int ItemId { get; set; } //this is also a primary key  and a foreign key

}

How do I tell EF Code first using Fluent API that NumericItem always has a Item and Item may or may not have a NumericItem. The cardinality is always zero/one

Answer 1

This is the case of the foreign unique key.

Normally, when you have a principal entity (like Item) and an optional dependent (NumericItem) in a relationship of 0 or 1, the dependent primary key is also the foreign key for the principal. In your case, since database is already like that, you could do like this:

public class Item
{
    public int ItemId { get; set; }

    public string ItemNo { get; set; }

    public virtual NumericItem NumericItem {get;set;} //navigation

}


public class NumericItem
{  //This is a subset of the Item entity

    public Item Item { get; set; }

    public int ItemNo { get; set; } //this is a primary key, different type

}

public class NumericItemConfiguration : EntityTypeConfiguration<NumericItem>
{

    public NumericItemConfiguration()
    {
        HasKey(n => n.ItemNo);

        HasRequired(n => n.Item).WithOptional(i => i.NumericItem).Map(m => m.MapKey("ItemId"));

    }

}

public class MyContextContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // do your stuff, and add your configuration here...

        modelBuilder.Configurations.Add(new NumericItemConfiguration());

    }
}

or you can do it without this NumericItemConfiguration class, doing the config directly in your OnModelCreating method:

public class MyContextContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // do your stuff, and add your configuration here...

        modelBuilder.Entity<NumericItem>().HasKey(n => n.ItemNo);

        modelBuilder.Entity<NumericItem>().HasRequired(n => n.Item).WithOptional(i => i.NumericItem);

    }
}

Take note I had to remove your ItemId property from NumericItem class, otherwise EF would complain like this:

ItemId: Name: Each property name in a type must be unique. Property name 'ItemId' is already defined.