How to nest transactions in EF Core 6?

Question

I am already using transactions inside my repository functions in some cases because I sometimes need to insert data into two tables at once and I want the whole operation to fail if one of the inserts fails.

Now I ran into a situation where I had to wrap calls to multiple repositories / functions in another transaction, but when one of those functions already uses a transaction internally I will get the error The connection is already in a transaction and cannot participate in another transaction.

I do not want to remove the transaction from the repository function because this would mean that I have to know for which repository functions a transaction is required which I would then have to implement in the service layer. On the other hand, it seems like I cannot use repository functions in a transaction when they already use a transaction internally. Here is an example for where I am facing this problem:

// Reverse engineered classes
public partial class TblProject
{
    public TblProject()
    {
        TblProjectStepSequences = new HashSet<TblProjectStepSequence>();
    }
    
    public int ProjectId { get; set; }

    public virtual ICollection<TblProjectStepSequence> TblProjectStepSequences { get; set; }
}

public partial class TblProjectTranslation
{
    public int ProjectId { get; set; }
    public string Language { get; set; }
    public string ProjectName { get; set; }

    public virtual TblProject Project { get; set; }
}

public partial class TblProjectStepSequence
{
    public int SequenceId { get; set; }
    public int ProjectId { get; set; }
    public int StepId { get; set; }
    public int SequencePosition { get; set; }

    public virtual TblStep Step { get; set; }
    public virtual TblProject Project { get; set; }
}

// Creating a project in the ProjectRepository
public async Task<int> CreateProjectAsync(TblProject project, ...)
{
    using (var transaction = this.Context.Database.BeginTransaction())
    {
        await this.Context.TblProjects.AddAsync(project);
        await this.Context.SaveChangesAsync();
        // Insert translations... (project Id is required for this)
        await this.Context.SaveChangesAsync();
        transaction.Commit();
        
        return entity.ProjectId;
    }
}

// Creating the steps for a project in the StepRepository
public async Task<IEnumerable<int>> CreateProjectStepsAsync(int projectId, IEnumerable<TblProjectStepSequence> steps)
{
    await this.Context.TblProjectStepSequences.AddRangeAsync(steps);
    await this.Context.SaveChangesAsync();

    return steps.Select(step =>
    {
        return step.SequenceId;
    }
    );
}

// Creating a project with its steps in the service layer
public async Task<int> CreateProjectWithStepsAsync(TblProject project, IEnumerable<TblProjectStepSequence> steps)
{
    // This is basically a wrapper around Database.BeginTransaction() and IDbContextTransaction
    using (Transaction transaction = await transactionService.BeginTransactionAsync())
    {
        int projectId = await projectRepository.CreateProjectAsync(project);
        await stepRepository.CreateProjectStepsAsync(projectId, steps);

        return projectId;
    }
}

Is there a way how I can nest multiple transactions inside each other without already knowing in the inner transactions that there could be an outer transaction?

I know that it might not be possible to actually nest those transactions from a technical perspective but I still need a solution which either uses the internal transaction of the repository or the outer one (if one exists) so there is no way how I could accidentally forget to use a transaction for repository functions which require one.

Answer 1

You could check the CurrentTransaction property and do something like this:

var transaction = Database.CurrentTransaction ?? Database.BeginTransaction()

If there is already a transaction use that, otherwise start a new one...

Edit: Removed the Using block, see comments. More logic is needed for Committing/Rollback the transcaction though...

Answer 2

I am answering the question you asked "How to nest transactions in EF Core 6?"

Please note that this is just a direct answer, but not an evaluation what is best practice and what not. There was a lot of discussion going around best practices, which is valid to question what fits best for your use case but not an answer to the question (keep in mind that Stack overflow is just a Q+A site where people want to have direct answers).

Having said that, let's continue with the topic:

Try to use this helper function for creating a new transaction:

public CommittableTransaction CreateTransaction() 
    => new System.Transactions.CommittableTransaction(new TransactionOptions()
    {
        IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
    });

Using the Northwind database as example database, you can use it like:

public async Task<int?> CreateCategoryAsync(Categories category)
{         
    if (category?.CategoryName == null) return null;
    using(var trans = CreateTransaction()) 
    {
        await this.Context.Categories.AddAsync(category);
        await this.Context.SaveChangesAsync();

        trans.Commit();

        return category?.CategoryID;
    }
}

And then you can call it from another function like:

/// <summary>Create or use existing category with associated products</summary>
/// <returns>Returns null if transaction was rolled back, else CategoryID</returns>
public async Task<int?> CreateProjectWithStepsAsync(Categories category)
{
    using var trans = CreateTransaction();
    
    int? catId = GetCategoryId(category.CategoryName) 
                ?? await CreateCategoryAsync(category);

    if (!catId.HasValue || string.IsNullOrWhiteSpace(category.CategoryName))
    {
        trans.Rollback(); return null;
    }
    
    var product1 = new Products()
    {
        ProductName = "Product A1", CategoryID = catId
    };
    await this.Context.Products.AddAsync(product1);
    
    var product2 = new Products()
    {
        ProductName = "Product A2", CategoryID = catId
    };
    await this.Context.Products.AddAsync(product2);

    await this.Context.SaveChangesAsync();

    trans.Commit();
    
    return catId;
}

To run this with LinqPad you need an entry point (and of course, add the NUGET package EntityFramework 6.x via F4, then create an EntityFramework Core connection):

// Main method required for LinqPad
UserQuery Context;

async Task Main()
{
    Context = this;
    var category = new Categories()
    {
        CategoryName = "Category A1"
        // CategoryName = ""
    };
    var catId = await CreateProjectWithStepsAsync(category);
    Console.WriteLine((catId == null) 
           ? "Transaction was aborted." 
           : "Transaction successful.");
}

This is just a simple example - it does not check if there are any product(s) with the same name existing, it will just create a new one. You can implement that easily, I have shown it in the function CreateProjectWithStepsAsync for the categories:

int? catId = GetCategoryId(category.CategoryName) 
            ?? await CreateCategoryAsync(category);

First it queries the categories by name (via GetCategoryId(...)), and if the result is null it will create a new category (via CreateCategoryAsync(...)).

Also, you need to consider the isolation level: Check out System.Transactions.IsolationLevel to see if the one used here (ReadCommitted) is the right one for you (it is the default setting).

What it does is creating a transaction explicitly, and notice that here we have a transaction within a transaction.

---

Note:

• I have used both ways of using - the old one and the new one. Pick the one you like more.

Answer 3

Just don't call SaveChanges multiple times.

The problem is caused by calling SaveChanges multiple times to commit changes made to the DbContext instead of calling it just once at the end. It's simply not needed. A DbContext is a multi-entity Unit-of-Work. It doesn't even keep an open connection to the database. This allows 100-1000 times better throughput for the entire application by eliminating cross-connection blocking.

A DbContext tracks all modifications made to the objects it tracks and persists/commits them when SaveChanges is called using an internal transaction. To discard the changes, simply dispose the DbContext. That's why all examples show using a DbContext in a using block - that's actually the scope of the Unit-of-Work "transaction".

There's no need to "save" parent objects first. EF Core will take care of this itself inside SaveChanges.

Using the Blog/Posts example in the EF Core documentation tutorial :

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    public string DbPath { get; }

    // The following configures EF to create a Sqlite database file in the
    // special "local" folder for your platform.
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlServer($"Data Source=.;Initial Catalog=tests;Trusted_Connection=True; Trust Server Certificate=Yes");
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; } = new();
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

The following Program.cs will add a Blog with 5 posts but only call SaveChanges once at the end :

using (var db = new BloggingContext())
{
    Blog blog = new Blog { Url = "http://blogs.msdn.com/adonet" };
    IEnumerable<Post> posts = Enumerable.Range(0, 5)
                                       .Select(i => new Post {
                                            Title = $"Hello World {i}",
                                            Content = "I wrote an app using EF Core!"
                                         });
    blog.Posts.AddRange(posts);

    db.Blogs.Add(blog);

    await db.SaveChangesAsync();
}

The code never specifies or retrieves the IDs. Add is an in-memory operation so there's no reason to use AddAsync. Add starts tracking both the blog and the related Posts in the Inserted state.

The contents of the tables after this are :

select * from blogs

select * from posts;
-----------------------

BlogId  Url
1   http://blogs.msdn.com/adonet

PostId  Title   Content BlogId
1   Hello World 0   I wrote an app using EF Core!   1
2   Hello World 1   I wrote an app using EF Core!   1
3   Hello World 2   I wrote an app using EF Core!   1
4   Hello World 3   I wrote an app using EF Core!   1
5   Hello World 4   I wrote an app using EF Core!   1

Executing the code twice will add another blog with another 5 posts.

PostId  Title   Content BlogId
1   Hello World 0   I wrote an app using EF Core!   1
2   Hello World 1   I wrote an app using EF Core!   1
3   Hello World 2   I wrote an app using EF Core!   1
4   Hello World 3   I wrote an app using EF Core!   1
5   Hello World 4   I wrote an app using EF Core!   1
6   Hello World 0   I wrote an app using EF Core!   2
7   Hello World 1   I wrote an app using EF Core!   2
8   Hello World 2   I wrote an app using EF Core!   2
9   Hello World 3   I wrote an app using EF Core!   2
10  Hello World 4   I wrote an app using EF Core!   2

Using SQL Server XEvents Profiler shows that these SQL calls are made:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Blogs] ([Url])
VALUES (@p0);
SELECT [BlogId]
FROM [Blogs]
WHERE @@ROWCOUNT = 1 AND [BlogId] = scope_identity();
',N'@p0 nvarchar(4000)',@p0=N'http://blogs.msdn.com/adonet'

exec sp_executesql N'SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([PostId] int, [_Position] [int]);
MERGE [Posts] USING (
VALUES (@p1, @p2, @p3, 0),
(@p4, @p5, @p6, 1),
(@p7, @p8, @p9, 2),
(@p10, @p11, @p12, 3),
(@p13, @p14, @p15, 4)) AS i ([BlogId], [Content], [Title], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([BlogId], [Content], [Title])
VALUES (i.[BlogId], i.[Content], i.[Title])
OUTPUT INSERTED.[PostId], i._Position
INTO @inserted0;
SELECT [i].[PostId] FROM @inserted0 i
ORDER BY [i].[_Position];
',N'@p1 int,@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 int,@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 int,@p8 nvarchar(4000),@p9 nvarchar(4000),@p10 int,@p11 nvarchar(4000),@p12 nvarchar(4000),@p13 int,@p14 nvarchar(4000),@p15 nvarchar(4000)',@p1=3,@p2=N'I wrote an app using EF Core!',@p3=N'Hello World 0',@p4=3,@p5=N'I wrote an app using EF Core!',@p6=N'Hello World 1',@p7=3,@p8=N'I wrote an app using EF Core!',@p9=N'Hello World 2',@p10=3,@p11=N'I wrote an app using EF Core!',@p12=N'Hello World 3',@p13=3,@p14=N'I wrote an app using EF Core!',@p15=N'Hello World 4'

The unusual SELECT and MERGE are used to ensure IDENTITY values are returned in the order the objects were inserted, so EF Core can assign them to the object properties. After calling SaveChanges all Blog and Post objects will have the correct database-generated IDs