Using System.Transaction how to update multiple rows in Entity Framework

Question

I want to use System.Transactions and update multiple rows. My database is connected using Entity Framework.

Below is the code I tried but it throws an error :

public  void Update(List<PortfolioCompanyLinkModel> record)
{
    var transaction = _context.Database.BeginTransaction();

    try
    {
        foreach (var item in record)
        {
            var portfolioCompanyLink = _context.PortfolioCompanyLink.FirstOrDefault(p => p.Id == item.Id);
            portfolioCompanyLink.ModifiedBy = _loggedInUser;
            portfolioCompanyLink.ModifiedOn = DateTime.UtcNow;
            portfolioCompanyLink.URL = item.URL;

            _context.SaveChanges();
            //_context.PortfolioCompanyLink.Update(portfolioCompanyLink);
        }

        transaction.Commit();
    }
    catch(Exception ex) 
    {
        transaction.Rollback();
    }
}

Error:

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.

Can someone help me on how to proceed with this?

Answer 1

Ive struggled with this for a few weeks and decided to share my solution, to broadly use in our library

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
    
public class Transactions {

    private Transactions() {}

    public static void Run(DbContext dbContext, Func<IDbContextTransaction, bool> act) {
        if(dbContext != null && act != null) {
            var executionStrategy = dbContext.Database.CreateExecutionStrategy();

            executionStrategy.Execute(() => {
                
                using var ret = dbContext.Database.BeginTransaction();
                if(ret != null) {
                    
                    try {
                        if(act.Invoke(ret)) {
                            ret.Commit();
                        }
                    } catch (Exception e) {
                        ret.Rollback();
                        throw new Exception("Error during transaction, rolling back");
                    }

                } else {
                    throw new Exception("Error while starting transaction");
                }

            });
        }
    }

}

So one might use it like this

Transactions.Run(dbContext, (trans) => {

    trackedEntity.property = "updated valued";
    dbContext.SaveChanges();
    return true; // this is to give some control over the transaction if needed, so one might use savepoints etc

});

Answer 2

You problem is the SqlServerRetryingExecutionStrategy as described in Microsoft documentation

When not using a retrying execution strategy you can wrap multiple operations in a single transaction. For example, the following code wraps two SaveChanges calls in a single transaction. If any part of either operation fails then none of the changes are applied.

MS docs on resiliency

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.

Solution: Manually Call Execution Strategy

var executionStrategy = _context.db.CreateExecutionStrategy();

executionStrategy.Execute(
    () =>
    {
        // execute your logic here
        using(var transaction = _context.Database.BeginTransaction()) 
        {
            try
            {
                foreach (var item in record)
                {
                    var portfolioCompanyLink = _context.PortfolioCompanyLink.FirstOrDefault(p => p.Id == item.Id);
                    portfolioCompanyLink.ModifiedBy = _loggedInUser;
                    portfolioCompanyLink.ModifiedOn = DateTime.UtcNow;
                    portfolioCompanyLink.URL = item.URL;
                    _context.SaveChanges();
                    //_context.PortfolioCompanyLink.Update(portfolioCompanyLink);
                }

                transaction.Commit();
            }
            catch(Exception ex) {
                transaction.Rollback();
            }
        }
    });

You can set the strategy globally too, but that depends on what you are trying to achieve.