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

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.