EF CORE multiple properties that map to the same table

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

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: 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?

1
2
3/13/2020 10:45:20 AM

Accepted Answer

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.

1
3/16/2020 2:37:11 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow