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

Question

So let's say I have an ApplicationUser : IdentityUser model class that has the identity relations and it has a String ID by default and roles assigned when the user signs up.

ApplicationUsers will have different roles for example Student and Library.

The Library will have a list of books while the Student will have list of orders.

Now I want to create a List of another model which will have the name Orders, but the Orders model class will have two UserIds as foreign keys from ApplicationUser.

ApplicationUser: IdentityUser 
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<Libraries> Libraries { get; set; }
    public List<Orders> Orders { get; set; }
}

Since the ApplicationUser is connected with ASPNetRoles, what I want to achieve on Orders model class is that I want to have different StudentId and LibraryId from the same table which is ApplicationUser:

public class Orders 
{ 
    [Key]
    public id OrderId {get; set;}

    [ForeignKey("StudentId")]
    public string StudentId { get; set; }
    public ApplicationUser Student {get; set;}
 
    [ForeignKey("LibraryId")]
    public string LibraryId {get; set;} 
    public ApplicationUser Library {get; set;}
}

Is there any way I can achieve this? What are the best solutions for this case? I did try ICollection and list but still same. Any suggestion about this would be great.

When I run Add-Migrations, I get this error:

System.InvalidOperationException: Unable to determine the relationship represented by navigation 'ApplicationUser.OrdersLists' of type 'ICollection'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

Thank you.

Answer 1

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.