Entity Framework Code First - two Foreign Keys from same table

Alex Dunlop

I have the following models:

public class User
{
[Key]
public int UserId { get; set; }
public string Name { get; set; }
public string LastName { get; set; }
public string? ImageUrl { get; set; }

    //Navigation properties
    public virtual List Requests { get; set; }
    public virtual List Messages { get; set; }
}

public class Request
{
[Key]
public int RequestId { get; set; }
public DateTime RequestTime { get; set; } = DateTime.Now;
public bool? AcceptStatus { get; set; }

    //Navigation properties
    public User TargetUserRef { get; set; }
    public User SenderUserRef { get; set; }
}

I want the Request table to have two foreign keys. I tried to use the following fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasOne(m => m.SenderUserRef)
.WithMany(t => t.Requests)
.HasForeignKey(m => m.RequestId)
.OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity()
                    .HasOne(m => m.TargetUserRef)
                    .WithMany(t => t.Requests)
                    .HasForeignKey(m => m.RequestId)
                    .OnDelete(DeleteBehavior.Restrict);
    }

When I try to create migration, the following error is shown:

Quote:

Cannot create a relationship between 'User.Requests' and 'Request.TargetUserRef' because a relationship already exists between 'User.Requests' and 'Request.SenderUserRef'. Navigations can only participate in a single relationship. If you want to override an existing relationship call 'Ignore' on the navigation 'Request.TargetUserRef' first in 'OnModelCreating'.

How can I solve this problem?

Lost User

This is where you build the (test) "database first" and have EF generate the model it undertstands; instead of having to guess what your "model building" code is trying to do. Then you can try again with having some idea of what works.

"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I

jschell

To me this is why relying on these sorts of APIs leads to problems. Any API that attempts to simplify something always reduces complexity by removing features. That is of course the definition of simplification. That works only as long as the needed usage is in fact simple. And more importantly that it is expected that over time it will remain simple. If not then it will start to fail at some time. Then attempting to then use the API to solve the problem becomes a mess. Or perhaps even worse the API itself will introduce odd constructs with perhaps even odd restrictions and rules in an attempt to work around the original limitations. Those themselves will lead to problems over time.

Richard Deeming

You can't use the same collection navigation property to represent the requests where the user is the sender and the requests where the user is the target. You also can't use the RequestId property as the foreign key to the users table - you're not creating a one-to-one relationship, so the request ID and the user ID will be different.

public class User
{
...
public List SentRequests { get; set; }
public List ReceivedRequests { get; set; }
...
}

...

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasOne(m => m.SenderUserRef)
.WithMany(t => t.SentRequests)
.OnDelete(DeleteBehavior.Restrict);

modelBuilder.Entity()
            .HasOne(m => m.TargetUserRef)
            .WithMany(t => t.ReceivedRequests)
            .OnDelete(DeleteBehavior.Restrict);

}

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer