Entity Framework Core With Multiple Foreign Key on Same Column

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

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");
 });

EF Core creates multiple foreign key columns

It turns out, relationships can be empty - leaving decision to the framework, but it doesn't really serve Your interest. I modified my code, so there is explicit pointing at the navigation property, and EF recognized the relationship and stopped creating shadow properties for the columns:

modelBuilder.Entity<Event>()
    .HasOne<Client>(e => e.Client)
    .WithMany()
    .IsRequired()
    .OnDelete(DeleteBehavior.Cascade);

How can we add multiple foreign keys from one model to another on ASP.NET Core & EF Core & C#

Ok, something in you model doesn't look quite right...

In your User class you have a collection of Orders and an collection of a class called Library. Yet within your Order class you have a property called Library, but point that at an ApplicationUser class?

EF does support having multiple references to the same class, though you need to explicitly tell it what the FK names would be. EF's default convention is to base FK names on the type of the navigation property, not the name of the navigation property.

Take the following:

public class Order
{
    ....

    public int CreatedById { get; set; }
    public virtual ApplicationUser CreatedBy { get; set; }

    public int LastModifiedById { get; set; }
    public virtual ApplicationUser LastModifiedBy { get; set; }
}

Here by default EF would want to use "ApplicationUser_Id" or "ApplicationUserId" as a FK name for both of the two navigation properties, settling on something like "ApplicationUser_Id" and "ApplicationUser_Id1" if left to its own devices with the schema. In this situation we would need to configure it to use our desired FK properties:

[ForeignKey("CreatedBy")]
public int CreatedById { get; set; }
public virtual ApplicationUser CreatedBy { get; set; }

[ForeignKey("LastModifiedBy")]
public int LastModifiedById { get; set; }
public virtual ApplicationUser LastModifiedBy { get; set; }

or the FK attribute can be put on the navigation property:

public int CreatedById { get; set; }
[ForeignKey("CreatedById")]
public virtual ApplicationUser CreatedBy { get; set; }

public int LastModifiedById { get; set; }
[ForeignKey("LastModifiedById")]
public virtual ApplicationUser LastModifiedBy { get; set; }

The ForeignKey Attribute is a bit weird, as it represents either "I am the FK of ..." if on the FK property, or it represents "My FK is ...." if on the navigation property.

With EF Core the FK property can be left off and treated by EF as a shadow property which is recommended to avoid having two sources of truth in the entity.

[ForeignKey("CreatedById")]
public virtual ApplicationUser CreatedBy { get; set; }

[ForeignKey("LastModifiedById")]
public virtual ApplicationUser LastModifiedBy { get; set; }

In situations where you want bi-directional references in the other side of the relationship, you may need to map those out. For instance if I want a "CreatedOrders" in my ApplicationUser class:

public class ApplicationUser
{
    // ...

    public virtual ICollection<Order> CreatedOrders { get; set; } = new List<Order>();
}

Now it's generally a good idea to tell EF what to relate this back to since Order has two references to the application user. Again, this can be done on either side of the relationship. So in the case of back in our Order class:

[ForeignKey("CreatedById"), InverseProperty("CreatedOrders")]
public virtual ApplicationUser CreatedBy { get; set; }

This tells EF that CreatedBy on this record is the link to use when accessing the orders for CreatedOrders.

Back to your example it is a bit confusing why ApplicationUser would contain a collection of Libraries while an Order expects a "library" to be a User.

EF Core 2.2 - Two foreign keys to same table

public class Fixture
{
    public int Id { get; set; }
    public int HomeTeamId { get; set; }
    public int AwayTeamId { get; set; }

    [ForeignKey("HomeTeamId")]
    public virtual Team HomeTeam { get; set; }

    [ForeignKey("AwayTeamId")]
    public virtual Team AwayTeam { get; set; }
}

This way navigation will work. Also as suggested by @Ivan remove duplicate getters and setters.

Entity Framework Core - Code First - Two Foreign Keys - One Table

Apparently you have a 2 Fks to the same table in the same row so imagine if you delete a user who has created an assignment and Modified one what should happen, delete the assignment what if other users modified it this will be nulls in heir tables.

so

You need to specify the CascadeOnDelete to false using FluentApi

In the ApplicationContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<DivAssignment>()
                .HasRequired(c => c.CreatedByUser)
                .WithMany(u => u.CreatedDivAssignments)
                .HasForeignKey(c => c.CreatedByUserId)
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<DivAssignment>()
                .HasRequired(c => c.LastModifiedByUser)
                .WithMany(u => u.ModifiedDivAssignments)
                .HasForeignKey(c => c.LastModifiedByUserId)
                .WillCascadeOnDelete(false);
        }

Then add-migration then update-database

Can I reference single foreign key on multiple columns in a table? If yes, how to configure it in Entity Framework core

For multiple Navigation Properties, you need multiple Foreign Keys. EG

public class Company
{
    public int CompanyId { get; set; }

    public string CompanyName { get; set; }

    public virtual Contact FirstContact { get; set; }

    public virtual Contact SecondContact { get; set; }

    public virtual Contact ThirdContact { get; set; }
}

And let EF Core create shadow properties for your FKs or with Foreign Key properties:

public class Company
{
    public int CompanyId { get; set; }

    public string CompanyName { get; set; }

    public int FirstCotactId {get; set;}
    public virtual Contact FirstContact { get; set; }

    public int SecondCotactId {get; set;}
    public virtual Contact SecondContact { get; set; }

    public int SecondCotactId {get; set;}
    public virtual Contact ThirdContact { get; set; }
}

---

Related Topics