Jeff Cox Jeff Cox - 2 years ago 98
C# Question

Setting up multiple Foreign keys of same class in .Net Core / EF Core

I'm building a .Net Core web app to replace an old legacy Access ADP/ADE front end to a project management SQL Server database. However, the builder of the original DB didn't setup a whole lot of Foreign key relationships in favor of doing it all in SQL views. Something I'm hoping to remedy with this app.

I'm wanting to make sure I have my head wrapped around setting up foreign keys when they are of the same class type. For example, I've got the following classes.

Project.cs

using System;
using System.Collections.Generic;

namespace ProjectLogic.Models
{
public class Project
{
public int Id { get; set; };
public string Name { get; set; }
public int? PmEmployeeId { get; set; }
public int? CadEmployeeId { get; set; }
public int? SalesRepEmployeeId { get; set; }
...
}
}


Employee.cs

using System;
using System.Collections.Generic;

namespace ProjectLogic.Models
{
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public string Title { get; set; }
...
}
}


Would my keys at the end of the Project Class look like this?

public Employee PmEmployee { get; set; }
public Employee CadEmployee { get; set; }
public Employee SalesRepEmployee { get; set; }


With corresponding declarations in Employee Class

public ICollection<Project> Projects { get; set; }


And the Model Builder in my DbContext class

modelBuilder.Entity<Project>(entity =>
{
// Fluent API for column properties
...
entity.HasOne(d => d.PmEmployee)
.WithMany(p => p.Projects)
.HasForeignKey(d => d.PmEmployeeId)
.OnDelete(DeleteBehavior.SetNull)
.HasConstraintName("FK_Project_Employee_PM");

entity.HasOne(d => d.CadEmployee)
.WithMany(p => p.Projects)
.HasForeignKey(d => d.CadEmployeeId)
.OnDelete(DeleteBehavior.SetNull)
.HasConstraintName("FK_Project_Employee_CAD");

entity.HasOne(d => d.SalesRepEmployee)
.WithMany(p => p.Projects)
.HasForeignKey(d => d.SalesRepEmployeeId)
.OnDelete(DeleteBehavior.SetNull)
.HasConstraintName("FK_Project_Employee_SALES");
});


I guess my biggest concern/question is how the Employee class will handle multiple relationships with the Project Class. Do I need to handle those separately as well like
public ICollection<Project> PmProjects { get; set; }
?

Answer Source

Yes, you need to have separate project collections.

In Employee, you would have:

public ICollection<Project> PmProjects { get; set; }
public ICollection<Project> CadProjects { get; set; }
public ICollection<Project> SalesProjects { get; set; }

In Project, you would have:

public Employee PmEmployee { get; set; }
public Employee CadEmployee { get; set; }
public Employee SalesRepEmployee { get; set; }

The builder would be :

modelBuilder.Entity<Project>(entity =>
{
// Fluent API for column properties
...
entity.HasOne(d => d.PmEmployee)
    .WithMany(p => p.PmProjects)
    .HasForeignKey(d => d.PmEmployeeId)
    .OnDelete(DeleteBehavior.SetNull)
    .HasConstraintName("FK_Project_Employee_PM");

entity.HasOne(d => d.CadEmployee)
    .WithMany(p => p.CadProjects)
    .HasForeignKey(d => d.CadEmployeeId)
    .OnDelete(DeleteBehavior.SetNull)
    .HasConstraintName("FK_Project_Employee_CAD");

 entity.HasOne(d => d.SalesRepEmployee)
    .WithMany(p => p.SalesProjects)
    .HasForeignKey(d => d.SalesRepEmployeeId)
    .OnDelete(DeleteBehavior.SetNull)
    .HasConstraintName("FK_Project_Employee_SALES");
 });
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download