Background
- I need to create an endpoint which will fetch Out-of-Office (OOO) details for a user
- Each OOO detail will also have an alternate contact detail which also is of type user
- One user can have many OOO details, however each OOO detail will belong to only one main and one alternate contact (of type user)
For this purpose I have created two entity objects which mirror their respective table relationships
public class User
{
public int Id {get; set;} //Primary Key
public string Name {get; set;}
public virtual ICollection<OOOSchedule> Schedules {get; set;}
}
public class OOOSchedule
{
public int Id {get; set;} //Primary Key
public int UserId {get; set;} //Foreign key reference from User table above (column Id)
public int AlternateUserId {get; set;} //Another foreign key reference from User table above (column Id)
public DateTime StartDate {get; set;}
public DateTime EndDate {get; set;}
public virtual User User {get; set;}
public virtual User AlternateUser {get; set;}
}
The thing is I am able to fetch the OOO schedules (collection) for a user with the corresponding user data also populated in the OOO schedule object. However try as I might I am not able to get the AlternateUser data in the OOO schedule object.
I have tried like this using Fluent API (have tried multiple variations of this):
modelBuilder.Entity<OOOSchedule>()
.HasOne(s => s.User)
.WithMany(o => o.Schedules);
modelBuilder.Entity<OOOSchedule>()
.HasOne(s => s.AlternateUser)
.WithMany(o => o.Schedules);
modelBuilder.Entity<User>()
.HasMany(o => o.Schedules)
.WithOne(u => u.User);
I am sure that the issue is due to an incorrect relationship in the DbContext but since my knowledge on EF is pretty basic I am not able to get it right.
Can someone please help me with this?