7

I'm calling a stored procedure in Entity Framework 6 that can create Databases and tables if necessary. It is throwing the error;

Message "CREATE DATABASE statement not allowed within multi-statement transaction.\r\nALTER DATABASE statement not allowed within multi-statement transaction.\r\nDatabase 'CoreSnapshotJS3' does not exist. Make sure that the name is entered correctly." string

I do not want it in a transaction, and have used this to supress the transaction 

using (var transation = new TransactionScope(TransactionScopeOption.Suppress))
{
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("spCreateSnapshotFromQueue", snapshotQueueIDParameter);    
}

It still throws an error.

How do I stop automatic transactions?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Founder
  • 626
  • 6
  • 19
  • Does this answer your question? [EF6 wraps every single stored procedure call in its own transaction. How to prevent this?](https://stackoverflow.com/questions/19991609/ef6-wraps-every-single-stored-procedure-call-in-its-own-transaction-how-to-prev) – Michael Freidgeim Jun 28 '22 at 21:40

1 Answers1

26

I found a way:

var snapshotQueueIDParameter = new SqlParameter("SnapshotQueueID", entityId);
return _db.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction,
        "EXEC spCreateSnapshotFromQueue @SnapshotQueueID", snapshotQueueIDParameter);
Founder
  • 626
  • 6
  • 19
  • Is there a way to run a storedproecdure without a transaction which actually executes DQL - Select. I need the result also without using any output parameter in the storedprocedure. – Rupendra Dec 05 '16 at 10:30
  • in .net core is no `TransactionalBehavior` class :/ – Muflix Nov 13 '18 at 11:32
  • 1
    @Muflix in EF Core ExecuteSqlInterpolated/ExecuteSqlRaw do not automatically start a transaction. https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlraw?view=efcore-6.0 – Michael Freidgeim Jul 01 '22 at 13:42