EF CORE multiple properties that map to the same table

user_5139847 0
Viewed 1262

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: enter image description here 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?

entity-framework-core asp.net-core database .net c#
1 Answers

From the relationshipn 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 enter image description here

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

user_102018500
Related