One-to-Zero-or-One relationship with E.F. Code First when column is not primary key

Question

I'm developing an Entity Framework 6.1.3 Code First library, C# and .NET Framework 4.5.1.

I have a problem with a One-to-Zero-or-One relationship (or maybe is another kind of relationship).

I have two tables, Codes and HelperCodes, a code could have zero or one helper code. This is the sql script to create these two tables and their relationships:

CREATE TABLE [dbo].[Code]
(
    [Id] NVARCHAR(20) NOT NULL, 
    [Level] TINYINT NOT NULL, 
    [CommissioningFlag] TINYINT NOT NULL, 
    [SentToRanger] BIT NOT NULL DEFAULT 0, 
    [LastChange] NVARCHAR(50) NOT NULL, 
    [UserName] NVARCHAR(50) NOT NULL, 
    [Source] NVARCHAR(50) NOT NULL, 
    [Reason] NVARCHAR(200) NULL, 
    [HelperCodeId] NVARCHAR(20) NULL,
    CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ),
    CONSTRAINT [FK_Code_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level]),
    CONSTRAINT [FK_Code_HelperCode]
       FOREIGN KEY ([HelperCodeId])
        REFERENCES [dbo].[HelperCode] ([HelperCodeId])
)

CREATE TABLE [dbo].[HelperCode]
(
    [HelperCodeId] NVARCHAR(20) NOT NULL, 
    [Level] TINYINT NOT NULL, 
    [CommissioningFlag] TINYINT NOT NULL, 
    [LastChange] NVARCHAR(50) NOT NULL,
    CONSTRAINT [PK_HelperCode] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ),
    CONSTRAINT [FK_HelperCode_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level])
)

But, when I try to do the same with Entity Framework it doesn't do the same. My problem is in Codes and HelperCodes foreing key.

This is my Entity Framework Code First code:

public class Code
    {
        public string Id { get; set; }
        public byte Level { get; set; }

        // omitted for brevity

        public string HelperCodeId { get; set; }

        public virtual HelperCode HelperCode { get; set; }
    }

class CodeConfiguration : EntityTypeConfiguration<Code>
{
    public CodeConfiguration()
    {
        HasKey(c => c.Id);

        Property(c => c.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        Property(c => c.Id)
            .HasMaxLength(20);

        // omitted for brevity

        Property(c => c.HelperCodeId)
            .HasMaxLength(20)
            .IsOptional();

        HasOptional(c => c.HelperCode)
            .WithRequired(hc => hc.Code);
    }
}

public class HelperCode
{
    public string HelperCodeId { get; set; }
    public byte Level { get; set; }
    public byte CommissioningFlag { get; set; }
    public string LastChange { get; set; }

    public virtual Code Code { get; set; }
}

class HelperCodeConfiguration : EntityTypeConfiguration<HelperCode>
{
    public HelperCodeConfiguration()
    {
        HasKey(h => h.HelperCodeId);

        Property(h => h.HelperCodeId)
            .HasMaxLength(20);

        Property(h => h.Level)
            .IsRequired();

        Property(h => h.CommissioningFlag)
            .IsRequired();

        Property(h => h.LastChange)
            .IsOptional()
            .HasMaxLength(50);
    }
}

This Entity Code First code generates a foreign key in HelperCodes table using HelperCodes.HelperCodeId referencing Codes.Id.

Entity Framework only allows this kind of relationship if both tables have the same primary key.

Any idea about how to resolve this E.F. bug?

Answer 1

The relationship is wrong;Change your code to blow code

 public class HelperCode
{

    public byte Level { get; set; }
    public byte CommissioningFlag { get; set; }
    public string LastChange { get; set; }
    public string CodeId { get; set; }
    public virtual Code Code { get; set; }
}

  public class Code
{
    public string Id { get; set; }
    public byte Level { get; set; }
    public virtual HelperCode HelperCode { get; set; }
}

I also configure the relationship from the other side.

public  class CodeConfiguration : EntityTypeConfiguration<Code>
{
    public CodeConfiguration()
    {
        HasKey(h => h.Id);

        HasOptional(x => x.HelperCode).WithRequired(x => x.Code);
    }
}

Answer 2

First: Remove the HelperCodeId from the Code class:

public class Code
{
    public string Id { get; set; }
    public byte Level { get; set; }

    // omitted for brevity

    public virtual HelperCode HelperCode { get; set; }
}

In the Code configuration:

.HasOptional(i => i.HelperCode)
.WithRequired(i => i.Code);

Generated migration:

CreateTable(
    "dbo.Codes",
    c => new
        {
            Id = c.String(nullable: false, maxLength: 128),
            Level = c.Byte(nullable: false),
        })
    .PrimaryKey(t => t.Id);

CreateTable(
    "dbo.HelperCodes",
    c => new
        {
            HelperCodeId = c.String(nullable: false, maxLength: 128),
            Level = c.Byte(nullable: false),
            CommissioningFlag = c.Byte(nullable: false),
            LastChange = c.String(),
        })
    .PrimaryKey(t => t.HelperCodeId)
    .ForeignKey("dbo.Codes", t => t.HelperCodeId)
    .Index(t => t.HelperCodeId);

Only the HelperCodes table should have the foreign key statement.

HelperCodeId is a primary key and also a foreign key, so it ensures the 0:1 relationship.