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

[scottengle]

I have an existing database table that doesn't allow identity inserts. When I try to add a new item to the database, I get the following error message:

{"Cannot insert explicit value for identity column in table 'ITEMS' when IDENTITY_INSERT is set to OFF."}

My simplified service code:

public Item AddItem(Item item)
{
        db.Items.Add(item);

        if(db.SaveChanges() > 0)
        {
            return item;
        }

        return null;
    }
}

My OnModelCreating override:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Items>()
        .Key(e => e.Id);

    builder.Entity<Items>(e =>
       {
           e.Metadata.SqlServer().Table = "ITEMS";
           e.Property(c => c.Id).Metadata.SqlServer().Column = "ITEM_ID";
           e.Property(c => c.Id).Metadata.SqlServer().ColumnType = "numeric(18,0)";
           e.Property(c => c.Id).GenerateValueOnAdd(true);
           e.Property(c => c.Name).Metadata.SqlServer().Column = "ITEM_NAME";
           e.Property(c => c.Name).MaxLength(50).Required();
           e.Property(c => c.LastModified).Metadata.SqlServer().Column = "LAST_MODIFIED_DTM";
       });
}

And lastly, the SQL Command that is being executed against SqlServer:

--The data may be truncated and may not represent the query that was run on the server
USE [database];

GO

--Type and value data was not available for the following variables. Their values have been set to defaults.
DECLARE @p0 AS SQL_VARIANT;
DECLARE @p1 AS SQL_VARIANT;
DECLARE @p2 AS SQL_VARIANT;
SET @p0 = NULL;
SET @p1 = NULL;
SET @p2 = NULL;

SET NOCOUNT OFF;
INSERT INTO [ITEMS] ([ITEM_ID], [LAST_MODIFIED_DTM], [ITEM_NAME])
VALUES (@p0, @p1, @p2);
SELECT @@ROWCOUNT;

Because Identity Insert is turned off, I can't have the Item ID included when an insert occurs. Is there a proper way to deal with this scenario?

[scottengle]

Here's the stack trace for reference:

   at Microsoft.Data.Entity.Relational.Update.ReaderModificationCommandBatch.Execute(RelationalTransaction transaction, IRelationalTypeMapper typeMapper, DbContext context, ILogger logger)
   at Microsoft.Data.Entity.Relational.Update.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.Data.Entity.Relational.RelationalDataStore.SaveChanges(IReadOnlyList`1 entries)
   at Microsoft.Data.Entity.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.Data.Entity.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.Data.Entity.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.Data.Entity.DbContext.SaveChanges()
   ...

[rowanmiller]

@scottengle what is the value assigned to item.Id in your AddItem method. Currently in EF7 if there is a value assigned (i.e. it's not null or 0 if it's non-nullable numeric type) then EF will just pass that thru to the database. If it's null (or 0) then it will allow the database to generate a value.

[scottengle]

The item ID is a (non-nullable) decimal type and the value defaults to 0.0. I'm basically testing this through an API call from a REST client in the browser and I'm sending only the Name and LastModified properties in the request.

[scottengle]

Here's the Web API implementation for POST, but this errors out in the call to AddItem():

        [HttpPost]
        public IActionResult CreateItem([FromBody] Item item)
        {
            if (!ModelState.IsValid)
            {
                return new HttpStatusCodeResult(400);
            }
            else
            {
                var addedItem = _itemDataSvc.AddItem(item);
                if(addedItem != null)
                {
                    string url = Url.RouteUrl("GetByIdRoute", new { id = addedItem.Id }, Request.Scheme, Request.Host.ToUriComponent());
                    Context.Response.Headers["Location"] = url;
                    return new HttpStatusCodeResult(201);
                }
                else
                {
                    return new HttpStatusCodeResult(400);
                }
            }

[rowanmiller]

@scottengle we're having trouble getting to the bottom of this since the decimal column should never be configured as Identity by convention. Could it be that you mapping to an existing database that was not created by EF? If so, you would need to configure that property as Identity (since it won't be configured that way by convention).

modelBuilder.Entity<Items>()
                .Property(i => i.Id)
                .ForSqlServer()
                .UseIdentity()

[rowanmiller]

BTW I notice you are using the Metadata property a lot in your model builder code. Any particular reason you use e.Metadata.SqlServer().Table = "ITEMS"; in favor over e.ForRelational().Table("ITEMS);?

Was it just a matter of finding the APIs first, or was there a reason you prefer the Metadata one?

[scottengle]

@rowanmiller Yes, I'm mapping to an existing database that wasn't created by EF originally. I'll try assigning the Identity. With respect to the Metadata calls, I started working on this code base during beta3 and there wasn't much documentation at the time on how to code up a "database first" project. I think the Metadata calls just ended up working for me at the time. If the ForRelational calls are better practice, I'll definitely use those.

[rowanmiller]

@scottengle ok that makes sense. If EF doesn't know that the column is IDENTITY then it will try and insert values for you.

Here are some very early docs on using the configuration API http://ef.readthedocs.org/en/latest/modeling/configuring.html.

[jpwits]

Hi rowan,
I 've got a similar issue , I seed my db (250 tables) with json files, for this I need Identity Insert Off, for the existing PK/FK's, but once I run the web app I need Identity insert on to add new entries, since the front end sends multi threaded updates to the same table, via Web API so it then need to create the identity for me. Thus if u could add this to EF7, it will be an excellent addition, experienced this issue for decades. Thanks for the great work I'm using EF7 beta 7 at the moment.

[strizzwald]

I am experiencing this issue as well now with EF7 my object has a auto-incremented int primary key but whenever I try to insert EF does not try to generate a key for the object.

public DivisionManager AddDivisionManager (DivisionManager divisionManager) { _dataContext.Set<DivisionManager>().Add(divisionManager); _dataContext.SaveChanges(); }

[rowanmiller]

@carnag3kid7 please open a new issue with details of what you are seeing. The issue being tracked here is closed.

[jpwits]

@scottengle , have not investigated this, but that e.Property(c => c.Id).GenerateValueOnAdd(true); is probably only gonna work with Identity Insert 'ON', else what will generate the ID?

[jpwits]

@scottengle You can generate your own Id, this will be an issue on a multi user system, too keep it in sync, actually kind of impossible, in that case u can auto generate a guid as your Id, but this is dirty on a central db.