Foreign keys to same Entity with one required and another optional

Question

I'm trying to create two entities as below and add referential constraints to them using Fluent API.

The design it to enforce required Primary Contact with optional Secondary Contact with added requirement that Primary and Secondary may be referring to two different contacts in the ContactInfo entity.

public class Employee
{
    public int EmployeeId { get; set; }

    public int PrimaryContactId { get; set; }
    public int SecondaryContactId { get; set; }

    public virtual ContactInfo PrimaryContact { get; set; }
    public virtual ContactInfo SecondaryContact { get; set; }
}

public class ContactInfo
{
    public int ContactId { get; set; }
    public string PhoneNumer { get; set; }
    public string Email { get; set; }
}

public class EmployeeConfiguration : EntityTypeConfiguration<Employee>
{
    public EmployeeConfiguration ()
    {
        var employeeEntity = this;

        employeeEntity.HasKey(e => e.EmployeeId).ToTable("Employees");
        employeeEntity.Property(e => e.EmployeeId).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity);

        employeeEntity.HasRequired(e => e.PrimaryContact).WithMany().HasForeignKey(e => e.PrimaryContactId).WillCascadeOnDelete(true);
        employeeEntity.HasRequired(e => e.SecondaryContact ).WithMany().HasForeignKey(e => e.SecondaryContact Id).WillCascadeOnDelete(false);
    }
}

It seems to create with required constraints as expected but when I try to add an employee with SecondaryContact set to null, the row created for this new Employee has SecondaryContactId set to same as PrimaryContactId which is not my intention.

I'm not able to understand whether the design is correct in the first place, or the configuration needs to be tweaked to get the right results.

If you're going to set your secondary contact to null, then use a nullable FK: public int? SecondaryContactId { get; set; } and change HasRequired(e => e.SecondaryContact ) to HasOptional — Steve Greene, Mar 09 '16 at 13:54
Please see ["Should questions include “tags” in their titles?"](http://meta.stackexchange.com/questions/19190/should-questions-include-tags-in-their-titles), where the consensus is "no, they should not"! — , Mar 09 '16 at 16:15

score 1 · Answer 1 · answered Mar 09 '16 at 13:56

You can add foreign Key annotations and also set the keys to be nullable if you expect either to be empty.

public class Employee
{
    public int EmployeeId { get; set; }

    public int? PrimaryContactId { get; set; }
    public int? SecondaryContactId { get; set; }

    [ForeignKey("PrimaryContactId")]
    public virtual ContactInfo PrimaryContact { get; set; }
    [ForeignKey("SecondaryContactId")]
    public virtual ContactInfo SecondaryContact { get; set; }
}

I'm not looking at using attributes. – Gururaj Mar 09 '16 at 15:08 — Gururaj, Mar 09 '16 at 15:08

score 1 · Answer 2 · answered Mar 09 '16 at 13:59

1

Make the secondary contact id to be a nullable type.

public int? SecondaryContactId { get; set; }

answered Mar 09 '16 at 13:59

Konstantin Dinev

34,219
14
75
100

score 1 · Accepted Answer · edited Mar 09 '16 at 14:11

1

Looks like you set both contacts to be required for your employee object. If you want to have SecondaryContract optional change:

employeeEntity
    .HasRequired(e => e.SecondaryContact)
    .WithMany()
    .HasForeignKey(e => e.SecondaryContactId)
    .WillCascadeOnDelete(false);

to

employeeEntity
    .HasOptional(e => e.SecondaryContact)
    .WithMany()
    .HasForeignKey(e => e.SecondaryContactId)
    .WillCascadeOnDelete(false);

Addtionally change:

public int SecondaryContactId { get; set; }

to

public int? SecondaryContactId { get; set; }

since your SecondaryContactId is optional.

edited Mar 09 '16 at 14:11

drneel

2,887
5
30
48

answered Mar 09 '16 at 14:03

Marcin Ciesla

84
4

FYI, your first 2 code blocks are exactly the same. – drneel Mar 09 '16 at 14:11
@drneel `HasRequired` ->`HasOptional` – Marcin Ciesla Mar 09 '16 at 14:17
Ah, I see now, my bad. – drneel Mar 09 '16 at 14:19
My bad, even I missed to change it to HasOptional, but changing to int to int? did the trick as well. Thanks! – Gururaj Mar 09 '16 at 15:18
So far it's one way relationship Employee -> Contacts. What should I do to reverse traverse from Contact -> Employee (at least for primary contact)? – Gururaj Mar 09 '16 at 15:24

Foreign keys to same Entity with one required and another optional

3 Answers3