Shared DbContext Transaction using ASP.NET Core 2.0 MVC/EF

.net-core asp.net-core asp.net-core-mvc entity-framework-core relational-database

Question

If anyone could set me straight on this, much appreciated!!!

Apologies for any re-tread, I have seen many examples, but not any that address a MVC pattern.

My dilemma is an implementation of a MVC Controller that can process multiple requests and Db operations, but never commit anything until the operator has finished the requisite processes.

For Example, add a new Parent record and return record info, with generated Id PrimaryKey. Now operator can add Children records by passing along the requisite ForiegnKey. Some of these Child records may have children of their own, etc. The operator must get through the whole process before ANYTHING is committed.

Unless I’m completely off, I can’t figure this out. I ran into these:

How to make a transaction in asp.net-core 2.0?

Alternative to TransactionScope of System.Transaction assembly in .net core framework

From what I understand, System.Transactions is currently available in .Net Core 2.0.x. as pointed out in 3568202:

https://docs.microsoft.com/en-us/dotnet/api/system.transactions.transactionscope?view=netcore-2.0

https://docs.microsoft.com/en-us/dotnet/framework/data/transactions/writing-a-transactional-application

https://docs.microsoft.com/en-us/dotnet/framework/data/transactions/managing-concurrency-with-dependenttransaction which has a WorkerThread example I played around with a bit.

But the example does not exactly show where Transactions.Current comes from. Here is where I hit a wall in my Controller.

I could not figure out the transition from a IDbContextTransaction derived from MyDbContext.Database.BeginTransaction(); and the System.Transactions stuff.

As there is no .DependentClone() for transactions created with IDbContextTransaction.

I was missing something, an Interface? I could not put it together…

Then I ran into this, which looked a little more straightforward:

https://docs.microsoft.com/en-us/ef/core/saving/transactions#cross-context-transaction-relational-databases-only

Nothing to do with TransactionScope, and the first line seemed to be salvation:

“You can also share a transaction across multiple context instances.”

After attempting to implement the example in a Controller, I continuously get the dreaded:

“InvalidOperationException: The specified transaction is not associated with the current connection. Only transactions associated with the current connection may be used.”

Also, most importantly even, in all my perusing it seems to indicate that not much, if any of this is possible using an injected context???!!! Huh, What, Why?

And how would one employ the correct “using” pattern???

I am Using .Net Core 2.0.5 and VS 2017 15.5.3

BTW – I am not using a Repository here, but if a solution requires a repository, I am all in on that idea.

Here is the basic setup I am looking at

public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    { }

    public DbSet<Source> Sources { get; set; }
    public DbSet<Comment> Comments { get; set; }
    public DbSet<DwsFileInfo> DwsFileInfo { get; set; }
}

Then in Startup.ConfigureServices

services.AddDbContext<MyDbContext>(options =>
   options.UseSqlServer(Configuration.GetConnectionString("MyConnection")));

Then the Controller:

public class MyController : Controller
{
    private MyDbContext MyDbContext { get; set; }
    private static IDbContextTransaction RootTransaction { get; set; }
    //private DependentTransaction DependentTransaction { get; set; }
    //private TransactionScope ScopedTransaction { get; set; }
    public CommentsController(MyDbContext context)
    {
        MyDbContext = context;
        if (RootTransaction == null)
        {
            RootTransaction = MyDbContext.Database.BeginTransaction();
        }
        else
        {
            MyDbContext.Database.UseTransaction(RootTransaction.GetDbTransaction());
        }
    }

    /// <summary>
    /// Not sure I really need this?!?
    /// </summary>
    ~CommentsController()
    {
        if (RootTransaction != null && RootTransaction.GetDbTransaction() != null)
        {
            RootTransaction.Rollback();
        }
        RootTransaction.Dispose();
        MyDbContext.Dispose();
    }

    public IActionResult Add(object recordinfo)
    {
        // DB operations
    }

    public IActionResult Add2(object recordinfo)
    {
        // DB operations
    }

    public IActionResult Edit(object recordinfo)
    {
        // Db Operations
    }

    /// <summary>
    /// something here to manage, instead of in the constructor 
    /// or relying on the destructor??
    /// </summary>
    private void ManageTransaction()
    {
        //commit
        //rollback
        //dispose
    }
}

I already inject some application specific info into the context using:

app.Use(next => context =>
{
    string path = context.Request.Path;
    //modify context accordingly per path
    return next(context);
});

But couldn't devine exactly what "modify accordingly" meant...And now I find myself at the mercy of StackOverflow... Everybody's time and consideration is greatly appreciated. Many Thanks!!!

1
1
1/16/2018 2:12:31 PM

Accepted Answer

TL;DR: The saga pattern helps you solve your problem without ambient/database related transactions. Sagas are transactions too, but in a different way, more like a state machine.

My dilemma is an implementation of a MVC Controller that can process multiple requests and Db operations, but never commit anything until the operator has finished the requisite processes.

That's your first misunderstanding. That's how Web and the Internet works. Http is stateless. You can't store state with Http and all data required for a single request should be sent with the request itself.

Or you change the design to allow persisting the parent record and then later add the child objects in subsequent requests. Or make your client send them all together:

Json Example:

{
    // EF core assigns a new key when the key is null or has the default value
    parentId: 0,
    name: "Parent",
    children: [{
        name: "Child 1",
    },{
        name: "Child 2",
    }]
}

Now you have all data in a single request and can process it in a single operation. In ages of jQuery, Angular etc. its not an issue.

Some of these Child records may have children of their own, etc. The operator must get through the whole process before ANYTHING is committed.

You are thinking like when you develop a desktop application. But the web is not a desktop application and you can't transfer state via http as its a stateless protocol.

You must adapt your design and use a more web-friendly process. Or just create a desktop application if that's an absolute requirement ;)

After attempting to implement the example in a Controller, I continuously get the dreaded:

“InvalidOperationException: The specified transaction is not associated with the current connection. Only transactions associated with the current connection may be used.”

Also, most importantly even, in all my perusing it seems to indicate that not much, if any of this is possible using an injected context???!!! Huh, What, Why?

Not in the way you want it, not with transaction scopes. TransactionScope is meant to perform multiple database operations to guarantee consistency. It's not meant when the data will come asynchronously and delayed at any possible time.

BTW – I am not using a Repository here, but if a solution requires a repository, I am all in on that idea.

Well you are using a repository. EntityFramework is an implementation of Unit Of Work and Repository pattern. DbContext is the Unit of Work, while the DbSet<T> properties are the repositories.

But people usually tend to abstract EF Core away behind an repository of its own.

The way you want it won't work, because the DbContext has to be scoped (or you risk memory leaks). Also DbContext is not thread-safe so it can only be safely used by a single thread. That's why it defaults to scoped, trying to access it from another thread will just thrown an invalid operation exception.

One solution: Saga / Process Managers

Last but not least, if you have long-running transactions where the data can come in any given order and at any given time, there is a pattern at hand.

It's called process manager / sagas. A saga or process manager is a long running process. For example one operation can happen now and 2 minutes later the next one. The third one may even take an hour.

The saga acts similar to a state machine. It will receive a message (command) to perform a certain operation and then persists it state (to a database, memory, distributed cache, session, whatever is suitable for your scenario).

Then some undefined time later, the second command comes and performs some operation on it. And even later a third command. Only when the saga is in a specific condition it can be commited as a whole.

Imagine, you want to go to vacations. Now you want to book your flight and the hotel.

  • The first operation may be to choose a destination hotel.
  • Second operation is to book a flight.
  • Now you have to wait until your booking is confirmed. The airline needs to get your payment data, ask the payment company and wait until the money transaction is confirmed
  • When your flight is confirmed, you want to book a hotel room.
  • The hotel needs to wait until the payment is confirmed by the payment provider. Once its confirmed it confirms your hotel room booking.

Only now after both your hotel and your flight are confirmed your vacation can be "commited". For example if the hotel company declines your request, you can't go to vacation. You may then either issue another command (look for another hotel) or cancel your vacation. When you cancel the vacation, you also need to cancel your flight.

This equals to a roll back in transaction.

In a saga you would just delete the registered records or put the "vacation saga" in an "canceled" state.

2
1/16/2018 6:08:45 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow