0

I have large DB model (hundred of tables) split to multiple EntityFrameworkCore DbContexts. Is is a quite common use case when I modify multiple entities in two (or more) different DbContexts, but I need to commit this operations withing a single transaction.

I use a IReporitory pattern where I get injected into Controller an instance of ISomeRepository implementation which looks like:

[HttpPost]
public asycn Task DoSomeWorkAsync()
{
   using (var transaction = this.IEmployeesRepository.BeginTransaction())
   {
       // do some work
       await this.IEmployeesRepository.SaveChangesAsync();

       // do another work
       await this.IPayrollRepository.SaveChangesAsync();
   }
}

An EmployeeDbContext implements an IEmployeeRepository interface, PayrollDbContexts implements IPayrollRepository.

I end up with error: System.InvalidOperationException: The specified transaction is not associated with the current connection. Only transactions associated with the current connection may be used.

There exists very handy documentation, which basically solves the problem.

Cool, but I am not able to create a new instance of EmployeeDbContext, as described in documentation, because I am working with abstraction - interface only. I am looking for some method how to change / swap / inject / replace a DbConnection in existing DbContext.

I was thinking of implementing Clone method like

[HttpPost]
public asycn Task DoSomeWorkAsync()
{
   using (var transaction = this.IEmployeesRepository.BeginTransaction())
   {
       await this.IEmployeesRepository.SaveChangesAsync();

       var payrollRepoClone = IPayrollRepository.Clone(transaction);

       await payrollRepoClone.SaveChangesAsync();
   }
}

and then I would do

public class PayrollDbContext : DbContext, IPayrollRepository
{

   private readonly DbConnection dbConnection;

   public PayrollDbContext Clone(DbTransaction tran)
   {
      return new PayrollDbContext(tran.GetDbTransaction.Connection);
   }

   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   {
        optionsBuilder.UseSqlServer(dbConnection);
   }
}

but I am trying to avoid this kind of tight coupling with SQL Server, because currently UseNpgSql is called in IoC in Service container where I resolve IPayrolRepository instance. And UseInMemoryDatabase in unit tests. This would crash my tests (or at least will require some dirty if/else in OnConfiguring)

Do you have any hints how to inject transaction or dbConnection to existing DbContext?

Thanks

Luke1988
  • 1,850
  • 2
  • 24
  • 42
  • `SaveChanges` already uses a transaction internally while saving *all* pending changes. It already acts as a unit of work. You don't need an explicit transaction and **shouldn't** call `SaveChanges` multiple times, otherwise you break it - which seems to be the case here. Using a "generic repository" on top of a higher level abstraction like an ORM is a bad practice – Panagiotis Kanavos Jan 26 '21 at 14:26
  • A DbContext is not a connection or database model and a DbSet isn't a table. A DbContext already is a multi-entity Unit-of-Work that's supposed to exist only as long as necessary. A DbSet already is a single-entity Repository. By using single-entity "repositories' on top of EF you prevent it from working. You wouldn't need an explicit transaction if you didn't call `SaveChanges` multiple times. You wouldn't need to call `SaveChanges` multiple times on different "entities" if there wasn't a single-type "repository" on top of the multi-entity DbContext – Panagiotis Kanavos Jan 26 '21 at 14:29
  • I call SaveChanges on different DbContexts. Well, a generic repository may be replaced to direct DbContext, but this does not solve the problem that DbContext is (and should be) injected. There are reasons why there is multiple DbContexts, that is definitely not a rare case. Splitting hundred+ tables solves huge cold start issue – Luke1988 Jan 26 '21 at 14:31
  • 1
    You should read *carefully* Gunnar Peipman's [No Need for Repository and Unit of Work with Entity Framework Core](https://gunnarpeipman.com/ef-core-repository-unit-of-work/) to understand why the only way to fix the problem is to remove the inappropriate abstraction. This isn't some new idea or concept, [Repository is the new Singleton](https://ayende.com/blog/3955/repository-is-the-new-singleton) was written by the maintainer of NHibernate back in 2009 – Panagiotis Kanavos Jan 26 '21 at 14:32
  • You are **causing** all those problems by using multiple DbContexts without reason. Again, and I have to *stress this*, a DbContext is a multi-entity UoW. It contains *all necessary entities* for a bounded context/scenario/use-case. The only reason to have multiple DbContexts is to handle multiple scenarios. – Panagiotis Kanavos Jan 26 '21 at 14:35
  • DI doesn't affect DbContext. Without DI, you can just create a DbContext inside a `using` block. If you exit the `using` block without calling `SaveChanges`, you effectively roll back the changes. With DI, the ASP.NET Core middleware disposes the DbContext thus implementing `transaction-per-request`. You don't even need an open connection until you either need to load or persist data. Disconnected operation is a **huge benefit** resulting in 100x-1000x or even better performance and far fewer deadlocks – Panagiotis Kanavos Jan 26 '21 at 14:35
  • I got your poins, I must agree with some of those, but I may not join DbContexts back to single one. We have splitted a monolithic DbContext into multiple due to masive cold start issues. We had about ~30seconds cold start. Now it is less than ~3sec. – Luke1988 Jan 26 '21 at 14:39
  • BTW the link you posted isn't the EF docs. Someone copied a 5 year old copy of the actual [Using Transactions](https://learn.microsoft.com/en-us/ef/core/saving/transactions) section from the documentation – Panagiotis Kanavos Jan 26 '21 at 14:40
  • This means the problem wasn't fixed. There should be no cold start time. If you have 3 second delays just to create a DbContext instance, something's seriously wrong. One bad design was replaced with another bad design and the problem is still there. – Panagiotis Kanavos Jan 26 '21 at 14:42
  • The original design problem is that DbContext was treated as a database model. It's not. It's a UoW containing only the entities needed for a specific bounded context. Why add 100 entities when only 5-6 are used in a specific bounded context? As for why even your current startup is so slow, it's either because you use EF Old and encounter its attempt to generate views and precompile queries for all entities, or use EF Core and encounter its migration test – Panagiotis Kanavos Jan 26 '21 at 14:45
  • The solution in EF Old is to [pre-generate those views](https://learn.microsoft.com/en-us/ef/ef6/fundamentals/performance/pre-generated-views) so EF doesn't have to do so when an application starts – Panagiotis Kanavos Jan 26 '21 at 14:48
  • In any case, the real docs show how to either [enlist an existing transaction](https://learn.microsoft.com/en-us/ef/core/saving/transactions#using-external-dbtransactions-relational-databases-only) or use a [TransactionScope](https://learn.microsoft.com/en-us/ef/core/saving/transactions#using-systemtransactions). Any connections created inside a TransactionScope are automatically enlisted in distributed transaction BUT ... that may only works on Windows. – Panagiotis Kanavos Jan 26 '21 at 15:04
  • The only way to use the same transaction across connections is to use an expensive distributed transaction using MS DTC (which has to be running of course). That's not available on Linux or Mac. Even on Windows, EF does its best to detect whether the connections are all made to the same server and use a single cross-database transaction instead of a distributed transaction. If that fails, it will try to use a DTC transaction. There are no plans to add such support to EF Core when running on Linux, as it's not needed when EF is used properly - DbContexts aren't meant to be single-entity. – Panagiotis Kanavos Jan 26 '21 at 15:06
  • 1
    @PanagiotisKanavos, it is a known EF problem with cold start. It will not work if you do not define all entities. If there are hundred of them - you have cold start delay. – Svyatoslav Danyliv Jan 26 '21 at 15:48
  • @SvyatoslavDanyliv it works perfectly well if you define only the entities you want. Just **don't** add hundreds of entities. You only need a few of them per use case. Again, and again, a DbContext isn't a model of the database. It's a Unit-of-Work for a specific bounded context. What you describe is how EF Old worked before 4.1, a way abandoned as soon as Code-First came out and people stopped using the graphical designer and the double mapping models. – Panagiotis Kanavos Jan 26 '21 at 15:52
  • In any case, there's nothing else to add. EF Coreis made to work in a certain way. If you want to do things differently, you'll encounter performance, scalability and concurrency problems. You can't make it work like EF 3, nor should you apply techniques meant for such old versions. It's a completely different ORM, with its own strengths and weaknesses. If you insist on using a distributed transaction over multiple connections, you'll get into trouble – Panagiotis Kanavos Jan 26 '21 at 15:58

0 Answers0