Introduction

Database operations often involve multiple steps that need to be executed together to maintain data consistency. Entity Framework Core (EF Core) provides a robust way to manage transactions, ensuring that a series of database operations either succeed entirely or fail as a unit. In this article, we will explore how to implement transactions in EF Core with a practical code example. We will add or update data in two tables: Books and Authors, where there is a one-to-many relationship between authors and books i.e. one author can have many books. If an error occurs in any part of the operation, the entire transaction will be rolled back to maintain data integrity.

Prerequisites

Before we begin, make sure you have the following prerequisites installed:

1. Visual Studio or any code editor of your choice.

2. Entity Framework Core (EF Core) installed.

3. A SQL Server or other compatible database.

Creating the DbContext and Entity Classes

We start by defining our DbContext class and the entity classes that represent the Books and Authors tables. In our example, we'll create two entity classes: Book and Author, and establish a one-to-many relationship between them. The DbContext class, LibraryDbContext, will include DbSet properties for these entities.

public class Author
{
    public int AuthorId { get; set; }
    public string Name { get; set; }
    //navigation property (1:N relationship)
    public ICollection<Book> Books { get; set; } 
}

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    [ForeignKey("Author")]
    public int AuthorId { get; set; }
    //navigation property
    public Author Author { get; set; }
}

public class LibraryDbContext : DbContext
{
    public DbSet<Author> Authors { get; set; }
    public DbSet<Book> Books { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Configure your database connection here (e.g., SQL Server)
        optionsBuilder.UseSqlServer("YourConnectionString");
    }
}

Setting up Transactions

Transactions are crucial for ensuring data consistency in a database. We use the System.Transactions namespace to work with transactions in EF Core. In our Main method, we wrap our database operations in a TransactionScope.

using System;
using System.Transactions;
using Microsoft.EntityFrameworkCore;

class Program
{
    static void Main(string[] args)
    {
        // Wrap the database operations in a TransactionScope
        using (var scope = new TransactionScope())
        {
            try
            {
                using (var dbContext = new LibraryDbContext())
                {
                    // Create a new author
                    var author = new Author { Name = "J.K. Rowling" };
                    dbContext.Authors.Add(author);

                    // Create and add new books for the author
                    var book1 = new Book { Title = "Harry Potter and the Sorcerer's Stone", Author = author };
                    var book2 = new Book { Title = "Harry Potter and the Chamber of Secrets", Author = author };
                    dbContext.Books.AddRange(book1, book2);

                    // Save changes to the database
                    dbContext.SaveChanges();
                }

                // If everything succeeded, complete the transaction
                scope.Complete();
            }
            catch (Exception ex)
            {
                // Handle exceptions and log errors here
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

In this code:

1. We create a TransactionScope to encapsulate our database operations. Any exception thrown within this scope will trigger a rollback of the entire transaction.

2. Inside the transaction scope, we create a LibraryDbContext instance (dbContext) to work with our database. We create a new author, add books to the author's collection, and then call SaveChanges to commit the changes to the database.

3. If an exception occurs during the database operations, the catch block will handle the error, and the transaction will be rolled back when the scope is disposed. This ensures that our database remains in a consistent state, even in the face of errors or exceptions.

Implementing the solution in a Web API

To implement the same transactional behavior in a Web API application, you'll need to create API controllers and use dependency injection to inject your repository classes and DbContext into those controllers. Here, I'll provide an example using ASP.NET Core for a Books and Authors Web API.

Create a new ASP.NET Core Web API project

You need to create a basic Web API project using Visual Studio.

Add the Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.InMemory nuget packages to your project

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.InMemory

These packages are crucial for enabling Entity Framework functionality and utilizing an in-memory database for testing.

The .csproj file should look like this afterwards:

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.10" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="7.0.10" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.2.3" />
  </ItemGroup>

</Project>

Set up your entity classes, repository classes, and DbContext

Let's first create a repository pattern for Books and Authors:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Transactions;

public interface IRepository<T> where T : class
{
    IQueryable<T> GetAll();
    T GetById(int id);
    void Add(T entity);
    void Update(T entity);
    void Delete(T entity);
}

public class Repository<T> : IRepository<T> where T : class
{
    private readonly DbContext _context;
    private readonly DbSet<T> _set;

    public Repository(DbContext context)
    {
        _context = context;
        _set = _context.Set<T>();
    }

    public IQueryable<T> GetAll()
    {
        return _set.AsQueryable();
    }

    public T GetById(int id)
    {
        return _set.Find(id);
    }

    public void Add(T entity)
    {
        _set.Add(entity);
    }

    public void Update(T entity)
    {
        _set.Update(entity);
    }

    public void Delete(T entity)
    {
        _set.Remove(entity);
    }
}

public class AuthorRepository : Repository<Author>
{
    public AuthorRepository(DbContext context) : base(context)
    {
    }
}

public class BookRepository : Repository<Book>
{
    public BookRepository(DbContext context) : base(context)
    {
    }
}

Create API controllers for Authors and Books

[Route("api/[controller]")]
[ApiController]
public class AuthorsController : ControllerBase
{
    private readonly AuthorRepository _authorRepository;

    public AuthorsController(AuthorRepository authorRepository)
    {
        _authorRepository = authorRepository;
    }

    // Implement your Author-related API endpoints (e.g., GET, POST, PUT, DELETE).
}

[Route("api/[controller]")]
[ApiController]
public class BooksController : ControllerBase
{
    private readonly BookRepository _bookRepository;

    public BooksController(BookRepository bookRepository)
    {
        _bookRepository = bookRepository;
    }

    // Implement your Book-related API endpoints (e.g., GET, POST, PUT, DELETE).
}

Configure dependency injection for your repositories and DbContext in the Startup.cs file:

csharpCopy codepublic void ConfigureServices(IServiceCollection services)
{
    // Add DbContext
    services.AddDbContext<LibraryDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

    // Add repositories
    services.AddScoped<AuthorRepository>();
    services.AddScoped<BookRepository>();

    // Other configuration...
}

Add Transaction

Now, you can use transactions in your API controllers as follows:

using Microsoft.AspNetCore.Mvc;
using System;
using System.Transactions;

[Route("api/[controller]")]
[ApiController]
public class LibraryController : ControllerBase
{
    private readonly AuthorRepository _authorRepository;
    private readonly BookRepository _bookRepository;
    private readonly LibraryDbContext _dbContext;

    public LibraryController(AuthorRepository authorRepository, BookRepository bookRepository, LibraryDbContext dbContext)
    {
        _authorRepository = authorRepository;
        _bookRepository = bookRepository;
        _dbContext = dbContext;
    }

    [HttpPost]
    [Route("addAuthorAndBooks")]
    public IActionResult AddAuthorAndBooks()
    {
        // Wrap the database operations in a TransactionScope
        using (var scope = new TransactionScope())
        {
            try
            {
                // Create a new author
                var author = new Author { Name = "J.K. Rowling" };
                _authorRepository.Add(author);

                // Create and add new books for the author
                var book1 = new Book { Title = "Harry Potter and the Sorcerer's Stone", Author = author };
                var book2 = new Book { Title = "Harry Potter and the Chamber of Secrets", Author = author };
                _bookRepository.Add(book1);
                _bookRepository.Add(book2);

                // Save changes to the database
                _dbContext.SaveChanges();

                // If everything succeeded, complete the transaction
                scope.Complete();

                return Ok("Author and books added successfully.");
            }
            catch (Exception ex)
            {
                // Handle exceptions and log errors here
                return BadRequest($"Error: {ex.Message}");
            }
        }
    }
}

In this example:

• We create an API controller called LibraryController, which injects the AuthorRepository, BookRepository, and LibraryDbContext using dependency injection.

• The AddAuthorAndBooks method represents an API endpoint that adds an author and associated books within a transaction.

• Inside the TransactionScope, we perform the database operations as discussed earlier, including adding an author and books.

• If everything succeeds, we call scope.Complete() to commit the transaction. Otherwise, if an exception occurs, the transaction is rolled back automatically.

• Appropriate HTTP responses are returned based on the success or failure of the transaction.

This example demonstrates how to use transactions in a Web API application, ensuring that data integrity is maintained even when working with multiple database operations.

Conclusion

Implementing transactions in Entity Framework Core is essential for maintaining data consistency during complex database operations. In this article, we demonstrated how to do this with a one-to-many relationship between Books and Authors. By wrapping our operations in a TransactionScope, we can ensure that either all changes are committed successfully or none of them are. This guarantees the integrity of our data, even in the face of errors or exceptions. In your own applications, be sure to replace "YourConnectionString" with the actual database connection string and handle exceptions according to your specific requirements.