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
?
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();
}