SqlException: Cannot insert explicit value for identity column in table [Table name] when IDENTITY_INSERT is set to OFF

Question

I am creating a website for expense tracking and when I send a new POST request calling AddNew method to add a new Entry I get this error:

SqlException: Cannot insert explicit value for identity column in table 'Accounts' when IDENTITY_INSERT is set to OFF. Cannot insert explicit value for identity column in table 'Categories' when IDENTITY_INSERT is set to OFF.

public class Entry
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Description { get; set; }
    public decimal MoneyAmount { get; set; }
    public virtual Account Account { get; set; }
    public virtual Category Category { get; set; }
    public string CreatedTimestamp { get; set; }
}

public class Account
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual User User { get; set; }
    //public byte[] BankApi { get; set; }
    public decimal MoneyAmount { get; set; }
    public string CreatedTimestamp { get; set; }
    public virtual ICollection<Entry> Entries { get; set; }
}

public class Category
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual EntryType EntryType { get; set; }
    public virtual ICollection<Entry> Entries { get; set; }
}

public interface IEntryService
{
    void AddNew(Entry entry);
}

public class EntryService : IEntryService
{
    private DataContext _context;

    public EntryService(DataContext context)
    {
        _context = context;
    }

    public void AddNew(Entry entry)
    {
        entry.CreatedTimestamp = DateTime.Now.ToString();

        _context.Entries.Add(entry);
        _context.SaveChanges();
    }
}

Here is my postman request:

Postman request

Account with ID == 1, and Category with ID == 1 both exist already in the respective databases.

Also, when I am inserting values to Account database, whose method looks exactly the same as AddNew for entries, it works without a problem.

Does anyone know what is this about?

Did you try this solution: https://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity — J.K, Jun 30 '19 at 15:13
DatabaseGenerated.Identity means that you will leave the PK generation to the database. Try setting entry.Id = 0 before adding the entry. — Vidmantas Blazevicius, Jun 30 '19 at 15:13
@VidmantasBlazevicius i put entry.Id to 0 but i get the same error. — Tomislav Ćalušić, Jun 30 '19 at 15:54
There is a lot of missing code here. The error is complaining about you setting an ID for both `Accounts` and `Categories` entities. The error is where you instantiate and save those two objects and not the `Entry`. — Simon Whitehead, Jun 30 '19 at 16:19

score 4 · Answer 1 · answered Jun 30 '19 at 21:36

The issue is that you are passing entities with relations back to the server. Each request is served by a different DB Context so while your Entry with it's associated Account and Category look like entities, they are not. They are deserialized POCO objects that the DbContext knows nothing about. When you pass an entity referencing other entities that already exist in the database, the receiving context does not know about these entities, so it interprets them as new records and tries to insert them.

My default recommendation is to never pass entities between client and server. This leads to serialization errors & performance issues from server to client, and exposes your system to tampering, plus issues like this with insert errors or duplication when used from client to server.

To solve your issue without too much of a change:

public void AddNew(Entry entry)
{
    entry.CreatedTimestamp = DateTime.Now.ToString();
    var account = _context.Accounts.Single(x => x.Id = entry.Account.Id);
    var category = _context.Categories.Single(x => x.Id = entry.Category.Id);
    entry.Account = account; 
    entry.Category = category;
    _context.Entries.Add(entry);
    _context.SaveChanges();
}

This associates the entry account and category with instances known by the context.

An alternative would be to attach the account and category passed back to the DbContext. This would also work, however it can make your system vulnerable to tampering. The data passed back to the controller can be intercepted by debugging tools or sniffers on a client machine where the data in the message can be altered to change details in records and their related records in ways you don't intend. If for any reason any code ends up setting those re-attached entities to a Modified state, those alterations would be persisted to the database on SaveChanges. Attaching entities can also be a nuisance when dealing with multiple detached references. For instance if you are saving multiple records at a time with relatives. Attaching each will work fine when all relatives are unique, but without the extra logic to check each one to see if there is already an attached reference, you can get errors when a second reference to an already attached (or loaded) entity attach is attempted. This means more code to check the local association and replace it if found.

You're making good points here, but the tampering hazard isn't too realistic. If the `AdNew` method just attaches the known `account/category` they won't ever be marked as `Changed`. And then, if such interceptions really happen the main entity could just as well be tampered with, *with* success (any non-encrypted network trafic, etc.). FWIW, my recommendation would be to start using *foreign key associations* instead of these *independent associations*. — Gert Arnold, Jun 30 '19 at 21:54
Yes, it would require something to set the entity state to modified. For simple references that isn't likely, but as systems evolve a reference to an entity should never be second-guessed whether it came from the data state and assumed safe, or somewhere else and deemed unsafe. Category will likely be safe, but something later updating a field on Account? Possible and certainly a risk. That code tucked in a different method may even use context.Accounts to fetch and assume it's getting data state before SaveChanges is called. I don't advocate sending any entities, reference or main. — Steve Py, Jun 30 '19 at 22:04
@StevePy hey man, just commenting here to thank you. Since this is a common question in stackoverflow and the common answers weren't solving my problem, i was so frustrated. Finally your detailed answer solved the problem for me. I can't express my gratitude enough. — Muzib, Oct 06 '20 at 08:42
@StevePy Thanks for this explanation. I was struggling trying to understand why this was happening to my project. Your explanation was clear and simple. My database saves now work and, more importantly, I understand why. — AndyW, Jun 27 '23 at 07:35

score 1 · Accepted Answer · answered Jul 04 '19 at 17:52

I just changed Entry model from

public class Entry
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Description { get; set; }
    public decimal MoneyAmount { get; set; }
    public virtual Account Account { get; set; }
    public virtual Category Category { get; set; }
    public string CreatedTimestamp { get; set; }
}

To this:

 public class Entry
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Description { get; set; }
        public decimal MoneyAmount { get; set; }
        public int AccountId { get; set; }
        public int CategoryId { get; set; }
        public string CreatedTimestamp { get; set; }
    }

And everything seems to work fine now. C# does its magic and picks AccountId and CategoryId as foreign keys.

But what if we have a collection of categories for example ? Because I'm facing the same issue but instead of including one category to the request, I include multiple. Any idea on how to come up with this issue ? — faye.babacar78, Apr 17 '22 at 16:45

SqlException: Cannot insert explicit value for identity column in table [Table name] when IDENTITY_INSERT is set to OFF

2 Answers2