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

Following through Julie Lerman's Pluralsight course EF Core 6 Fundamentals I've created two classes in my own project (my own design, but identical to the course in terms of class structure/data hierarchy):

Class 1: Events - To hold information about an event being held (e.g. a training course), with a title and description (some fields removed for brevity):

public class EventItem
{   
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int EventItemId { get; set; }

    [Required(AllowEmptyStrings = false)]
    public string EventTitle { get; set; }

    public string? EventDescription { get; set; }

    [Required]
    public List<EventCategory> EventCategories { get; set; } = new();
}

Class 2: Event categories - Each event can be linked to one or more pre-existing (seeded) categories (e.g. kids, adult).

public class EventCategory
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int EventCategoryId { get; set; }

    [Required]
    public string EventCategoryName { get; set; }

    public List<EventItem>? EventItems { get; set; }
}

In my Razor form to create the event, the user can select from multiple categories. Using EF Core I take the posted data (via a VM/DTO object) and construct the relevant parent/child entities. However upon saving to the database I get an exception as EF Core tries to re-create the categories when they already exist:

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

My code explicitly looks up the existing categories selected by the user, but the context tracker appears to still believe they need inserting, in addition to creating the many-to-many relationship.

I'd appreciate any input as to why this is happening please:

using (var dbcontext = DbFactory.CreateDbContext())
{
    // Get selected categories from user's check box list
    var selectedCategoryIds = _eventCagetories.Where(c => c.isSelected).Select(c => c.EventCategoryId).ToList();

    // Create new Event
    var newEventItem = new EventFinderDomain.Models.EventItem() {
        EventTitle = _eventItemDTO.EventTitle,
        EventDescription = _eventItemDTO.EventDescription,
        EventUrl = _eventItemDTO.EventUrl,
        TicketUrl = _eventItemDTO.TicketUrl
    };

    // Find categories from the database based on their ID value
    var selectedEventCategories = dbcontext.EventCategories.Where(c => selectedCategoryIds.Contains(c.EventCategoryId)).ToList();
    
    // Add the categories to the event
    newEventItem.EventCategories!.AddRange(selectedEventCategories);

    // Add the event to the change tracker
    await dbcontext.EventItems.AddAsync(newEventItem); // <-- Created correctly with child list objects added

    // Detect changes for debugging 
    dbcontext.ChangeTracker.DetectChanges();
    var debugView = dbcontext.ChangeTracker.DebugView; // <-- Incorrectly shows newEventItem.Categories being added

    // Save to database
    await dbcontext.SaveChangesAsync(); // <-- Cannot insert explicit value for identity column
}

The Event entity appears to be correctly created in the debugger with its related child categories included:

The change tracker however incorrectly shows the selected categories being added again when they already exist:

CodePudding user response：

After commenting out every line of code in the app and adding back in until it broke, it emerges the problem was elsewhere within Program.cs:

builder.Services.AddDbContextFactory<EventFinderContext>(
    opt => opt.UseSqlServer(new SqlConnectionStringBuilder() {/*...*/}.ConnectionString)
    .EnableSensitiveDataLogging()
    .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking) // <-- THE CULPRIT
);

In the training video this method was described as a way of reducing overhead for disconnected apps. I had assumed that because of the disconnected nature of HTTP, this would be beneficial and that context would be re-established when creating the model's child data. This was incorrect on my part.

I should have used .AsNoTracking() only when retriving read-only data from my database. For example, loading in the child-data for a new model that wouldn't be modified directly, but used to create the many-to-many data (explicitly, for the category data option items only and not for the event data).