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?
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();
}
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow