Cached values returned after TransactionScope.Dispose()

Question

I'm a newbie in integration tests and I'm looking for some explanation and advice about workaround of my issue:

I'm using TransactionScope in my tests to keep database clean and creating new TransactionScope before each test and dispose it after each test:

 [SetUp]
    public void Init()
    {
        this.scope = new TransactionScope(
            TransactionScopeOption.Required,
            new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted },
            TransactionScopeAsyncFlowOption.Enabled);
        this.context = new SportsPerformanceDbContext();
        this.questRepo = new QuestionRepository(this.context);
    }

    [TearDown]
    public void CleanAll()
    {
        this.context.Dispose();
        this.scope.Dispose();
    }

Everything works ok when I run one test class. But when I run at least two test classes, I'm facing a problem: in this test (see below) lasrQuestionId equals the last question id from database - that's ok, but actualResultId equals the Id_of_the_last_added_question_in_tests_with_transaction_scope + 1:

[Test]
    public async void AddAsyncTest()
    {
        // Arrange
        var questionModel = new QuestionModel
        {
            //some properties
        };
        Question lastQuestion = this.GetLastQuestion();
        var lastQuestionId = lastQuestion?.Id ?? 0;

        // Act
        var addResult = await this.questRepo.AddAsync(questionModel);
        var actualResult = addResult.Value;

        // Assert
        Assert.AreEqual(lastQuestionId + 1, actualResult.Id);
        // some other assertions
    }

So I have the following, e.g., lastQuestionId is 5 (5 questions in Database), but actualResult Id is 16 (because I've previously added some questions in other tests)... I assume that there is a problem with my context or scope.dispose(). I don't know where is a problem, could you explain what am I doing wrong here? Thanks in advance!

this.GetLastQuestion() code is below:

 private Question GetLastQuestion()
    {
        using (var ctx = new SportsPerformanceDbContext())
        {
            return ctx.Question
             .OrderByDescending(q => q.Id)
             .FirstOrDefault();
        }
    }

Answer 1

This is how SQL Server engine works:

For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.