xUnit Theory. How clear database between tests

I write test using xUnit [Theory]. My problem is that I run first test and it pass, while 2nd test throw exception ---- Microsoft.Data.Sqlite.SqliteException : SQLite Error 19: 'UNIQUE constraint failed: CONTRAHENT.ID'. When I run each test separately, in Test Explorer they all passed. So I assume that database is not cleared/disposed before second run.

 public class TestDatabaseFixture
    {
        private const string ConnectionString = "DataSource=file::memory:?cache=shared";
        private static readonly object _lock = new();
        private static bool _databaseInitialized;

        public TestDatabaseFixture()
        {

            lock (_lock)
            {
                if (!_databaseInitialized)
                {
                    using (var context = CreateContext())
                    {
                        context.Database.EnsureDeleted();
                        context.Database.EnsureCreated();
                    }

                    _databaseInitialized = true;
                }
            }
        }

        public DbTrancheTest CreateContext(bool sensitiveDataLoggingEnabled = false)
            => new DbTrancheTest(
                new DbContextOptionsBuilder<DbTranche>()
                .EnableSensitiveDataLogging(sensitiveDataLoggingEnabled)
                .UseSqlite(ConnectionString)
                .Options);


    }

Test class

public class TrancheServiceTest : IClassFixture<TestDatabaseFixture>
    {
        private TestDatabaseFixture Fixture { get; }
        // private readonly ITestOutputHelper output;
        public TrancheServiceTest(TestDatabaseFixture fixture)
        {
            Fixture = fixture;
        }

            [Theory]
            [InlineData(TrancheStatus.created, true)] // passed
            [InlineData(TrancheStatus.accepted, false)] // failed
            [InlineData(TrancheStatus.chanaged, true)] // failed
            [InlineData(TrancheStatus.waitforconf, false)] // failed
            [InlineData(TrancheStatus.rejected, false)] //failed
           
            public void Should_Set_Can_Edit_Flag_For_User(TrancheStatus status, bool result)
            {
                // Arrange
                using var context = Fixture.CreateContext();
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();
    
              
                var orderRepository = new TrancheOrderRepository(context);
                var userRepo = new UserRepository(context);
                var contrahentRepository = new ContrahentRepository(context);
    
                contrahentRepository.Add(new CONTRAHENT { ID = 1, NAME = "Contrahent1" });
                contrahentRepository.Save();

                var trancheOrder = new TRANCHE_ORDER
                {
                    ID = 1,
                    ISSUE_DATE = DateTime.Parse("2023-02-01"),
                    LAST_UPDATE = DateTime.Parse("2023-02-01"),
                    STATUS = (short)status,
                    VOLUME = 0,
                    USER = "",
                };
                orderRepository.Add(trancheOrder);
                orderRepository.Save();
    
                userRepo.Add(new USER { ID = 1, CAN_MODIFY = 1, DESCRIPTION = "Test user", FK_CONTRAHENT_ID = 1, NAME = "User1", USER_ID = 123 });
                userRepo.Save();
    
                var service = new TrancheService(contrahentRepository, orderRepository, userRepo);
                DateTime dateFrom = DateTime.Parse("2023-01-01");
                DateTime dateTo = DateTime.Parse("2023-12-01");
    
                // Act
                var trancheList = service.GetTrancheList(dateFrom, dateTo, 123, true);
    
                // Assert
                Assert.Collection(trancheList,
                    e => Assert.Equal(result, e.CanEdit));
    
            }
}

Just like @fildor said, you can create a new DB each time. For that, you can use a connection string which looks like this:

ConnectionString = "file:{0}?mode=memory";  

And update your code like this:

public TestDatabaseFixture()
    {

        lock (_lock)
        {
                using (var context = CreateContext())
                {
                    context.Database.EnsureDeleted();
                    context.Database.EnsureCreated();
                }
            }
        }
    }

public DbTrancheTest CreateContext(bool sensitiveDataLoggingEnabled = false)
        => new DbTrancheTest(
            new DbContextOptionsBuilder<DbTranche>()
            .EnableSensitiveDataLogging(sensitiveDataLoggingEnabled)
            .UseSqlite(string.Format(ConnectionString, DateTime.Now.Ticks))
            .Options);

I end up creating context in each individual test like this. All tests passed.

  using var context = new DbTrancheTest(
        new DbContextOptionsBuilder<DbTranche>()
        .EnableSensitiveDataLogging(false)
        .UseSqlite(string.Format("DataSource=file::memory:?{0}", Guid.NewGuid().ToString()))
        .Options);

    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();