CODEWITHSUNDEEP
entity-frameworkentity-framework-core
John Edwards
John Edwards

Reputation: 1546

EF 7 Identity Insert Issue

I have to be able to set the primary key of the entities I am trying to add in my list "final_zones". I have a controller constructor as follows:

    public UtilController(CFETSContext context)
    {
        _context = context; 
    }
     ......Then in a web api method.........
        using (var transaction = _context.Database.BeginTransaction())
        {
            _context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [CFETSWeb].[dbo].[Zone] ON");             

            _context.SaveChanges();
            transaction.Commit();
        }


       using (var transaction = _context.Database.BeginTransaction())
        {             
            _context.Zones.AddRange(final_zones);
            _context.SaveChanges(); //ERROR HERE
            transaction.Commit();
        }

No matter what I do, I cannot seem to get IDENTITY_INSERT to turn on. I get the following error:

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

I just can't seem to get it to turn off. I can add entities to my DB in the controller, so I know everything else is working. I have tried doing this without a transaction as well with the same result.

 _context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [CFETSWeb].[dbo].[Zone] ON"); 
_context.Zones.AddRange(final_zones);
        _context.SaveChanges();

Any ideas? I am at a loss for what to try next. Thanks.

Upvotes: 2

Views: 652

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109253

The SET IDENTITY_INSERT statement is scoped to one SQL session, which is practically equivalent to one set of statements that is sent over an open connection.

However, by default, an EF context will open and close a connection for each single statement with database interaction it executes. So after the ExecuteSqlCommand statement, the connection is closed, and the IDENTITY_INSERT is reset.

Now it's a somewhat hidden feature that EF won't close a connection if you open it before the context executes statements. So if you do this ...

try
{
    _context.Database.Connection.Open();
    _context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [CFETSWeb].[dbo].[Zone] ON");
    _context.Zones.AddRange(final_zones);
    _context.SaveChanges();
}
finally
{
    _context.Database.Connection.Close();
}

... you'll notice that the IDENTITY_INSERT setting will "survive" the ExecuteSqlCommand statement.

Upvotes: 1

John Edwards
John Edwards

Reputation: 1546

I solved it with the following:

       using (var dbContextTransaction = _context.Database.BeginTransaction())
        {
            try
            {
                _context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [CFETSWeb].[dbo].[Zone] ON");
                _context.Zones.AddRange(final_zones);
                _context.SaveChanges();          

                dbContextTransaction.Commit();
            }
            catch (Exception e)
            {
                dbContextTransaction.Rollback();
            }
        }

NOTE: You HAVE to do this per table. IDENTITY_INSERT can only be set for 1 table at a time it seems, so you can do it this way or toggle it to OFF in the same transaction.

Also, IDENTITY_INSERT has to be in a transaction, as it only stays on for the duration of a transaction.

Upvotes: 4

Related Questions