C#•4mo ago

EF Core Transaction in both SaveChanges and SqlQueryRaw for Unit Test

I have some complicated SQL script building that I need to unit test. The application uses data in EF that describes how the SQL should be built, and from that it builds a script to execute. I'm using xUnit database fixtures to build a database and seed it with data in a transaction. However, it appears that the transaction that's opened and being used by EF in SaveChanges it not being used by subsequent SqlQueryRaw call. The following unit test should return results, but it's not. It's possible there is a bug in the code I'm testing, but I've reviewed it several times and can't find it. My question is: can the transaction created from context.Database.BeginTransaction() be shared and used by context.Database.SqlQueryRaw?

public class ActionLoadTableFromHistoryTests : IClassFixture<DbFixture> {
    public ActionLoadTableFromHistoryTests(DbFixture fixture)
        => Fixture = fixture;

    private DbFixture Fixture { get; }

    [Fact]
    public async Task DoesLoadFromHistory_Load1() {

        // Arrange
        using var context = Fixture.CreateContext();
        context.Database.BeginTransaction();
        await SeedData.Perform(context);
        await context.SaveChangesAsync();
        context.ChangeTracker.Clear();

        // Action
        var action = new ActionLoadTableFromHistory(Actions.Get());
        var historySql = await action.Perform(0, 1, false); // Uses data in EF to produce a SQL Script
        var finalSql = $"{historySql.SelectSql} select * from {historySql.CurrentTableName} {historySql.CloseSql}";
        var result = await context.Database.SqlQueryRaw<SourceTable>(finalSql, new object[0]).ToListAsync();

        // Assert
        Assert.NotNull(result);
        Assert.NotEmpty(result);
    }
}

public class ActionLoadTableFromHistoryTests : IClassFixture<DbFixture> {
    public ActionLoadTableFromHistoryTests(DbFixture fixture)
        => Fixture = fixture;

    private DbFixture Fixture { get; }

    [Fact]
    public async Task DoesLoadFromHistory_Load1() {

        // Arrange
        using var context = Fixture.CreateContext();
        context.Database.BeginTransaction();
        await SeedData.Perform(context);
        await context.SaveChangesAsync();
        context.ChangeTracker.Clear();

        // Action
        var action = new ActionLoadTableFromHistory(Actions.Get());
        var historySql = await action.Perform(0, 1, false); // Uses data in EF to produce a SQL Script
        var finalSql = $"{historySql.SelectSql} select * from {historySql.CurrentTableName} {historySql.CloseSql}";
        var result = await context.Database.SqlQueryRaw<SourceTable>(finalSql, new object[0]).ToListAsync();

        // Assert
        Assert.NotNull(result);
        Assert.NotEmpty(result);
    }
}

1 Reply

Camster•4mo ago

Looks like I asked one of those weird questions again 😅 Actually I did some testing and it turns out the transaction is shared between the two. So the problem is on my end. Nevermind

Gaming

Programming

EF Core Transaction in both SaveChanges and SqlQueryRaw for Unit Test