Entity Framework: one to zero or one relationship with foreign key on principal

Question

I have a 1:0..1 relationship that I'd like to map with EF 6 using fluent API. The relation consists of a principal, which may or may not have a dependent. A dependent must always have a principal.

In the principal, I need to have access to the Id of the dependent.

My code looks like this:

public class Principal
{
    public int Id {get; private set; }

    public int? DependentId { get; private set; }
    public virtual Dependent Dependent { get; private set; }
}

public class Dependent
{
    public int Id { get; private set; }

    public virtual Principal Principal { get; private set; }
}

My mapping looks like this:

    public class PrincipalMap : EntityTypeConfiguration<Principal>
    {
        public PrincipalMap()
        {
            ToTable("PRINCIPALS");

            HasKey(x => x.Id);

            Property(x => x.Id)
                .HasColumnName("PRINCIPALID")
                .IsRequired()
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

            Property(x => x.DependentId)
                .HasColumnName("DEPENDENTID")
                .IsOptional();
        }
    }

    public class DependentMap : EntityTypeConfiguration<Dependent>
    {
        public DependentMap()
        {
            ToTable("DEPENDENTS");

            HasKey(x => x.Id);

            Property(x => x.Id)
                .HasColumnName("DEPENDENTID")
                .IsRequired()
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

            HasRequired(x => x.Principal).WithOptional(x => x.Dependent).Map(x => x.MapKey("PRINCIPALID")).WillCascadeOnDelete();
        }
    }

Which results in the following migration:

            CreateTable(
                "dbo.PRINCIPALS",
                c => new
                    {
                        PRINCIPALID = c.Int(nullable: false, identity: true),
                        DEPENDENTID = c.Int(),
                    })
                .PrimaryKey(t => t.PRINCIPALID);

            CreateTable(
                "dbo.DEPENDENTS",
                c => new
                    {
                        DEPENDENTID = c.Int(nullable: false, identity: true),
                        PRINCIPALID = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.DEPENDENTID)
                .ForeignKey("dbo.PRINCIPALS", t => t.PRINCIPALID, cascadeDelete: true)
                .Index(t => t.PRINCIPALID);

As you can see, the column DEPENDENTID is not a foreign key. When running the program and associating a dependent object to a principal, the DependentId property remains empty, i.e. EF does not recognize it to be related to the dependent itself.

What am I doing wrong?

Answer 1

In DependentMap you declared field DEPENDENTID as primary key of DEPENDENT table, database generated (identity) so it will never be a foreign key. You can't change it as you want (making it pointing to an entity of your choice).

Also, with EF (and E/R) you don't need two columns (one per table) to have a 1-0..1 relationship. You can have only one column (not nullable).

In your case this model should work:

public class Principal
{
    public int Id { get; private set; }

    public virtual Dependent Dependent { get; private set; }
}

public class Dependent
{
    public int Id { get; private set; }

    public virtual Principal Principal { get; private set; }
}

public class PrincipalMap : EntityTypeConfiguration<Principal>
{
    public PrincipalMap()
    {
        ToTable("PRINCIPALS");

        HasKey(x => x.Id);

        Property(x => x.Id)
            .HasColumnName("PRINCIPALID")
            .IsRequired()
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

public class DependentMap : EntityTypeConfiguration<Dependent>
{
    public DependentMap()
    {
        ToTable("DEPENDENTS");

        HasKey(x => x.Id);

        Property(x => x.Id)
            .HasColumnName("DEPENDENTID")
            .IsRequired()
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        HasRequired(x => x.Principal).WithOptional(x => x.Dependent).Map(x => x.MapKey("PRINCIPALID")).WillCascadeOnDelete();
    }
}

In this case the table creation stataments (generated by EF provider) should be similar to

ExecuteNonQuery==========
CREATE TABLE [DEPENDENTS] (
 [DEPENDENTID] int not null identity(1,1)
, [PRINCIPALID] int not null
);
ALTER TABLE [DEPENDENTS] ADD CONSTRAINT [PK_DEPENDENTS_204c4d57] PRIMARY KEY ([DEPENDENTID])
ExecuteNonQuery==========
CREATE TABLE [PRINCIPALS] (
 [PRINCIPALID] int not null identity(1,1)
);
ALTER TABLE [PRINCIPALS] ADD CONSTRAINT [PK_PRINCIPALS_204c4d57] PRIMARY KEY ([PRINCIPALID])
ExecuteNonQuery==========
CREATE INDEX [IX_PRINCIPALID] ON [DEPENDENTS] ([PRINCIPALID])
ExecuteNonQuery==========
ALTER TABLE [DEPENDENTS] ADD CONSTRAINT [FK_DEPENDENTS_PRINCIPALS_PRINCIPALID] FOREIGN KEY ([PRINCIPALID]) REFERENCES [PRINCIPALS] ([PRINCIPALID])

(I omitted on cascade delete but should be clear as well).

The E/R model is in normal form (and is the only that works with EF).
BTW, if you access to Principal.Dependent property EF will generate a query similar to selected * from dependent where PRINCIPALID = <principal_id> where is the id of the principal entity so it really works.

Now, about your requirements, to access to Dependent.Id from Principal the only way is dependentId = Principal.Dependent.Id (or, better, dependentId = Principal.Dependent == null ? null : Principal.Dependent.Id).

What to do if you REALLY WANT a field for the foreign key on PRINCIPAL that refers to DEPENDENT table?
This model is not in normal form so EF will not handle it (also with DBMS you need to write triggers to handle it).
I mean, in R-DBMS there is not a constraint where you can specify that if a column DEPENDENT.PRINCIPALID refers to a PRINCIPAL also a column PRINCIPAL.DEPENDENTID should refers to the original DEPENDENT.
What you need to do in this case is to handle PRINCIPAL.DEPENDENTID yourself (i.e. the Principal entity must have a DEPENDENTID property that you must handle by yourself and is not used by EF during navigation).

Answer 2

Yes, that is tricky and an EF bug IMO. The workaround I have used is a pseudo 1:M:

HasRequired(x => x.Principal)
  .WithMany()
  .HasForeignKey(x => x.DependentId);
  .WillCascadeOnDelete();

http://weblogs.asp.net/manavi/associations-in-ef-4-1-code-first-part-5-one-to-one-foreign-key-associations