Summary
I have two tables, a Users table and a Sponsorships table. The Sponsorships table has two foreign keys to the Users table. The relationship here is that a user can be sponsored by a several different users, and a user can sponsor several different users. I want to use this information to fill a list of Sponsors for each user.
Context
I am currently working on a project where I have to create an API that uses an existing database being used in a different project, and it has been decided that .NET 5 will be used. The already existing project has thousands of users, so I cannot make any changes to the database. For the sake of brevity I have removed code that is not relevant to the problem.
Database
The database I am connected to has many tables, but only two of them are relevant for my problem, Users and Sponsorships. Below I have illustrated what these tables look like and their relationship, removing columns that are not relevant to this problem. These are columns that do not provide a relationship between the Users and Sponsorships tables in any way.
Users
| UserId |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
Sponsorships
| UserId | SponsorUserId |
|---|---|
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 2 | 3 |
| 2 | 4 |
The two columns in Sponsorships are Primary Keys, and Foreign Keys for the UserId in the Users Table, so we’re dealing with composite keys. There is one more column in this table that is also a primary key and a foreign key, which is called RolesId. It is a foreign key for a different table, but that shouldn’t be relevant because it has no relation to the Users table. Furthermore, Sponsorships table does not have an Id column that is auto incremented, only these three primary keys and an unrelated column which is of type DateTime. I have decided to not edit out references to RolesId to keep the relevant code as close to the original as possible.
A user can be sponsored by, or can sponsor a multiple different users. Using this information it should be possible to create two lists for each user, one list that contains all the users that this user has sponsored, and one list that contains all the users that this user has been sponsored by.
My code
My code so far consists of a class that inherits DbContext, two entity classes, one for User and Sponsorship which has two corresponding configuration classes that implements the IEntityTypeConfiguration interface. One Repository that accesses the database using the DbSets defined in the class that inherits DbContext. And one controller that makes this information available to a consumer. Below you can find these classes, edited for brevity, to make my solution so far much more clear. Some class names and properties have been edited to avoid copy pasting proprietary code.
public class MyContext: DbContext, IMyContext
{
public DbSet<User> Users { get; set; }
public DbSet<Sponsorship> Sponsorships { get; set; }
public MyContext(DbContextOptions<MyContext> options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfiguration(new SponsorshipConfiguration());
modelBuilder.ApplyConfiguration(new UserConfiguration());
}
}
public class User
{
public int Id { get; set; }
public int UserId { get; set; }
public List<Sponsorship> AffiliateRelations { get; set; }
public List<Sponsorship> SponsorRelations { get; set; }
public List<User> Sponsors { get; set; }
}
public class Sponsorship
{
public int RoleId { get; set; }
public int UserId { get; set; }
public int SponsorUserId { get; set; }
public User User { get; set; }
public User Sponsor { get; set; }
}
public class SponsorshipConfiguration : IEntityTypeConfiguration<Sponsorship>
{
public void Configure(EntityTypeBuilder<Sponsorship> builder)
{
builder.ToTable("Sponsorships");
builder.HasKey(s => new { s.RoleId, s.UserId, s.SponsorUserId });
builder.Property(s => s.RoleId).HasColumnName("Roles_Id");
builder.HasOne(s => s.Sponsor)
.WithMany(u => u.SponsorRelations)
.HasForeignKey(s => s.SponsorUserId);
builder.HasOne(s => s.User)
.WithMany(u => u.AffiliateRelations)
.HasForeignKey(s => s.UserId);
}
}
public class UserConfiguration : IEntityTypeConfiguration<User>
{
public void Configure(EntityTypeBuilder<User> builder)
{
builder.ToTable("Users");
builder.HasKey("Id");
}
}
public class UserRepository: BaseRepository, IUserRepository
{
public UserRepository(IMyContext context) : base(context)
{
}
public async Task<List<Sponsorship>> GetSponsorships()
{
return await Context.Sponsorships.Include(s => s.Sponsor).Include(s => s.User).OrderBy( s => s.UserId).ToListAsync();
}
public async Task<List<User>> GetUsersAsync()
{
return await Context.Users.ToListAsync();
}
public async Task<List<User>> GetUsersWithSponsors()
{
return await Context.Users.Include(u => u.Sponsors).ToListAsync()
}
}
If necessary I can also include the Controller code, but all this does is call the various methods declared in the UserRepository, later on I will add a UserService that the controller can use instead.
The problem
GetUsersWithSponsors() does execute successfully, but there is missing information inside the Sponsors list. Here is an example of that using the data defined above. The user object with UserId = 1, has a list of sponsors that only contains one element, when it should have 3. I have confirmed this by querying the database and making sure that the Sponsorships table does have 3 rows when selecting all rows with that UserId.
I’ve looked at a lot of different questions here on Stack Overflow that seem to solve a very similar problem, but their solutions don’t work for me. Like for example Entity Framework Code First – two Foreign Keys from same table I can only guess that the way I have defined the relationships using Fluent API inside my two configuration classes is flawed and/or missing information. I would very much appreciate some help with this. Thank you.
As a side note, I have been made aware that Microsoft suggests using data annotations before Fluent API. I don’t know if that’s true, but I’ll look into that and think about converting the Fluent API code. However, I would appreciate it if the solution to this problem was given using Fluent API or both if it is necessary to alter or add configuration code.
Advertisement
Answer
Everything except this property of the User class
public List<User> Sponsors { get; set; }
is classic many-to-many self relationship with explicit join entity (in your case the Sponsorship is a joining entity with additional fields), represented by the other two collection navigation properties.
This property though needs attention. Without additional fluent configuration it is treated as a separate one-to-many self relationship (most likely treating User.UserId property which looks strange in the presented model as a FK and joining to it), which is not what you want. Instead, it has to be configured as skip navigation (a new concept in EF Core 5.0 used to implement many-to-many with implicit join entity) and tied to the existing Sponsorship “regular” relationships as explained (along with example) in the Join entity type configuration section of the EF Core documentation.
But in order to do that you need to define another collection navigation property (the “inverse” of the one in question) because that’s a current requirement of the many-to-many mapping.
So let add one – the name doesn’t matter, just the type – IEnumerable<User> or derived:
public List<User> Sponsoring { get; set; }
and use the following fluent configuration inside UserConfiguration (remove the relationship configuration code from SponsorshipConfiguration since they must/will be configured as part of the User relationship configuration below):
builder
.HasMany(e => e.Sponsors)
.WithMany(e => e.Sponsoring)
.UsingEntity<Sponsorship>(
j => j
.HasOne(e => e.Sponsor)
.WithMany(e => e.SponsorRelations)
.HasForeignKey(e => e.SponsorUserId),
j => j
.HasOne(e => e.User)
.WithMany(e => e.AffiliateRelations)
.HasForeignKey(e => e.UserId)
);
And that’s all. Now both
Context.Users.Include(u => u.Sponsors)
and
Context.Users.Include(u => u.Sponsoring)
and of course
Context.Users.Include(u => u.Sponsors).Include(u => u.Sponsoring)
can be used to obtain (correctly!) the information you need.