Transaction between contexts

Question

I'm developing a Console Application using Entity Framework Core (7).

The application is divided into 3 different areas but the database is shared. I created 3 different DbContext and now I need to perform a transaction between all of them. So I need an atomic operation the save all the changes or nothing (rollback).

I know that in entity frameowrk 6 there was a class called TransactionScope but I cant find an alterntive in EF Core.

Using the following code:

public static void Main(string[] args)
    {
        var options = new DbContextOptionsBuilder<DbContext>()
        .UseSqlServer(new SqlConnection("Server=x.x.x.x,1433;Database=test;user id=test;password=test;"))
        .Options;

        var cat = new Cat { Name = "C", Surname = "C", Age = 55 };
        var dog = new Dog { Date = DateTime.Now, Code = 120, FriendId = cat.Id };


        using (var context1 = new DogsContext(options))
        {
            using (var transaction = context1.Database.BeginTransaction())
            {
                try
                {
                    context1.Dogs.Add(dog);
                    context1.SaveChanges();

                    using (var context2 = new CatsContext(options))
                    {
                        context2.Database.UseTransaction(transaction.GetDbTransaction());
                        context2.Cats.Add(cat);
                    }

                    transaction.Commit();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                    transaction.Rollback();
                }
            }
        }
    }

I get the following error:

System.InvalidOperationException: ExecuteScalar requires the command to have a transaction when the connection assigned to the co
mmand is in a pending local transaction.  The Transaction property of the command has not been initialized.
   at System.Data.SqlClient.SqlCommand.ValidateCommand(Boolean async, String method)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStrea
m, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMe
thod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)

Answer 1

TransactionScope is not part of Entity Framework. Its part of the System.Transactions namespace. Additionally, TransactionScope is not the recommended approach for handling transactions with Entity Framework 6.x.

With Entity Framework Core you can share a transaction across multiple contexts for relational databases only. The contexts must share the same database connection.

More information here: https://learn.microsoft.com/en-us/ef/core/saving/transactions

Example (not tested):

        using (var context1 = new YourContext())
        {
            using (var transaction = context1.Database.BeginTransaction())
            {
                try
                {
                    // your transactional code
                    context1.SaveChanges();
                    
                    using (var context2 = new YourContext())
                    {
                        context2.Database.UseTransaction(transaction.GetDbTransaction());
                        // your transactional code
                    }
                    
                    // Commit transaction if all commands succeed, transaction will auto-rollback when disposed if either commands fails
                    transaction.Commit();
                }
                catch (Exception)
                {
                    // handle exception
                }
            }
        }