Cannot insert explicit value for identity column #5436 | Support Center

bilalhaidar created 8 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 8 years ago
Support Team

Can you share the error stack information?

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

alper created 8 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.

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)

bilalhaidar created 8 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 8 years ago
Support Team

Hi @bilalhaidar,

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

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

bilalhaidar created 8 years ago

Hi @ismcagdas

It is: SQL Server 2016

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

ismcagdas created 8 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>

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

bilalhaidar created 8 years ago

I run the script on the database you mean?

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0
maliming created 8 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

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0
bilalhaidar created 8 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?

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

alper created 8 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>

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0
bilalhaidar created 8 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");

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

alper created 8 years ago
Support Team

smart solution

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

bilalhaidar created 8 years ago

Thanks :)

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)

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