Error saving record to database. Using Entity Framework. Cannot insert explicit value for identity column

Question

I'm receiving an error but cannot figure out why it's trying to save anything to the 'Priority' table. From the Database, Priority table has a Primary Key of Id. ForeignKey on Request Table is PriorityId.

Here is the error I receive: "An error occurred while updating the entries. See the inner exception for details. Cannot insert explicit value for identity column in table 'Priority' when IDENTITY_INSERT is set to OFF."

It seems like upon calling db.SaveChangesAync();, it's trying to save something to the Priority Table but it shouldn't. There are items in the Priority table so that's not the issue either. The model being passed back from the controller has everything that is needed in order to complete the entry.

Here is the Request Extension

public static async Task AddRequest(this AppDbContext db, Request request)
    {
        if (await request.Validate(db))
        {
            request.DateSubmitted = DateTime.Now;
            request.LastModified = DateTime.Now;
            request.PriorityId = request.Priority.Id;
            request.SiteId = request.Site.Id;
            await db.Requests.AddAsync(request);
            await db.SaveChangesAsync();

            foreach (var item in request.RequestItems)
            {
                await db.AddItems(item, request.Id);
            }
        }
    }
private static async Task<bool> Validate(this Request model, AppDbContext db)
    {
        if (string.IsNullOrEmpty(model.Subject))
        {
            throw new Exception("Subject must be filled in");
        }

        var check = model.Id > 0 ?
            await db.Requests.FirstOrDefaultAsync(x => x.Subject.ToLower() == model.Subject.ToLower() && x.Id == model.Id) :
            await db.Requests.FirstOrDefaultAsync(x => x.Subject.ToLower() == model.Subject.ToLower() && x.UserId == model.UserId);

        if (check != null)
        {
            throw new Exception("The provided Request already exists");
        }

        return true;
    }

Request Model

public class Request
{
    public int Id { get; set; }
    public int PriorityId { get; set; }
    public int SiteId { get; set; }
    public int UserId { get; set; }
    public string Justifications { get; set; }
    public Guid Guid { get; set; }
    public string Subject { get; set; }
    public string Requirement { get; set; } 
    public string Mission { get; set; }
    public DateTime DateSubmitted { get; set; }
    public DateTime LastModified { get; set; }

    public bool IsRecurring { get; set; }
    public DateTime? RenewalDate { get; set; }

    public bool IsApproved { get; set; }
    public bool IsComplete { get; set; }

    public Priority Priority { get; set; }
    public Site Site { get; set; }
    public User User { get; set; }


    public List<ItemGroup> ItemGroups { get; set; }
    public List<RequestAttachment> RequestAttachments { get; set; }
    public List<RequestItem> RequestItems { get; set; }
}

}

Then here is the Priority Model

public class Priority
{
    public int Id { get; set; }
    public string Label { get; set; }
    public bool IsDeleted { get; set; }

    public List<Request> Requests { get; set; }
}

Here is the updated and operational version of my AddRequest Task: Thanks to @Imantas

public static async Task AddRequest(this AppDbContext db, Request request)
    {
        if (await request.Validate(db))
        {
            request.DateSubmitted = DateTime.Now;
            request.LastModified = DateTime.Now;
            request.PriorityId = request.Priority.Id;
            request.SiteId = request.Site.Id;
            db.Priorities.Attach(request.Priority);
            db.Sites.Attach(request.Site);
            await db.Requests.AddAsync(request);
            await db.SaveChangesAsync();

            foreach (var item in request.RequestItems)
            {
                await db.AddItems(item, request.Id);
            }
        }
    }

Nothing in your code should give rise to inserts of Priority entities. Still, the exception indicates a mismatch between the EF model and the database model. The table Priority has an identity column, but EF isn't configured accordingly. — Gert Arnold

Imantas Imantas · Accepted Answer · 2019-01-29T20:38:41

Your request.Priority entity is either detached from the DbContext or wasn't ever attached. It could also happen that you retrieved it from a different instance of DbContext. You didn't provide the context of where it comes from to the method therefore it's hard to tell what's the exact cause. This way, change tracker identifies it as a new entity and tries to insert it. Since it has it's Id already set, database rejects this insert.

You have multiple options here. One of them is manually attaching the request.Priority to the current DbContext with Unchanged state. The other is simpler. Just explicitly set request.Priority = null; right after you set request.PriorityId = request.Priority.Id. This way change tracker won't see the entity you don't want to insert and won't try to insert it into the database. Relationship (foreign key) will still be set correctly so no worries about that.

However if you need to use the request.Priority relationship after the save, you need to reload the entity from database or set it back again. In this case, the first option might be better.

Error saving record to database. Using Entity Framework. Cannot insert explicit value for identity column

1 Answers