How to unit-test transactions in Entity Framework Core?

Question

I have a method that does some work in a transaction:

public async Task<int> AddAsync(Item item)
{
    int result;

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

        // Save the item so it has an ItemId
        result = await _context.SaveChangesAsync();

        // perform some actions using that new item's ItemId
        _otherRepository.Execute(item.ItemId);

        transaction.Commit();
    }

    return result;
}

I'd like to add unit tests to check that if _context.SaveChangesAsync or _otherRepository.Execute fail then the transaction is rolled back, is that possible?

I can't see a way to do that using InMemory or SQLite?

Answer 1

@Ilya Chumakov's excellent answer allowed me to unit test for the transaction. Our discussion in the comments then exposed some interesting points that I thought were worth moving into an answer so they'd be more permanent and easier to see:

The primary point is that the events logged by Entity Framework change dependent on the database provider, which surprised me. If using the InMemory provider you get just one event:

1. Id:1; ExecutedCommand

Whereas if you use Sqlite for the in-memory database you get four events:

1. Id:1; ExecutedCommand
2. Id:5; BeginningTransaction
3. Id:1; ExecutedCommand
4. Id:6; CommittingTransaction

I hadn't expected the events logged to change depending on the DB provider.

To anyone wanting to look into this more, I captured the event details by changing Ilya's logging code as follows:

    public class FakeLogger : ILogger
    {
        public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception,
            Func<TState, Exception, string> formatter)
        {
            var record = new LogRecord
            {
                EventId = eventId.Id,
                RelationalEventId = (RelationalEventId) eventId.Id,
                Description = formatter(state, exception)
            };

            Events.Add(record);

        }

        public List<LogRecord> Events { get; set; } = new List<LogRecord>();

        public bool IsEnabled(LogLevel logLevel) => true;

        public IDisposable BeginScope<TState>(TState state) => null;   
    }

    public class LogRecord
    {
        public EventId EventId { get; set; }
        public RelationalEventId RelationalEventId { get; set; }
        public string Description { get; set; }
    }

And then I adjusted my code that returns an in-memory database so that I could switch in-memory DB provider as follows:

    public class InMemoryDatabase
    {
        public FakeLogger EfLogger { get; private set; }

        public MyDbContext GetContextWithData(bool useSqlite = false)
        {
            EfLogger = new FakeLogger();

            var factoryMock = Substitute.For<ILoggerFactory>();
            factoryMock.CreateLogger(Arg.Any<string>()).Returns(EfLogger);

            DbContextOptions<MyDbContext> options;

            if (useSqlite)
            {
                // In-memory database only exists while the connection is open
                var connection = new SqliteConnection("DataSource=:memory:");
                connection.Open();

                options = new DbContextOptionsBuilder<MyDbContext>()
                    .UseSqlite(connection)
                    .UseLoggerFactory(factoryMock)
                    .Options;
            }
            else
            {
                options = new DbContextOptionsBuilder<MyDbContext>()
                    .UseInMemoryDatabase(Guid.NewGuid().ToString())
                    // don't raise the error warning us that the in memory db doesn't support transactions
                    .ConfigureWarnings(x => x.Ignore(InMemoryEventId.TransactionIgnoredWarning))
                    .UseLoggerFactory(factoryMock)
                    .Options;
            }

            var ctx = new MyDbContext(options);

            if (useSqlite)
            {
                ctx.Database.EnsureCreated();                
            }

            // code to populate the context with test data

            ctx.SaveChanges();

            return ctx;
        }
    }

Finally, in my unit test I made sure to clear the event log just before the assert part of my test to ensure I don't get a false positive due to events that were logged during the arrange part of my test:

        public async Task Commits_transaction()
        {
            using (var context = _inMemoryDatabase.GetContextWithData(useSqlite: true))
            {

                // Arrange
                // code to set up date for test

                // make sure none of our setup added the event we are testing for
                _inMemoryDatabase.EfLogger.Events.Clear();

                // Act
                // Call the method that has the transaction;

                // Assert
                var result = _inMemoryDatabase.EfLogger.Events
                    .Any(x => x.EventId.Id == (int) RelationalEventId.CommittingTransaction);

Answer 2

You could check EF Core logs for a RelationalEventId.RollingbackTransaction event type. I provided full details here:

How to trace an Entity Framework Core event for integration testing?

How it could look:

Assert.True(eventList.Contains((int)RelationalEventId.CommittingTransaction));

Answer 3

I think you are asking about how to rollback when a commit fails, EF core will auto rollback on if any of the statement failed Read more here , if you are asking for other reason or you want to do something when rollback happens, just to add try catch blocks,

    using (var transaction = await 
        _context.Database.BeginTransactionAsync()){
   try { 
                    _context.Add(item);

        // Save the item so it has an ItemId
        result = await _context.SaveChangesAsync();

        // perform some actions using that new item's ItemId
        _otherRepository.Execute(item.ItemId);

        transaction.Commit();
                } 
                catch (Exception) 
                { 
                // failed, Do something 
                } }