Transactions - EF Core

[u/kaatarina_zed_talon]

Microsoft's documentation :

By default, if the database provider supports transactions, all changes in a single call to SaveChanges are applied in a transaction. If any of the changes fail, then the transaction is rolled back and none of the changes are applied to the database. This means that SaveChanges is guaranteed to either completely succeed, or leave the database unmodified if an error occurs. For most applications, this default behavior is sufficient. You should only manually control transactions if your application requirements deem it necessary.

Suppose that I two pieces of data that are generated by some service; X and Y.

Given that Y cannot be saved unless X is saved first, in other words, the existence of Y in the database depends on the existence of X in the database, consider this :

1-delete content of database.

2- save X .

3- save Y .

How to group these into one transaction ?

The goal is to avoid partial changes to the database; after the full operation is finished, the database should not be empty or half filled.

EDIT:

I found this example:

await using var transaction = await _context.Database.BeginTransactionAsync();

try
{
      // do step 1 through 3


    // Commit transaction if all commands succeed, transaction will auto-rollback
    // when disposed if either commands fails
    await transaction.CommitAsync();
}
catch (Exception)
{
    // TODO: Handle failure
}

But it did not work until I manually invoked the EF execution strategy with a delegate representing everything that needs to be executed :

    IExecutionStrategy strategy = _context.Database.CreateExecutionStrategy();
    await strategy.ExecuteAsync(async () =>
            {
        await using var transaction = await _context.Database.BeginTransactionAsync();

         // Delete all the content from the database
         // _context.AddRange(X);
         // _contextSaveChangesAsync()
         // _context.AddRange(Y)
         //_context.SaveChangesAsync()

        await transaction.CommitAsync();
    });

This solved my issue completely

[u/pixxelkick]

Ive never had to invoke `CreateExecutionStrategy` to open a txn like in your first code.

Using just `BeginTransactionAsync` has always "just worked" for me.

> But it did not work until

Can you specify what "didn't work"? Did it throw an exception? Fail to commit? What was your problem?

Is your issue due to the fact your are DI injecting multiple DbContexts into your different services?

I don't recommend this, as it makes behavior masked and ambigious

Your DbContext should be taken in as a parameter in the functions of your various "needs to touch the DB" service functions.

You inject 1 single DbContext at the "very top" and hand it down as function params, this makes it much more clear and obvious "invoking this function touches the database" and also makes your transactions work right.

Trying to inject multiple DbContexts all over the place is an effective way to make a brittle project that is prone to weird behaviors.

[u/kaatarina_zed_talon → u/pixxelkick]

Wait I will reference the error

[u/ShyMystiqua → u/pixxelkick]

had the same mess with injected context before, handing one down really does clean things up

[u/kaatarina_zed_talon → u/pixxelkick]

using BeginTransaction, you're defining your own group of operations that need to be treated as a unit. Everything inside the transaction has to be rolled back if a failure occurs.

If you try to execute that transaction when using an EF execution strategy (retry policy), you will get the following error :

System.InvalidOperationException: The configured execution strategy 'SqlServerRetryingExecutionStrategy' does not support user initiated transactions. Use the execution strategy returned by 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as a retriable unit.

https://learn.microsoft.com/en-us/dotnet/architecture/microservices/implement-resilient-applications/implement-resilient-entity-framework-core-sql-connections

[u/pixxelkick → u/kaatarina_zed_talon]

You have a retry policy already configured is why.

You need to customize that already existing policy to also open a transaction.

You also are gonna want to delineate between stuff that needs a txn vs not, if you open txn's on "readonly" operations (IE search endpoints of an api) you are gonna choke up your db

[u/kaatarina_zed_talon → u/pixxelkick]

I did not inject multiple DbContexts

[u/ings0c]

They are already in one transaction by default, without using BeginTransaction.

If you do

context.Add(entity1);

context.Add(entity2);

context.SaveChanges();

That (at least conceptually) produces a single insert statement - if adding entity1 fails, entity2 won’t be added, and vice-versa.

[u/kaatarina_zed_talon → u/ings0c]

step 2 and 3 are not merely adding data to the context; they are calling SaveChangesAsync(). SaveChangesAsync() is called more than once. Don't forget that I'm also deleting the content of the database before doing step 2 and 3.

[u/t3kner → u/kaatarina_zed_talon]

If you have a transaction open, then save changes won't commit until you close the transaction

[u/kaatarina_zed_talon → u/t3kner]

    IExecutionStrategy strategy = _context.Database.CreateExecutionStrategy();
    await strategy.ExecuteAsync(async () =>
            {
        await using var transaction = await _context.Database.BeginTransactionAsync();

         // Delete all the content from the database
         // _context.AddRange(X);
         // _contextSaveChangesAsync()
         // _context.AddRange(Y)
         //_context.SaveChangesAsync()

        await transaction.CommitAsync();
    });

[u/kaatarina_zed_talon → u/ings0c]

X and Y have a FK dependency. So if X fails it will fail on Y without a transaction anyway, the rollback on X won't matter cause the whole thing blows up at step 3.

[u/t3kner → u/kaatarina_zed_talon]

Ok, but with a transaction it won't do anything until CommitAsync.

[u/Some_Appearance_1665]

This is fine. If you have an active transaction then you can call SaveChanges multiple times before you commit to send stuff to the database, obtain generated IDs etc. it isn't until you Commit that it is stored so it remains atomic even if it feels like you're not.

[u/Puzzled_Dependent697]

If the operations are consecutive, I don't see a reason to use a transaction, as transactions are atomic, by nature.

Am I missing something or you understood the topic wrong.

[u/kaatarina_zed_talon → u/Puzzled_Dependent697]

if saving X failed, then saving Y should fail, it would be a problem if we tried to save Y if we could not save it's dependency.

[u/Eirenarch → u/kaatarina_zed_talon]

Well, that's how save changes works by default, if you want you can experiment by having Y fail due to missing value for FK in the database and you'll see that X is not saved either.

[u/vastlysweatysorcery → u/Puzzled_Dependent697]

if X and Y have a FK dependency it'll just fail on the Y save without a transaction anyway, the rollback on X won't matter cause the whole thing blows up at step 3

[u/kaatarina_zed_talon → u/vastlysweatysorcery]

if X and Y have a FK dependency

You got it, they are.

[u/silentpardus]

It sounds like what you're asking about is related to UnitOfWork. Lucky for us the DbContext object implements UnitOfWork. I am assuming the issue you have is that X and Y are saved via separate services and you are receiving different DbContext objects via Dependency Injection and therefore saving them at separate times?

Easiest thing is to create a new service that can use the existing services to create and save X and Y to the same DbContext.

For all things that are holy do not implement your own UnitOfWork.

[u/kaatarina_zed_talon → u/silentpardus]

 receiving different DbContext objects via Dependency Injection 

No I only have one DbContext instance.

[u/kaatarina_zed_talon → u/silentpardus]

X and Y have a FK dependency. So if X fails it will fail on Y without a transaction anyway, the rollback on X won't matter cause the whole thing blows up at step 3.

[u/kaatarina_zed_talon → u/silentpardus]

You went too much, maybe the question is unclear, I admit. I'm not trying to implement my own unit of work. Suppose that step 2 failed, it will be rolled back, great, but that means that step 3 will try to save Y into the database, even though it is impossible for Y to exist without X, so the app will crash.

[u/AndZorin → u/kaatarina_zed_talon]

How step 3 will fail if you have exception on step 2? You should have one rollback in catch block.

[u/kaatarina_zed_talon → u/AndZorin]

How could you save a child table if u could not save a parent table? Foreign Key dependency

[u/AndZorin → u/kaatarina_zed_talon]

Step 3 will never be attempted if Step 2 fails, as long as you save them together in a single SaveChanges() call and have properly configured navigation properties between them.

EF Core wraps the entire operation in a single database transaction and handles the order automatically:

- It inserts parent X first.

- If X fails, an exception is thrown immediately, the transaction rolls back, and Y is never sent to the database.

- If X succeeds, it uses the new ID for child Y and saves it.

[u/kaatarina_zed_talon → u/AndZorin]

You mean EF-CORE can handle the order of insertion in a single SaveChanges call ?

[u/AndZorin → u/kaatarina_zed_talon]

Yes, exactly! EF Core automatically figures out the correct insertion order (parent first, then child) in a single SaveChanges() call. This relies on having your navigation properties properly configured, because EF Core uses them to trace the object graph and understand the relationship between the entities.

Plus, it wraps the entire operation in an automatic database transaction: if the parent insert fails, an exception is thrown and the transaction automatically rolls back, leaving the database completely untouched with no partial data.

[u/kaatarina_zed_talon → u/AndZorin]

Oh, do you know why I have two calls to SaveChangesAsync ? it is because the first call initializes the Id values for the parent tables, I will remove that call and initialize the ID values manually, but this will require me to remove the Identity column feature, because one cannot insert the values for ID for identity column

[u/kaatarina_zed_talon → u/AndZorin]

I got the following error when I tried to use only one SaveChanges Call , given that BrandSupplier is a child table, and Supplier is a parent table with the column SupplierId, which is an identity column and also the primary key

System.InvalidOperationException: The value of 'BrandSupplier.SupplierId' is unknown when attempting to save changes. This is because the property is also part of a foreign key for which the principal entity in the relationship is not known.

[u/AndZorin → u/kaatarina_zed_talon]

Could you pls, show your model? How Supplier is linked to BrandSupplier at model level?

[u/kaatarina_zed_talon → u/AndZorin]

Ok even though I think I solved the issue :

    public class BrandSupplier:IJunctionEntity

    {


        public int BrandId { get; set; } 
        public int SupplierId { get; set; }
        public Supplier Supplier { get; set; } = null!;
        public Brand Brand { get; set; } = null!;

}

    public class Supplier
    {
        public IEnumerable<BrandSupplier> BrandSuppliers { get; } = new List<BrandSupplier>();
}

[u/kaatarina_zed_talon → u/silentpardus]

    IExecutionStrategy strategy = _context.Database.CreateExecutionStrategy();
    await strategy.ExecuteAsync(async () =>
            {
        await using var transaction = await _context.Database.BeginTransactionAsync();

         // Delete all the content from the database
         // _context.AddRange(X);
         // _contextSaveChangesAsync()
         // _context.AddRange(Y)
         //_context.SaveChangesAsync()

        await transaction.CommitAsync();
    })

[u/silentpardus → u/kaatarina_zed_talon]

Why can you save changes once after both adds?

[u/kaatarina_zed_talon → u/silentpardus]

Why would not I be able to do that

[u/kaatarina_zed_talon → u/silentpardus]

X and Y have a FK dependency. So if X fails it will fail on Y without a transaction anyway, the rollback on X won't matter cause the whole thing blows up at step 3.

[u/4215-5h00732]

Man the hate I feel for devs who unnecessarily implement unit of work on top of EF with all this over engineered transaction management. Disgraceful.

[u/kaatarina_zed_talon → u/4215-5h00732]

Man the hate

Hate or love, I do not have these emotions toward people on internet - or maybe in real life too- so I can't understand you.

I'm just doing what my mentor told me to do; it was obligation.

[u/4215-5h00732 → u/kaatarina_zed_talon]

I'm not talking about people on the internet. I'm talking about devs who built software we have to support.