Writing an API to POST to a many-to-many relationship

Question

Good evening all

This is probably an easy question for experienced developers, but I am new to C#, ASP.Net and Entity Framework. I have two tables - AppUser and CompanySettings. There is a many-to-many relationship between these 2 tables.

    public class AppUser
    {
        public int Id { get; set; }
        public string UserName { get; set; }
        public byte[] PasswordHash { get; set; }
        public byte[] PasswordSalt { get; set; }
        public string KnownAs { get; set; }
        public DateTime Created { get; set; } = DateTime.Now;
        public DateTime LastActive {get; set;} = DateTime.Now;
        public ICollection<Photo> Photos { get; set; }

        public virtual ICollection<AppUserCompanySettings> AppUserCompanySettings { get; set; }
    }

public class CompanySettings
    {
        public int Id { get; set; }
        public string CompanyName { get; set; }
        public string CompanyRegistrationNumber { get; set; }
        public bool isActive { get; set; }
        public bool isArchived { get; set; }
        public virtual ICollection<AppUserCompanySettings> AppUserCompanySettings { get; set; }

    }

I also created the join table:

public class AppUserCompanySettings
    {
        public int Id { get; set; }
        public int AppUserId { get; set; }
        public virtual AppUser AppUser { get; set; }
        public int CompanySettingsId { get; set; }
        public virtual CompanySettings CompanySettings { get; set; }
    }

My API now looks like this:

public async Task<ActionResult<CompanySettings>> AddNewCompany(CompanySettings companySettings)
        {
            if (await CompanyExists(companySettings.CompanyRegistrationNumber)) return
                BadRequest("Company already exists.  Please contact our Support team for assistance");

            var user = new AppUser {
                Id = 1
            };

            var company = new CompanySettings
            {
                CompanyName = companySettings.CompanyName,
                CompanyRegistrationNumber = companySettings.CompanyRegistrationNumber,
                isActive = companySettings.isActive,
                isArchived = companySettings.isArchived,
            };

            _context.CompanySettings.Add(company);
            _context.Update(user);
            await _context.SaveChangesAsync();

            return company;
        }

A user registers - and this writes username and password to the AppUser table. He does not need to create a company on registration.

However, when he wants to add a Company (that writes to Company Settings), how do I write an API to post to the CompanySettings table as well as the AppuserCompanySettings table that EF created?

At the moment, if I call the API, it creates the company. But it does not do the join to the AppUserCompanySettings.

How do I update the Join table?

Answer 1

Ensure you heve this in your DbContext configuration:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<AppUser>()
        .HasMany(x => x.AppUserCompanySettings )
        .WithOne(x => x.AppUser)
        .HasForeignKey(x => x.AppUserId);

    modelBuilder.Entity<CompanySettings>()
        .HasMany(x => x.AppUserCompanySettings)
        .WithOne(x => x.CompanySettings)
        .HasForeignKey(x => x.CompanySettingsId);

    modelBuilder.Entity<AppUserCompanySettings>()
        .HasKey(x => new {x.AppUserId, x.CompanySettingsId});

}

Replace public int Id { get; set; } from your AppUserCompanySettings, since you only have 2 foreign keys in your table, which act like a primary

After this piece of code:

var user = new AppUser 
{
    Id = 1
};

var company = new CompanySettings
{
    CompanyName = companySettings.CompanyName,
    CompanyRegistrationNumber = companySettings.CompanyRegistrationNumber,
    isActive = companySettings.isActive,
    isArchived = companySettings.isArchived,
};

You have to do this:

// adding relationship between entities here
user.AppUserCompanySettings.Add(new AppUserCompanySettings 
{
    AppUser = user, 
    CompanySettings = company
});

// if you haven't created a new user but used already existing - 
// replace Add with Update
_context.Add(user);
_context.SaveChanges();

Also modify your Collections in AppUser and CompanySettings like this

public virtual ICollection<AppUserCompanySettings> AppUserCompanySettings { get; set; } = new List<AppUserCompanySettings>();