How to handle many to many same table (User) in ASP.Net MVC 5 - Fluent API

Question

I am learning ASP.Net MVC 5 and I am stuck with one basic DB Design. So, I have one User which can refer many person in job Also, many person can apply to get themselves Referred. I have created two roles and all of that is taken care. Now, I have on class called Referral which will keep track of every instance of Referral that needs to be done.

Referral Model:

Public class Referral
{
     [Key]
        public int ReferralId { get; set; }
        public Company Company { get; set; }
        public int CompanyId { get; set; }
        public ApplicationUser User { get; set; }
        public string CandidateId { get; set; } // Application User ID of person asking for referral
        public string ReferrerId { get; set; } // Application user ID of person referring the candidate
}

ApplicationUser Model

  public class ApplicationUser : IdentityUser
    {
        public ICollection<Referral> Referrals { get; set; }
        // rest prop removed for brevity sake
    }

Now, suppose A(Referrer) refers B(Candidate). My Table row will look like below.

ReferralId     CompanyId     CandidateId   ReferrerId

1              1             B             A

So, far so good. But I want to establish FK relationship on Referral table. I am new to Fluent API but I tried like below.

    // one candidate can have many referrals
    dBModelBuilder.Entity<ApplicationUser>()
    .HasMany(u => u.Referrals)
    .WithRequired(u => u.User)
    .HasForeignKey(u => u.CandidateId)
    .WillCascadeOnDelete(false);

    //one referrar can have many referrals
    dBModelBuilder.Entity<ApplicationUser>()
    .HasMany(u => u.Referrals)
    .WithRequired(u => u.User)
    .HasForeignKey(u => u.ReferrerId);

But EF respects only one relationship. Why both the foreign key relationhip is not getting set. If I comment out one then other works, and vice versa. But keeping them together as shown never works.

Expected Behaviour: I expected to have two FK relationship. Once I have that then I can work accordingly. Please guide me here. I am new to all this.

You need separate Referrals & User properties for each relationship. — SLaks
– SLaks, Commented Aug 20, 2017 at 16:42
Sir, can you kindly elaborate what you mean. Let me know if I need to explain my requirement clearly. Also, I hope you understood what I wanted to achieve. I thought that If I set the relationship as above, I will use referrerId and canddiateId acccordingly to fetch/set the records. Please help me. — Unbreakable
– Unbreakable, Commented Aug 20, 2017 at 16:44
Even with current design everything will work, as long as I set the ID correctly. But I wanted to have proper FK constraint. Am I making sense? — Unbreakable
– Unbreakable, Commented Aug 20, 2017 at 16:45
@SLaks: Sir, I added one DB Design Image. Can you kindly see it once. — Unbreakable
– Unbreakable, Commented Aug 20, 2017 at 16:51

Ivan Stoev · Accepted Answer · 2017-08-20 17:28:46Z

1

As @SLaks mentioned in the comments, in order to have two relationships, you need two havigation properties (one for each FK).

So in the one side replace Referrals property with something like this:

public class ApplicationUser : IdentityUser
{
    // ...
    public ICollection<Referral> ReferrerOf { get; set; }
    public ICollection<Referral> CandidateOf { get; set; }
}

at many side replace the User property with:

public class Referral
{
    // ...
    public ApplicationUser Candidate { get; set; }
    public ApplicationUser Referrer { get; set; }
}

and correlate them with fluent API:

modelBuilder.Entity<ApplicationUser>()
    .HasMany(u => u.CandidateOf) // <--
    .WithRequired(r => r.Candidate) // <--
    .HasForeignKey(r => r.CandidateId)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<ApplicationUser>()
    .HasMany(u => u.ReferrerOf) // <--
    .WithRequired(r => r.Referrer) // <--
    .HasForeignKey(r => r.ReferrerId);

The names of the navigation properties don't really matter as soon as you correlate them correctly.

answered Aug 20, 2017 at 17:28

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Unbreakable Over a year ago

Worked like a charm. While doing experimentation. I did add different property on Referral class. But I never altered the ApplicationUser. I needed to add different property there also.

Collectives™ on Stack Overflow

How to handle many to many same table (User) in ASP.Net MVC 5 - Fluent API

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related