SET IDENTITY_INSERT not working when using TransactionScope in Entity Framework Core 3

.net-core c# entity-framework-core

Question

I'm having issues using TransactionScope in EF Core 3. The code below works fine when using Database.BeginTransaction but not when using TransactionScope. The code below doesn't work:

using (var ts = new TransactionScope(option, new TransactionOptions
            {
                Timeout = TransactionManager.MaximumTimeout,
                IsolationLevel = isolation
            }, TransactionScopeAsyncFlowOption.Enabled))
{
    Context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT ITEMS ON");
    Context.Item.Add(item);
    Context.SaveChanges();
}

I get the following error

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

This code does work:

using(var ts = Context.Database.BeginTransaction())
{
    Context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT ITEMS ON");
    Context.Item.Add(item);
    Context.SaveChanges();
    ts.Commit();
}

How can I make it work with a TransactionScope?

1
3
11/14/2019 4:47:19 AM

Accepted Answer

You need to open and close the connection yourself, otherwise EF will open/close for each operations. Your ExecuteRawSql and your SaveChanges are effectively done on different connections.

Context.Item.Add(item);
Context.Database.OpenConnection();
try
{
    Context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT ITEMS ON");
    Context.SaveChanges();
    Context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT ITEMS OFF");
}
finally
{
    Context.Database.CloseConnection();
}

Reference: https://docs.microsoft.com/en-us/ef/core/saving/explicit-values-generated-properties#explicit-values-into-sql-server-identity-columns

2
11/13/2019 11:57:08 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow