EF CORE multiple properties that map to the same table

Question

I have this data model that I cant make it work.
One User can be associated to a Company AND multiple at the same time.
However a Company has multiple Users
Image example below: I have tried to set relations on the modelBuilder like this:

 builder
        .HasMany(c => c.Users)
        .WithOne(u => u.CurrentAssociatedCompany)            
        .HasForeignKey(u => u.CurrentAssociatedCompanyId)
        .IsRequired();

But i get an error in the migration

Value cannot be null. (Parameter 'key')

Even researched about InverseProperty but no luck there either.
My question, is it possible to achieve this or should I try a different approach?

Answer 1

From the relationship between User and Company, you could design the model as shown:

Model

public class User
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public int Age { get; set; }

    public int CurrentAssociatedCompanyId { get; set; }
    public Company CurrentAssociatedCompany { get; set; }

    public ICollection<UserCompany> UserCompanies { get; set; }
}
public class Company
{
    public int Id { get; set; }
    public string CompanyName { get; set; }

    public ICollection<UserCompany> UserCompanies { get; set; }
}
public class UserCompany
{
    public int UserId { get; set; }
    public User User { get; set; }

    public int CompanyId { get; set; }
    public Company Company { get; set; }
}

DbContext

public class MVCDbContext : DbContext
{
    public MVCDbContext(DbContextOptions<MVCDbContext> options) : base(options)
    { }

    public DbSet<User> User { get; set; }
    public DbSet<Company> Company { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserCompany>()
            .HasKey(uc => new { uc.UserId,uc.CompanyId });

        modelBuilder.Entity<UserCompany>()
            .HasOne(uc=>uc.Company)
            .WithMany(c => c.UserCompanies)
            .HasForeignKey(uc=>uc.CompanyId)
            .OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity<UserCompany>()
                .HasOne(uc => uc.User)
                .WithMany(u => u.UserCompanies)
                .HasForeignKey(uc => uc.UserId)
                .OnDelete(DeleteBehavior.Restrict);
    }
}

Controller

public IActionResult GetUserData(int? id)
{
    var userData = _context.User
                .Include(u => u.CurrentAssociatedCompany)
                .Include(u => u.UserCompanies)
                    .ThenInclude(uc => uc.Company)
                .Where(u=>u.Id==id)
                .Select(u => new
                {
                    UserId = u.Id,
                    UserName = u.UserName,
                    Age = u.Age,
                    CurrentAssociatedCompany = u.CurrentAssociatedCompany.CompanyName,
                    AssociatedCompanies = u.UserCompanies.Select(uc => new {
                        Id=uc.Company.Id,
                        CompanyName=uc.Company.CompanyName
                    }).ToList()
                });

    return Json(userData);
}

Result

For many-to-many relationship in EF Core, you could refer to the official doc.