ASP.NET EF Core inserting into multiple tables with foreign key

Question

I really cannot figure this out. I am constantly hitting this error and I am unsure how to modify the code to support 1 to many. The examples I have read up so far are quite difficult to understand. Some suggest modifying fluent API or the model or even the controller.

Error:

SqlException: Cannot insert explicit value for identity column in table 'CompetitionCategory' when IDENTITY_INSERT is set to OFF.
System.Data.SqlClient.SqlCommand+<>c.b__122_0(Task result)

DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)

Competition model class:

public class Competition
{
        [Key]
        public int ID { get; set; }
        [Required]
        [Display(Name = "Competition Name")]
        public string CompetitionName { get; set; }
        [Required]
        public string Status { get; set; }

        public ICollection<CompetitionCategory> CompetitionCategories { get; set; }
}   

CompetitionCategory model class:

public class CompetitionCategory
{
        [Key]
        public int ID { get; set; }
        [Required]
        [Display(Name = "Category Name")]
        public string CategoryName { get; set; }

        [ForeignKey("CompetitionID")]
        public int CompetitionID { get; set; }
}

After some tinkering, I realised to pass the list to the controller I should use a view model as shown here:

public class CategoriesViewModelIEnumerable
{
        public Competition competition { get; set; }
        public CompetitionCategory competitionCategory { get; set; }

        // From Microsoft
        public IEnumerable<string> SelectedCategories { get; set; }

        public List<SelectListItem> CategoriesList { get; } = new List<SelectListItem>
        {
            new SelectListItem { Value = "xxx", Text = "xxx" },
            new SelectListItem { Value = "yyy", Text = "yyy" },
            new SelectListItem { Value = "zzz", Text = "zzz" },
         };
}

I can successfully pass the data to my controller and read/print it on the console. However I am hitting the last error which is to save the 2nd category onwards into the database, probably due to some primary key/foreign key restriction.

I can currently only save the first item in the list into the database.

public async Task<IActionResult> Create(CategoriesViewModelIEnumerable model)
{
    if (ModelState.IsValid)
    {
        CompetitionCategory competitionCategory = new CompetitionCategory();
        _context.Add(model.competition);
        await _context.SaveChangesAsync();

        foreach (var CategoryName in model.SelectedCategories)
        {
            competitionCategory.CategoryName = CategoryName;
            competitionCategory.CompetitionID = model.competition.ID;
            _context.Add(competitionCategory);
            await _context.SaveChangesAsync();
        }

        await _context.SaveChangesAsync();
    }
}

Appreciate your help a lot! :)

Answer 1

Edit: This answer works in case it is not clear

Thank you very much to Kelso Sharp for pointing me in the right direction. Fixed it by editing the controller.

For future reference for other users:

Basically you just need to add in your "main model", in this case Competition.

Since Competition already have a Collection of CompetitionCategory, initialize it and add each CompetitionCategory to the Collection as shown in the for loop.

Lastly, add the model Competition into the Database. I am assuming EF Core will add the Collection data to the CompetitionCategory table for you automatically with the foreign key mapping done. (Someone please edit this if it's wrong)

Working Controller:

public async Task<IActionResult> Create(CategoriesViewModelIEnumerable model)
{
    if (ModelState.IsValid)
    {
    model.competition.CompetitionCategories = new Collection<CompetitionCategory>();
        foreach (var CategoryName in model.SelectedCategories)
        {
             model.competition.CompetitionCategories.Add(new CompetitionCategory { CompetitionID=model.competition.ID, CategoryName=CategoryName});
        }
        _context.Add(model.competition);
        await _context.SaveChangesAsync();
    }
}

Answer 2

To fix the error, you might consider storing explicit ids into the foreign key, but EF Core sets up a primary key on a table with identity insert set to on. To explicitly turn it off, in your DbContext you can override the method OnModelCreating if you have not already done so and put in this line:

protected override OnModelCreating(DbModelBuilder modelBuilder){
//some more code if necessary - define via Fluent api below
modelBuilder.Entity<CompetitionCategory>().Property(t => t.CompetitionID)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
//define also other ids to not have databasegeneration option set to identity to allow explicit idsif required
}

Also you might consider first saving the Competition and CompetitionCategory inside a transaction so you can rollback or commit the transaction if error is encountered or not, using TransactionScope in EF. The error you get anyways is due to you setting in an explicit id and EF default will set up an ID column with identity insert if not explicitly stated so otherwise. You can use the databasegeneratedoption attribute if this is more convenient. See DatabaseGeneratedOption attribute

Answer 3

I believe the issue (haven't actually tested it) is that you're instantiating your CompetitionCategory entity once and then trying to add that single instance to the model for each iteration in the foreach loop.

You should be creating a new CompetitionCategory instance for each iteration and then adding each new entitiy to the model:

foreach (var CategoryName in model.SelectedCategories) 
{ 
  CompetitionCategory competitionCategory = new CompetitionCategory();
  competitionCategory.CategoryName = CategoryName;
  competitionCategory.CompetitionID = model.competition.ID;
  _context.Add(competitionCategory);
}

await _context.SaveChangesAsync();

Answer 4

Well, I potentially see 2 issues.

1. You are doing this asynchronously so it is possible that it is trying to save the second database changes before the first ones are completed.

2. You should be creating the complete model then adding it, the second add in this case should be an update, because you have already added it with the first and saved changes, so it would be better to create your model completely with all of the data and add it and then save changes.

      CompetitionCategory competitionCategory = new CompetitionCategory();
      foreach (var CategoryName in model.SelectedCategories)
      {
          competitionCategory.CategoryName = CategoryName;
          competitionCategory.CompetitionID = model.competition.ID;
   
       }
      _context.Add(model.competition);
       await _context.SaveChangesAsync();