使用外键将ASP.NET EF Core插入多个表 [英] ASP.NET EF Core inserting into multiple tables with foreign key

问题描述

我真的无法弄清楚.我经常遇到此错误，并且不确定如何修改代码以支持1对多.到目前为止，我已阅读的示例很难理解.有些人建议修改流畅的API或模型，甚至修改控制器.

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.

错误:

SqlException:当IDENTITY_INSERT设置为OFF时，无法为表'CompetitionCategory'中的标识列插入显式值.
System.Data.SqlClient.SqlCommand +<> c.b__122_0(任务结果)

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:更新条目时发生错误.有关详细信息，请参见内部异常.

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

Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection连接，CancellationToken cancelledToken)

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

Competition模型类:

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模型类:

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! :)

推荐答案

好吧，我可能会看到2个问题.

Well, I potentially see 2 issues.

1. 您正在异步执行此操作，因此有可能尝试在完成第一个数据库更改之前保存第二个数据库更改.

2. 您应该先创建完整的模型，然后添加它，在这种情况下，第二个添加应该是更新，因为您已经添加了第一个并保存了更改，因此最好创建模型完全包含所有数据并添加它，然后保存更改.

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();

这篇关于使用外键将ASP.NET EF Core插入多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！