Cannot insert explicit value for identity column in table 'Products Discussed' when IDENTITY_INSERT is set to OFF

Question

I get the error mentioned in the title.

Important

The form submits the data into the database successfully, but then it tries to submit again with another query that tries to simultaneously insert a Product Discussed Id and then fails/crashes. I have no queries like this in my project. What could cause that?

Cannot insert explicit value for identity column in table 'Products Discussed' when IDENTITY_INSERT is set to OFF.

I have checked out multiple forum posts, and scoured the internet, and I still cannot fix the issue.

A contact report will be submitted to the database, and a product discussed table will also be updated. The contact report worked perfectly, but the error occurred with the product discussed table.

Please see all the code below:

ContactReportController:

[HttpGet]
public async Task<IActionResult> Submit()
{
    var contactReport = new ContactReport
    {
        ProductsDiscussed = new List<ProductDiscussed>
        {
            new ProductDiscussed() // Add one default item
        },
        Report_Date = DateTime.Now
    };

    // Load necessary data for the form
    await LoadFormDataAsync();
    return View(contactReport);
}

[HttpPost]
public async Task<IActionResult> Submit(ContactReport contactReport)
{
    if (User?.Identity?.IsAuthenticated == true)
    {
        if (ModelState.IsValid)
        {
            try
            {
                _logger.LogInformation("Model is valid. Adding contact report to context.");

                // Add the ContactReport to the context
                _context.ContactReports.Add(contactReport);
                await _context.SaveChangesAsync();

                // Ensure EF Core recognizes each product as a new entity
                foreach (var product in contactReport.ProductsDiscussed)
                {
                    // Set the foreign key
                    product.ReportID = contactReport.Report_Id;

                    // Mark the product as a new entity
                    _context.ProductsDiscussed.Add(product);
                }

                await _context.SaveChangesAsync();

                return RedirectToAction("Index");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "An error occurred while saving the contact report.");
                ModelState.AddModelError("", "An error occurred while saving the report. Please try again.");
            }
        }
        else
        {
            _logger.LogWarning("Model state is invalid.");

            foreach (var error in ModelState.Values.SelectMany(v => v.Errors))
            {
                _logger.LogWarning(error.ErrorMessage);
            }
        }
    }
    else
    {
        _logger.LogWarning("User is not authenticated.");
        return Unauthorized();
    }

    // Reload data if ModelState is invalid
    await LoadFormDataAsync(); // Method to load the data needed for the form

    return View(contactReport);
}

private async Task LoadFormDataAsync()
{
    var staffList = await _context.Staff.OrderBy(s => s.Name).ToListAsync();
    ViewBag.StaffList = new SelectList(staffList, "StaffId", "Name");

    var countries = await _context.Countries.OrderBy(c => c.CountryName).ToListAsync();
    ViewBag.Countries = new SelectList(countries, "CountryCode", "CountryName");

    var productCategories = await _context.ProductCategories.OrderBy(c => c.CategoryName).ToListAsync();
    ViewBag.ProductCategories = new SelectList(productCategories, "CategoryID", "CategoryName");

    var customers = await _context.Customers.ToListAsync();
    var customerList = customers
        .Select(c => new
        {
            c.CustomerId,
            DisplayName = string.IsNullOrEmpty(c.City)
                ? $"{c.Name} {c.Surname} - {c.Country}"
                : $"{c.Name} {c.Surname} - {c.Country}, {c.City}",
            SortKey = string.IsNullOrEmpty(c.Name) ? c.Surname : $"{c.Name} {c.Surname}"
        })
        .OrderBy(c => c.SortKey)
        .ToList();
    ViewBag.CustomerList = new SelectList(customerList, "CustomerId", "DisplayName");
}

Submit.cshtml:

<div id="productsContainer">
    @for (int i = 0; i < Model.ProductsDiscussed.Count; i++)
    {
        <div class="card mb-3">
            <div class="card-body">
                <div class="form-group">
                    <label asp-for="ProductsDiscussed[i].BrandProductCategoriesID">Product Category</label>
                    <select asp-for="ProductsDiscussed[i].BrandProductCategoriesID" class="form-control" asp-items="ViewBag.ProductCategories">
                        <option value="">Select a product category</option>
                    </select>
                    <span asp-validation-for="ProductsDiscussed[i].BrandProductCategoriesID" class="text-danger"></span>
                </div>

                <div class="form-group">
                    <label asp-for="ProductsDiscussed[i].Sample">Sample Used</label>
                    <select asp-for="ProductsDiscussed[i].Sample" class="form-control">
                        <option value="false">No</option>
                        <option value="true">Yes</option>
                    </select>
                    <span asp-validation-for="ProductsDiscussed[i].Sample" class="text-danger"></span>
                </div>
            </div>
        </div>
    }
</div>

<button type="button" id="addProductButton" class="btn btn-secondary mt-3">Add Another Product</button>

SQL code:

[Products Discussed ID] [int] IDENTITY(1,1) NOT NULL,

ContactReport.cs:

public ContactReport()
{
     ProductsDiscussed = new List<ProductDiscussed>();
}

// Text in-between

[NotMapped]
public List<ProductDiscussed> ProductsDiscussed { get; set; } = new List<ProductDiscussed>();

ProductDiscussed.cs:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Column("Products Discussed ID")]
public int ProductsDiscussedID { get; set; } // Manually managed ID

Error after submitting report:

CustomerDatabase.Controllers.ContactReportController: Information: Model is valid. Adding contact report to context.

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand [Parameters=[@p0='SUR238' (Nullable = false) (Size = 4000), @p1='test' (Nullable = false) (Size = 4000), @p2='test' (Nullable = false) (Size = 4000), @p3='' (Nullable = false) (Size = 4000), @p4='test' (Nullable = false) (Size = 4000), @p5='2024-08-12T00:00:00.0000000', @p6='test' (Nullable = false) (Size = 4000), @p7='STA11' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [Contact Report] ([Customer ID], [Feedback], [Follow Up], [Location], [Notes], [Report Date], [Report Description], [Staff Id])
OUTPUT INSERTED.[Id]
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7);

Exception thrown: 'Microsoft.Data.SqlClient.SqlException' in Microsoft.Data.SqlClient.dll
Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand [Parameters=[@p0='18', @p1='3', @p2='195', @p3='False'], CommandType='Text', CommandTimeout='30']

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'Products Discussed' when IDENTITY_INSERT is set to OFF.

Edit 1:

ContactReport.cs:

    using Microsoft.AspNetCore.Mvc.Rendering;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    
    namespace CustomerDatabase.Models
    {
        [Table("Contact Report")]
        public class ContactReport
        {
            public ContactReport()
            {
                ProductsDiscussed = new List<ProductDiscussed>();
            }
    
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            [Column("Id")]
            public int Report_Id { get; set; }
    
            [Required]
            [Column("Report Date")]
            public DateTime Report_Date { get; set; }
    
            [Required]
            [Column("Report Description")]
            public string Report_Description { get; set; } = string.Empty;
    
            [Required]
            [Column("Customer ID")]
            public string Customer_ID { get; set; } = string.Empty;
    
    
            [Column("Location")]
            public string Location { get; set; } = string.Empty;
    
            [Required]
            [Column("Follow Up")]
            public string Follow_Up { get; set; } = string.Empty;
    
            [Required]
            [Column("Feedback")]
            public string Feedback { get; set; } = string.Empty;
    
            [Required]
            [Column("Notes")]
            public string Notes { get; set; } = string.Empty;
    
            [Required]
            [Column("Staff Id")]
            public string Staff_Id { get; set; } = string.Empty;
    
            [NotMapped]
            public List<SelectListItem>? StaffList { get; set; }
    
            // New properties for location
            [NotMapped]
            public string Country { get; set; } = string.Empty;
    
            [NotMapped]
            public string? Province { get; set; }
    
            [NotMapped]
            public string? City { get; set; }
    
            [NotMapped]
            public string? Area { get; set; }
    
            public virtual List<ProductDiscussed> ProductsDiscussed { get;} = new List<ProductDiscussed>();
        }
    }

ProductsDiscussed.cs

using CustomerDatabase.Models;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("Products Discussed")]
public class ProductDiscussed
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("Products Discussed ID")]
    public int ProductsDiscussedID { get; protected set; }

    [Required]
    [Column("Report ID")]
    public int ReportID { get; set; }
    [ForeignKey("ReportID")]
    public ContactReport? ContactReport { get; set; }

    [Required]
    [Column("Brand Product Categories")]
    public int BrandProductCategoriesID { get; set; }
    [ForeignKey("BrandProductCategoriesID")]
    public BrandProductCategory? BrandProductCategory { get; set; }

    [Required]
    public bool Sample { get; set; }
}

Answer 1

For one, the ProductsDiscussed collection should not be marked as [NotMapped] This tells EF to ignore the collection. The first step is to fix the navigation property as well as the PK:

[NotMapped]
public List<ProductDiscussed> ProductsDiscussed { get; set; } = new List<ProductDiscussed>();

should be:

public virtual ICollection<ProductDiscussed> ProductsDiscussed { get; } = [];

No setter. Collection navigation properties should expose no setter. If you want to initialize a default product that is fine, use Add(). Setters can end up leading to problems where tracked entities have a setter called resulting in breaking the change tracking, resulting in duplicate inserts or errors like you are seeing.

Next, PKs that are identity columns should be protected as well:

[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Column("Products Discussed ID")]
public int ProductsDiscussedID { get; protected set; }

I'm not sure what the comment about "manually managed Id" means, but there should be nothing manual about the PK. We want EF, and only EF assigning it. The error about Identity Insert OFF generally means EF was given an entity that it was told to treat as a new insert that already had a non-default value set. IN EF6 (.Net Framework) EF would ignore any existing value and you'd get a silent failure where EF would insert a duplicate record with a new identity column. I believe with EF Core they opted that if an ID was specified, even if marked as an Identity it would pass it in the INSERT statement to result in the Identity Insert exception. Protecting the setter should identify any code that might be trying to insert existing values.

Also code like this:

product.ReportID = contactReport.Report_Id;

is not necessary with mapped navigation products. When you call:

_context.ContactReports.Add(contactReport);
await _context.SaveChangesAsync();

with properly mapped associations, EF will insert all of the products under the report and associate the FKs automatically. [NotMapped] is going to gunk that up, so once that is fixed up where Products, having a ReportId parent FK, are associated properly, all of the code to set FKs after saving should be removed entirely.

Your code should work for inserting a new report, but likely won't work when updating a report if you are passing the collection of existing and potentially new products. Passing detached entities around can get complicated because any entity with an ID set that represents an existing row must be recognized by the DbContext as a tracked entity. Otherwise EF will attempt to insert it, resulting in that error. That involves checking that the DbContext isn't tracking an instance, and once confirmed, attaching the existing items or replacing the references if there is already one tracked.

If you make these changes and still have errors when inserting or updating, update your question with the actual entity definitions for the ContactReport and the ProductDiscussed.