.NET 9.0 的 Blazor Web App 项目中 EF Core 【事务】使用备忘

一、DbContext.Database.BeginTransactionAsync() 模式

1. 注意事项：连接字符串中启用了 MARS（Multiple Active Result Sets：MultipleActiveResultSets=True ）后，无法创建 保存点（保存点与 SQL Server 的多重活动结果集不兼容），此时，使用注入的 DbContext（例如：@inject ApplicationDbContext  FirstDb） 将报错。解决办法：在 事务 中自己 new 一个 DbContext。

  "ConnectionStrings": {
    "Ky1SqlServerConnection": "Server=localhost;Database=kyglxt;Trusted_Connection=True;MultipleActiveResultSets=True;TrustServerCertificate=True;User=sa;Password=123456",
    "Ky2SQLiteConnection": "Data Source=SQLiteFile.db",
  }

 上述语句为 appsettings.json 中保存的 连接字符串。

/* SQL Server 事务测试 —— ok
*  注：Multiple Active Result Sets(MARS) is enabled：MultipleActiveResultSets = True; 时，不能使用注入的 DbContext，在 事务 中自己 new 一个 DbContext。*/
try
{
    using (var FirstDb = new KyglxtContext(new DbContextOptionsBuilder<KyglxtContext>().UseSqlServer(AppsettingsJsonService.Configuration["ConnectionStrings:Ky1SqlServerConnection"]).Options))
    {
        var transaction = await FirstDb.Database.BeginTransactionAsync();

        var FirstDbEntity01 = new 用户表 { 用户名 = $"Entity in FirstDb：{DateTime.Now}" };
        FirstDb.Add(FirstDbEntity01);
        await FirstDb.SaveChangesAsync();

        System.Console.WriteLine("————save 1，end");

        await Task.Delay(2000);
        System.Console.WriteLine("————delay end");

        var FirstDbEntity02 = new 用户表 { 名称 = $"Entity in FirstDb：{DateTime.Now}" };
        FirstDb.Add(FirstDbEntity02);
        await FirstDb.SaveChangesAsync();

        System.Console.WriteLine("————save 2，end");

        // Commit transaction if all commands succeed, transaction will auto-rollback when disposed if either commands fails
        await transaction.CommitAsync();

        System.Console.WriteLine("————try，ok");
    }
}
catch (Exception ex)
{
    System.Console.WriteLine(ex.Message);
    System.Console.WriteLine(ex.InnerException?.Message);
}
finally { }

2. 对于 环境事务（TransactionScope） ，SQLite 不支持，针对多个数据库使用 事务 功能时，不能包含 SQLite。解决办法1：生产环境，SQLite 换成 SQL Server 。解决办法2：手动处理。

 /* 手动处理 多个数据库，支持 SQLite */
 try
 {
     using var FirstDb = new KyglxtContext(new DbContextOptionsBuilder<KyglxtContext>().UseSqlServer(AppsettingsJsonService.Configuration["ConnectionStrings:Ky1SqlServerConnection"]).Options);
     using var SecondDb = new ApplicationDbContext(new DbContextOptionsBuilder<ApplicationDbContext>().UseSqlite(AppsettingsJsonService.Configuration["ConnectionStrings:Ky2SQLiteConnection"]).Options);

     var FirstTransaction = await FirstDb.Database.BeginTransactionAsync();
     var SecondTransaction = await SecondDb.Database.BeginTransactionAsync();

     try
     {
         var firstEntity = new 用户表 { 用户名 = $"BeginTransactionAsync，2：{DateTime.Now}" };
         FirstDb.Add(firstEntity);
         await FirstDb.SaveChangesAsync();

         System.Console.WriteLine("FirstDb————save 1，end");

         await Task.Delay(2000);
         System.Console.WriteLine("BeginTransactionAsync，2————————delay end");

         var secondEntity = new ApplicationUser { UserName = $"BeginTransactionAsync，2：{DateTime.Now}" };
         SecondDb.Add(secondEntity);
         await SecondDb.SaveChangesAsync();

         System.Console.WriteLine("SecondDb————save 2，end");

         await FirstTransaction.CommitAsync();
         await SecondTransaction.CommitAsync();

         System.Console.WriteLine("BeginTransactionAsync，2——————try，ok");
     }
     catch (Exception ex)
     {
         await FirstTransaction.RollbackAsync();
         await SecondTransaction.RollbackAsync();

         System.Console.WriteLine($"BeginTransactionAsync，2——————catch，{ex.Message}");
         System.Console.WriteLine($"BeginTransactionAsync，2——————catch，{ex.InnerException?.Message}");
     }

 }
 catch (Exception ex)
 {
     System.Console.WriteLine($"BeginTransactionAsync，2——————catch，{ex.Message}");
     System.Console.WriteLine($"BeginTransactionAsync，2——————catch，{ex.InnerException?.Message}");
 }
 finally
 {
     System.Console.WriteLine("BeginTransactionAsync，2——————finally：");
 }

二、环境事务，System.Transactions、TransactionScope：支持操作多个数据库

 /* 环境事务：支持操作多个数据库，但是 SQLite 不支持 TransactionScope */
 try
 {
     using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
     {
         using (var FirstDb = new KyglxtContext(new DbContextOptionsBuilder<KyglxtContext>().UseSqlServer(AppsettingsJsonService.Configuration["ConnectionStrings:Ky1SqlServerConnection"]).Options))
         {
             var firstEntity = new 用户表 { 用户名 = $"TransactionScope：{DateTime.Now}" };
             FirstDb.Add(firstEntity);
             await FirstDb.SaveChangesAsync();

             System.Console.WriteLine("FirstDb————save 1，end");

             await Task.Delay(2000);
             System.Console.WriteLine("TransactionScope————————delay end");

             using (var SecondDb = new ApplicationDbContext(new DbContextOptionsBuilder<ApplicationDbContext>().UseSqlite(AppsettingsJsonService.Configuration["ConnectionStrings:Ky2SQLiteConnection"]).Options))
             {
                 var secondEntity = new ApplicationUser { UserName = $"TransactionScope：{DateTime.Now}" };
                 SecondDb.Add(secondEntity);
                 await SecondDb.SaveChangesAsync();
             }

             System.Console.WriteLine("SecondDb————save 2，end");
         }

         // Commit transaction if all commands succeed, transaction will auto-rollback when disposed if either commands fails
         scope.Complete();

         System.Console.WriteLine("TransactionScope——————try，ok");
     }
 }
 catch (Exception ex)
 {
     // Handle the exception (e.g., log it)
     System.Console.WriteLine($"TransactionScope——————catch，{ex.Message}");
     System.Console.WriteLine($"TransactionScope——————catch，{ex.InnerException?.Message}");
 }
 finally
 {
     System.Console.WriteLine("TransactionScope——————finally：");
 }