Cannot insert explicit value for identity column #5436

bilalhaidar created 5 years ago

Hi, I am running my application on a new laptop. I installed the database via update-database command and then tried to run the app. I get the following exception:

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

The exception occurs on this line:

var parent = _context.SpecificNeeds.Add(new SpecificNeed("Child at risk")).Entity;
 _context.SaveChanges();

All was working fine on the previous machine.

Any idea what might be the problem?

13 Answer(s)

0

maliming created 5 years ago
Support Team

Can you share the error stack information?
0

alper created 5 years ago
Support Team

that doesn't seem to seen as the out of box code. if it's your code, be aware that you cannot insert an entity with Id field set. The field (Id) is auto increment and must be empty.

bilalhaidar created 5 years ago

Hello @alper,

This is the stack trace.

at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(Tuple`2 parameters)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.&lt;&gt;c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Abp.EntityFrameworkCore.AbpDbContext.SaveChanges()
   at Tekvention.DrcApp.Migrations.Seed.DefaultSpecificNeedsCreator._create()
   at Tekvention.DrcApp.Migrations.Seed.DefaultSpecificNeedsCreator.Create()
   at Tekvention.DrcApp.Migrations.Seed.DefaultDrcBuilder.CreateDefaultDrcEntities()
   at Tekvention.DrcApp.Migrations.Seed.DefaultDrcBuilder.Create()
   at Tekvention.DrcApp.Migrations.Seed.SeedHelper.SeedHostDb(DrcAppDbContext context)
   at Tekvention.DrcApp.Migrations.Seed.SeedHelper.WithDbContext[TDbContext](IIocResolver iocResolver, Action`1 contextAction)
   at Tekvention.DrcApp.Migrations.Seed.SeedHelper.SeedHostDb(IIocResolver iocResolver)
   at Tekvention.DrcApp.Entity.DrcAppEntityFrameworkCoreModule.PostInitialize()
   at Abp.Modules.AbpModuleManager.<>c.<StartModules>b__15_2(AbpModuleInfo module)
   at System.Collections.Generic.List`1.ForEach(Action`1 action)
   at Abp.Modules.AbpModuleManager.StartModules()
   at Abp.AbpBootstrapper.Initialize()

Actually, I am not adding any value for the ID. This code was working before. Now on the new machine it is not.

I am just seeding some values and that's where I am getting the exception.

private void _create()
        {
            //Create if table is empty
            if (!_context.SpecificNeeds.Any())
            {
                var parent = _context.SpecificNeeds.Add(new SpecificNeed("Child at risk")).Entity;
                _context.SaveChanges(); // Exception here

0

ismcagdas created 5 years ago
Support Team

Hi @bilalhaidar,

This might be related to Database. What is your database (SQL Server 201x ) ?
0

bilalhaidar created 5 years ago

Hi @ismcagdas

It is: SQL Server 2016
0

ismcagdas created 5 years ago
Support Team

Could you set Identity Insert On by running an SQL query manually to see if it works ? <a class="postlink" href="https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017">https://docs.microsoft.com/en-us/sql/t- ... erver-2017</a>
0

bilalhaidar created 5 years ago

I run the script on the database you mean?

maliming created 5 years ago

Support Team

Execute the sql statement directly in the database of your problem.

SET IDENTITY_INSERT yourtable ON
INSERT INTO yourtable (id, name) VALUES (1, 'test 1.0')
SET IDENTITY_INSERT yourtable OFF

0
bilalhaidar created 5 years ago
I noticed the following.

var item = new SpecificNeed("Child at risk");

The object item has Id equal to 0.

After running the line below:

var parent = _context.SpecificNeeds.Add(item).Entity;

Now parent object has Id with a value of -2147482647

Isn't that weird?
0

alper created 5 years ago
Support Team

there's a bunch of comments about your case, could you check out these links if they are similar for your case? <a class="postlink" href="https://github.com/aspnet/EntityFrameworkCore/issues/10167">https://github.com/aspnet/EntityFramewo ... sues/10167</a> <a class="postlink" href="https://github.com/aspnet/EntityFrameworkCore/issues/7743">https://github.com/aspnet/EntityFramewo ... ssues/7743</a>

bilalhaidar created 5 years ago

Hi, I finally made it work. In one of the entities, I need to provide a value for Id. However, Id is configured to be Identity Column.

Therefore, I had to make use of the following:

_context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.DrcYesNoDecline ON");
                ...
                _context.SaveChanges();
                _context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.DrcYesNoDecline OFF");

0

alper created 5 years ago
Support Team

smart solution
0

bilalhaidar created 5 years ago

Thanks :)

Have an answer to this question? Log in and write your answer.