locked
Unexpected INSERT statement conflict with the FOREIGN KEY error after create action RRS feed

  • Question

  • User1255309776 posted

    Hi,

    In my project I have models like Categories -> Subcategories -> NestedCategories which are familiar to you.

    Models

     public class Category
        {
            public Category()
            {
                Subcategories = new HashSet<Subcategory>();
            }
    
            public int Id { get; set; }
            public string Name { get; set; }
            public ICollection<Subcategory> Subcategories { get; set; }
        }
     public class Subcategory
        {
            public Subcategory()
            {
                Posts = new HashSet<Post>();
    NestedCategories = new HashSet<NestedCategory>(); } public int Id { get; set; } public string Name { get; set; } public Category Category { get; set; } public int CategoryId { get; set; } public ICollection<Post> Posts { get; set; }
    public ICollection<NestedCategory> NestedCategories { get; set; } }
    public class NestedCategory
        {
            public NestedCategory()
            {
                Posts = new HashSet<Post>();
    } public int Id { get; set; } public string Name { get; set; } public ICollection<Post> Posts { get; set; } public Subcategory Subcategory { get; set; } public int SubcategoryId { get; set; } }

    In my adminController you can see actions that create these objects

            //Category
            [HttpGet]
            public async Task<IActionResult> CreateCategory()
            {
                MenuViewModel menuModel = new MenuViewModel();
                menuModel.Categories = await _samirDbContext.Categories.ToListAsync();
                return View(menuModel);
            }
    
            [HttpPost]
            [ValidateAntiForgeryToken]
            public async Task<IActionResult> CreateCategory(Category category)
            {
                if (ModelState.IsValid)
                {
                    _samirDbContext.Categories.Add(category);
                    await _samirDbContext.SaveChangesAsync();
                    MenuViewModel menuModel = new MenuViewModel();
                    menuModel.Categories = await _samirDbContext.Categories.ToListAsync();
                    return RedirectToAction(nameof(CreateCategory));
                }
                else
                {
                    ModelState.AddModelError("", "Couldn't create");
                    return View();
                }
            }
    
        
            //Subcategory 
    
            [HttpGet]
            public async Task<IActionResult> CreateSubcategory()
            {
                MenuViewModel menuModel = new MenuViewModel();
                menuModel.Subcategories = await _samirDbContext.Subcategories.ToListAsync();
                menuModel.Categories = await _samirDbContext.Categories.ToListAsync();
                menuModel.CategoryList = await _samirDbContext.Categories.Select(a => new SelectListItem()
                {
                    Value = a.Id.ToString(),
                    Text = a.Name
                }).ToListAsync();
    
                return View(menuModel);
            }
    
            [HttpPost]
            [ValidateAntiForgeryToken]
            public async Task<IActionResult> CreateSubcategory(Subcategory subcategory)
            {
                if (ModelState.IsValid)
                {
                    _samirDbContext.Subcategories.Add(subcategory);
                    await _samirDbContext.SaveChangesAsync();
                    MenuViewModel menuModel = new MenuViewModel();
                    menuModel.Subcategories = await _samirDbContext.Subcategories.ToListAsync();
                    menuModel.Categories = await _samirDbContext.Categories.ToListAsync();
                    menuModel.Subcategory = await _samirDbContext.Subcategories.LastOrDefaultAsync();
                    menuModel.CategoryList = await _samirDbContext.Categories.Select(a => new SelectListItem()
                    {
                        Value = a.Id.ToString(),
                        Text = a.Name
                    }).ToListAsync();
    
                    return RedirectToAction(nameof(CreateSubcategory));
                }
                else
                {
                    ModelState.AddModelError("", "Couldn't create");
                    return View();
                }
    
            }
      
            //Nested
    
            [HttpGet]
            public async Task<IActionResult> CreateNested()
            {
                MenuViewModel menuModel = new MenuViewModel();
                menuModel.NestedCategories = await _samirDbContext.NestedCategories.ToListAsync();
                menuModel.Subcategories = await _samirDbContext.Subcategories.ToListAsync();
                menuModel.SubcategoryList = await _samirDbContext.Subcategories.Select(a => new SelectListItem()
                {
                    Value = a.Id.ToString(),
                    Text = a.Name
                }).ToListAsync();
    
                return View(menuModel);
            }
    
            [HttpPost]
            [ValidateAntiForgeryToken]
            public async Task<IActionResult> CreateNested(NestedCategory nested)
            {
                if (ModelState.IsValid)
                {
                    MenuViewModel menuModel = new MenuViewModel();
                    menuModel.NestedCategories = await _samirDbContext.NestedCategories.ToListAsync();
                    menuModel.Subcategories = await _samirDbContext.Subcategories.ToListAsync();
                    menuModel.NestedCategory = await _samirDbContext.NestedCategories.LastOrDefaultAsync();
                    menuModel.SubcategoryList = await _samirDbContext.Subcategories.Select(a => new SelectListItem()
                    {
                        Value = a.Id.ToString(),
                        Text = a.Name
                    }).ToListAsync();
    
                    _samirDbContext.NestedCategories.Add(nested);
                    await _samirDbContext.SaveChangesAsync();
                    return RedirectToAction(nameof(CreateNested));
                }
                else
                {
                    ModelState.AddModelError("", "Couldn't create");
                    return View();
                }
    
            }
        

    When I create subcategory, it is successfully crerated while I do the same logic for nestedCategory, I face the following SQL Exception:

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_NestedCategories_Subcategories_SubcategoryId". The conflict occurred in database "SamirDb", table "dbo.Subcategories", column 'Id'.

    While creating nestedCategpry, I do not insert anything to subcategory table, it just takes its SubcategoryId from selectListItem in LINQ like I did for subcategopry when selecting relevant categoryId. In Database, all subcategories have their Ids, and everyting is ok. So what might cause this?

    Here is the view of NestedCategory creation

    @model Samirad.Models.ViewModels.MenuViewModel
    
    <section class="container-fluid">
        <section class="buttonpanel">
            <h6 style="font-weight:bold">Yeni Kateqoriya</h6>
        </section>
        <section class="posts listed">
            <div class="post-container">
                <div class="row">
                    <div class="col-lg-3 col-md-4 col-sm-6 col-12">
                        <form method="post" asp-action="CreateNested" asp-controller="Admin">
                            <div asp-validation-summary="ModelOnly"></div>
                            <label asp-for="NestedCategory.Name" style="width: 85px">Ad</label>
                            <input asp-for="NestedCategory.Name" class="postinput" type="text"><br>
                            <span asp-validation-for="NestedCategory.Name"></span>
                            <label asp-for="NestedCategory.Subcategory.Name" style="width: 85px">Category</label>
                            <select asp-for="NestedCategory.SubcategoryId" asp-items="@Model.SubcategoryList" id="cat">
                                <option value="">Subkateqoriya secin</option>
                            </select>
                            <br>
                            <button class="push" type="submit">Yarat</button> 
                        </form>
                        <br />
                        <a asp-action="AdminAccount" asp-controller="Admin">Admin Panele qayit</a>
                    </div>
                    <div class="col-lg-3 col-md-4 col-sm-6 col-12">
                        <table style="width:280px">
                            <tr>
                                <th class="mytab">Alt-submenyu</th>
                                <th class="mytab">Dəyiş</th>
                                <th class="mytab">Sil</th>
                            </tr>
                            @foreach (NestedCategory item in Model.NestedCategories)
                            {
                                <tr>
                                    <td class="mytab">@item.Name</td>
                                    <td class="mytab"><a asp-action="EditNested" asp-controller="Admin" asp-route-id="@item.Id">Dəyiş</a></td>
                                    <td class="mytab"><a asp-action="DeleteNested" asp-controller="Admin" asp-route-id="@item.Id">Sil</a></td>
                                </tr>
                            }
                        </table>
                    </div>
                    <div class="col-lg-3 col-md-4 col-sm-6 col-12">
    
                    </div>
                    <div class="col-lg-3 col-md-4 col-sm-6 col-12">
    
                    </div>
    
                </div>
            </div>
        </section>
    
    </section>
    
    
    

    Please, help me we this issue.

    Thanks in advance

    Wednesday, August 26, 2020 1:12 PM

All replies

  • User-17257777 posted

    Hi FaridGN:

    I tried your code and I got the same error as you.

    The reason for this error is that you should receive MenuViewModel instead of NestedCategory in the CreateNested method.

    The NestedCategory received will not have any value, which leads to your SubcategoryId is 0, causing foreign key conflicts.

    You can try to modify your code to the following format:

    Your Post Action CreateNested:

    [HttpPost]
            [ValidateAntiForgeryToken]
            public async Task<IActionResult> CreateNested(MenuViewModel nested)
            {
                if (ModelState.IsValid)
                {
                    _context.NestedCategories.Add(nested.NestedCategory);
                    await _context.SaveChangesAsync();
                    return RedirectToAction(nameof(CreateNested));
                }
                else
                {
                    ModelState.AddModelError("", "Couldn't create");
                    return View();
                }
            }

    In addition, I think there is no need to create the MenuViewModel in Post action.

    Best Regards,

    Jiadong Meng

    Thursday, August 27, 2020 7:30 AM
  • User1255309776 posted

    Thanks Jiadong,

    Agree, this way works, but the question is why it has worked when creating subcategory object accepting subcategory as parameter which also must not have any value?

    I think the problem is in EF Core, which sometimes cannot get what I need to do and just acts improperly.

    Thursday, August 27, 2020 5:19 PM
  • User-17257777 posted

    Hi FaridGN,

    Maybe you can show us the MenuViewModel. It seems the parameter name doesn't match the property name.

    Change it like below:

    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> CreateNested(NestedCategory nestedCategory)

    Best Regards,

    Jiadong Meng

    Friday, August 28, 2020 7:21 AM