One to zero or one relationship in EF Core

Question

Let me simplify:

Given two models

Department Model

    public Guid Id { get; set; }
    public string DepartmentID { get; set; }
    public string DepartmentName { get; set; }
    public Guid? DepartmentManager { get; set; } 

Employee Model

    public Guid ID { get; set; }
    public string EmployeeID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }

I want to be able to assign one - and only one - Employee as DepartmentManager for the Department.

Example: Department is Finance. Department Manager is Joe, Blow.

DepartmentManager in Department Model should contain the EmployeeID for Joe Blow from the Employee Model

I think once I get the relationship correct in the models, I will be able to finish the cshtml pages to handle the CRUD correctly.

Thanks, John

Answer 1

Assuming these are your model classes:

public class Department
{
    public Guid Id { get; set; }
    public string DepartmentID { get; set; }
    public string DepartmentName { get; set; }
    public Guid? DepartmentManager { get; set; } 
}

and

public class Employee
{
    public Guid ID { get; set; }
    public string EmployeeID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
}

You have a few options for explicitly configuring the relationships between your entities (your data models).

1. Data Attributes

If you aren't comfortable with the fluent API, you can decorate your models with data attributes, which EF will discover and use when building the model in memory. So, for your case, something like this works:

[Table(nameof(Department))]
public class Department
{
    [Key]
    public Guid Id { get; set; }
    public string DepartmentID { get; set; }
    public string DepartmentName { get; set; }

    [ForeignKey(nameof(DepartmentManager))]
    public Guid? DepartmentManagerID { get; set; } 

    public virtual Employee DepartmentManager { get; set; }
}

and

[Table(nameof(Employee))]
public class Employee
{
    public Employee()
    {
        Departments = new HashSet<Department>();
    }

    [Key]
    public Guid ID { get; set; }
    public string EmployeeID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }

    public virtual ICollection<Department> Departments { get; set; }
}

The TableAttribute is optional - I use it to explicitly state that the table uses the singular naming convention (so the table is called "Department", not "Departments"). KeyAttribute should also be optional, since one of EF's conventions is to assume an "ID" field is a primary key, but, again, [Key] makes it explicit (sensing a theme here?).

I also would recommend using DepartmentManagerID as the foreign key property, rather than DepartmentManager, to be consistent. That also lets you add a navigation property, DepartmentManager, which can be used to include the Employee record when querying for Departments.

Employee can (should?) also have a navigation property, Departments, which represents the "many" side of the "one-to-many" relationship between Employee and Department - a Department can only have one Employee (manager), but an Employee can manage many Departments. Making this a virtual property allows EF to lazy-load the property, so you can query for Employee records without always getting the associated Departments.

2. Fluent API

The fluent API can be used in addition to the data attributes, or in place off (your choice). I'd still add the relevant navigation properties, so you models would look like this:

public class Department
{
    public Guid Id { get; set; }
    public string DepartmentID { get; set; }
    public string DepartmentName { get; set; }

    public Guid? DepartmentManagerID { get; set; } 

    public virtual Employee DepartmentManager { get; set; }
}

and

public class Employee
{
    public Employee()
    {
        Departments = new HashSet<Department>();
    }

    public Guid ID { get; set; }
    public string EmployeeID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }

    public virtual ICollection<Department> Departments { get; set; }
}

Then, in your DbContext class (to keep this simple(ish)), you'd configure your models and their relationships:

public partial class JohnsDbContext : DbContext
{
    public JohnsDbContext(DbContextOptions<JohnsDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // To set the table name, uncomment:
        //modelBuilder.Entity<Department>()
        //    .ToTable(nameof(Department));

        modelBuilder.Entity<Department>()
            .HasKey(m => m.ID);

        modelBuilder.Entity<Department>()
            .HasOne(m => m.DepartmentManager)           // Department.DepartmentManager
            .WithMany(m => m.Departments)               // Employee.Departments
            .HasForeignKey(m => m.DepartmentManagerID); // Department.DepartmentManagerID


        modelBuilder.Entity<Employee>()
            .HasKey(m => m.ID);
    }
}

And that's pretty much the minimum to set up relationships using the fluent API. There are more settings available, which Intellisense will help you discover, should you need them.