One-to-Zero-or-One relationship where the FK is part of a compoun PK on dependent tables

Question

I'm developing a library with Entity Framework 6.1.2 for these two tables:

CREATE TABLE [dbo].[CODES]
(
    [CODE] [nvarchar](20) NOT NULL,
    [ ... ],
    CONSTRAINT [PK_CODES] PRIMARY KEY CLUSTERED 
    (
        [CODE] ASC
    )
)

CREATE TABLE [dbo].[AGGREGATIONS]
(
    [ID_AGGREGATION] INT IDENTITY(1,1) NOT NULL, 
    [CODE_LEVEL] TINYINT NOT NULL, 
    [CODE] NVARCHAR(20) NOT NULL,
    [ ... ], 
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED
    (
        [ID_AGGREGATION] ASC
    ),
    CONSTRAINT [FK_AGGREGATIONS_CODES] FOREIGN KEY ([CODE]) REFERENCES [dbo].[CODES] ([CODE])
)

Relationship:

I CODES could have zero or one AGGREGATIONS, but an AGGREGATIONS will have one CODES.

To do it, I have these two entity classes:

public class CODES
{
    public string CODE { get; set; }
    [ ... ]

    public virtual AGGREGATIONS Aggregation { get; set; }
    public virtual AGGREGATION_CHILDS AggregationChild { get; set; }
}
public class AGGREGATIONS
{
    public int ID_AGGREGATION { get; set; }
    public string CODE { get; set; }
    public byte CODE_LEVEL { get; set; }

    public virtual CODES Code { get; set; }
}

I have tried this to set relationship between AGGREGATIONS and CODES on Aggregations' EntityTypeConfiguration<AGGREGATIONS>:

HasKey(ag => ag.ID_AGGREGATION);
HasRequired(ag => ag.Code).WithOptional(c => c.Aggregation);

But I don't know how to set AGGREGATIONS.CODE as foreign key in this relantionship.

How can I set FK on this relationship?

UPDATE

I need to use AGGREGATIONS.ID_AGGREGATION because there is another table that has a foreign key to AGGREGATIONS and to CODES tables:

CREATE TABLE [dbo].[AGGREGATION_CHILDS]
(
    [ID_AGGREGATION] [int] NOT NULL,
    [CODE] [nvarchar](20) NOT NULL,
    [CODE_LEVEL] [tinyint] NOT NULL,
    [POSITION] [int] NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED 
    (
        [ID_AGGREGATION] ASC,
        [CODE] ASC
    ), 
    CONSTRAINT [FK_AGGREGATION_CHILDS_AGGREGATIONS] FOREIGN KEY ([ID_AGGREGATION]) REFERENCES [AGGREGATIONS]([ID_AGGREGATION]), 
    CONSTRAINT [FK_AGGREGATION_CHILDS_CODES] FOREIGN KEY ([CODE]) REFERENCES [CODES]([CODE])

If I do what you have recommend me in the answer, I will have two columns CODE in AGGREGATION_CHILDS table. One of them will be foreign key to AGGREGATIONS table and also to CODES table. And the other one FK to CODES table.

Answer 1

This is how I have solved the problem:

public class CODES
{
    public string CODE { get; set; }
    public byte CODE_LEVEL { get; set; }
    [ ... ]

    public virtual AGGREGATION_CHILDS AggregationChild { get; set; }
    public virtual AGGREGATIONS Aggregation { get; set; }
}
public class AGGREGATIONS
{
    public string CODE { get; set; }
    public string CREATED { get; set; }

    public virtual ICollection<AGGREGATION_CHILDS> AggregationChilds { get; set; }
    public virtual CODES Code { get; set; }
}
public class AGGREGATION_CHILDS
{
    public string CODE { get; set; }
    public string PARENT_CODE { get; set; }
    public int POSITION { get; set; }

    public AGGREGATIONS Aggregation { get; set; }
    public CODES Code { get; set; }
}

And their maps:

class AGGREGATIONSConfiguration : EntityTypeConfiguration<AGGREGATIONS>
{
    public AGGREGATIONSConfiguration()
    {
        HasKey(ag => ag.CODE);

        Property(ag => ag.CODE)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        Property(ag => ag.CODE)
            .HasMaxLength(20)
            .IsRequired();

        Property(ag => ag.CREATED)
            .HasMaxLength(50)
            .IsOptional();

        HasRequired(ag => ag.Code)
            .WithOptional(c => c.Aggregation)
            .WillCascadeOnDelete(false);
    }
}
class AGGREGATION_CHILDSConfiguration : EntityTypeConfiguration<AGGREGATION_CHILDS>
{
    public AGGREGATION_CHILDSConfiguration()
    {
        HasKey(ag_ch => ag_ch.CODE);

        Property(ag_ch => ag_ch.CODE)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        Property(ag_ch => ag_ch.CODE)
            .HasMaxLength(20)
            .IsRequired();

        Property(ag_ch => ag_ch.PARENT_CODE)
            .HasMaxLength(20)
            .IsRequired();

        HasRequired(ag_ch => ag_ch.Aggregation)
            .WithMany(ag => ag.AggregationChilds)
            .HasForeignKey(ag_ch => ag_ch.PARENT_CODE);

        HasRequired(ag_ch => ag_ch.Code)
            .WithOptional(c => c.AggregationChild)
            .WillCascadeOnDelete(false);
    }
}

CODESConfiguration is not relevant here.

I have used CODE as PK on AGGREGATIONS and on AGGREGATION_CHILDS and also as FK to CODES table. I have remove ID_AGGREGATION from AGGREGATIONS table and remove composite PK on AGGREGATION_CHILDS table.

I hope it helps someone that have the same problem with One-to-Zero-or-One relationship.