c# - 针对 EF Core 和 SQL Server 数据库编写集成测试

Question

本文末尾的工作示例(我保留了我尝试过的所有东西[长篇文章的原因]，以便其他人以后可以从中受益)

我正在尝试为我的 EF Core 3.1 类库编写集成测试。作为单元测试框架，我使用了 XUnit 并遵循了 Microsoft 的指南:https://learn.microsoft.com/en-us/ef/core/testing/sharing-databases

这是设置的样子(它有点长，因为我实际上是在我的 SQL Server 中创建一个数据库，以防我需要从测试输出中查看真实结果):

 public class SharedDatabaseFixture : IDisposable
 {
        private static readonly object _lock = new object();
        private static bool _databaseInitialized;
        private static string _DatabaseName = "Database.Server.Local";
        private static IConfigurationRoot config;

        public SharedDatabaseFixture()
        {
            config = new ConfigurationBuilder()
               .AddJsonFile($"appsettings.Development.json", true, true)
               .Build();

            var test = config.GetValue<string>("DataSource");

            var connectionStringBuilder = new SqlConnectionStringBuilder
            {
                DataSource = config.GetValue<string>("DataSource"),
                InitialCatalog = _DatabaseName,
                IntegratedSecurity = true,
            };

            var connectionString = connectionStringBuilder.ToString();
            Connection = new SqlConnection(connectionString);

            CreateEmptyDatabaseAndSeedData();
            Connection.Open();
        }

        public bool ShouldSeedActualData { get; set; } = true;
        public DbConnection Connection { get; set; }

        public ApplicationDbContext CreateContext(DbTransaction transaction = null)
        {
            var identity = new GenericIdentity("admin@sample.com", "Admin");
            var contextUser = new ClaimsPrincipal(identity); //add claims as needed
            var httpContext = new DefaultHttpContext() { User = contextUser };
            var defaultHttpContextAccessor = new HttpContextAccessor();
            defaultHttpContextAccessor.HttpContext = httpContext;

            var context = new ApplicationDbContext(new DbContextOptionsBuilder<ApplicationDbContext>().UseSqlServer(Connection).Options, null, defaultHttpContextAccessor);

            if (transaction != null)
            {
                context.Database.UseTransaction(transaction);
            }
           
            return context;
        }

        private static void ExecuteSqlCommand(SqlConnectionStringBuilder connectionStringBuilder, string commandText)
        {
            using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
            {
                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    command.ExecuteNonQuery();
                }
            }
        }

        private static SqlConnectionStringBuilder Master => new SqlConnectionStringBuilder
        {
            DataSource = config.GetValue<string>("DataSource"),
            InitialCatalog = "master",
            IntegratedSecurity = true
        };

        private static string Filename => Path.Combine(Path.GetDirectoryName(typeof(SharedDatabaseFixture).GetTypeInfo().Assembly.Location), $"{_DatabaseName}.mdf");
        private static string LogFilename => Path.Combine(Path.GetDirectoryName(typeof(SharedDatabaseFixture).GetTypeInfo().Assembly.Location), $"{_DatabaseName}_log.ldf");

        private static void CreateDatabaseRawSQL()
        {
            ExecuteSqlCommand(Master, $@"IF(db_id(N'{_DatabaseName}') IS NULL) BEGIN CREATE DATABASE [{_DatabaseName}] ON (NAME = '{_DatabaseName}', FILENAME = '{Filename}') END");
        }

        private static List<T> ExecuteSqlQuery<T>(SqlConnectionStringBuilder connectionStringBuilder, string queryText, Func<SqlDataReader, T> read)
        {
            var result = new List<T>();

            using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
            {
                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = queryText;

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            result.Add(read(reader));
                        }
                    }
                }
            }

            return result;
        }

        private static void DestroyDatabaseRawSQL()
        {
            var fileNames = ExecuteSqlQuery(Master, $@"SELECT [physical_name] FROM [sys].[master_files] WHERE [database_id] = DB_ID('{_DatabaseName}')", row => (string)row["physical_name"]);

            if (fileNames.Any())
            {
                ExecuteSqlCommand(Master, $@"ALTER DATABASE [{_DatabaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;EXEC sp_detach_db '{_DatabaseName}', 'true'");
                fileNames.ForEach(File.Delete);
            }

            if (File.Exists(Filename))
                File.Delete(Filename);

            if (File.Exists(LogFilename))
                File.Delete(LogFilename);
        }

        private void CreateEmptyDatabaseAndSeedData()
        {
            lock (_lock)
            {
                if (!_databaseInitialized)
                {
                    using (var context = CreateContext())
                    {
                        try
                        {
                            DestroyDatabaseRawSQL();
                        }
                        catch (Exception) { }

                        try
                        {
                            CreateDatabaseRawSQL();
                            context.Database.EnsureCreated();
                        }
                        catch (Exception) { }

                        if (ShouldSeedActualData)
                        {
                            List<UserDB> entities = new List<UserDB>()
                            {
                                new UserDB() { Id = "Admin@sample.com", Name= "Admin" }
                            };

                            context.Users.AddRange(entities);
                            context.SaveChanges();

                            List<IdentityRole> roles = new List<IdentityRole>()
                            {
                                new IdentityRole(){Id = "ADMIN",Name = nameof(DefaultRoles.Admin), NormalizedName = nameof(DefaultRoles.Admin)},
                                new IdentityRole(){Id = "FINANCE",Name = nameof(DefaultRoles.Finance), NormalizedName = nameof(DefaultRoles.Finance)}
                            };

                            context.Roles.AddRange(roles);
                            context.SaveChanges();

                        }
                    }

                    _databaseInitialized = true;
                }
            }
        }

        public void Dispose()
        {
            Connection.Dispose();
        }
}

然后，测试类如下所示(为简单起见仅显示 2 个测试):

public class BaseRepositoryTests : IClassFixture<SharedDatabaseFixture>
{
        private readonly SharedDatabaseFixture fixture;
        private IMapper _mapper;

        public BaseRepositoryTests(SharedDatabaseFixture fixture)
        {
            this.fixture = fixture;

            var config = new MapperConfiguration(opts =>
            {
                opts.AddProfile<CountriesDBMapper>();
                opts.AddProfile<EmployeeDBMapper>();
                opts.AddProfile<EmployeeAccountDBMapper>();
            });

            _mapper = config.CreateMapper();
        }

        [Fact]
        public async Task EntityCannotBeSavedIfDbEntityIsNotValid()
        {
            using (var transaction = fixture.Connection.BeginTransaction())
            {
                using (var context = fixture.CreateContext(transaction))
                {
                    var baseCountryRepository = new BaseRepository<CountryDB, Country>(context, _mapper);
                    var invalidCountry = new Country() { };

                    //Act
                    var exception = await Assert.ThrowsAsync<DbUpdateException>(async () => await baseCountryRepository.CreateAsync(invalidCountry));
                    Assert.NotNull(exception.InnerException);
                    Assert.Contains("Cannot insert the value NULL into column", exception.InnerException.Message);
                }
            }
        }

        [Fact]
        public async Task EntityCanBeSavedIfEntityIsValid()
        {
            using (var transaction = fixture.Connection.BeginTransaction())
            {
                using (var context = fixture.CreateContext(transaction))
                {
                    var baseCountryRepository = new BaseRepository<CountryDB, Country>(context, _mapper);
                    var item = new Country() { Code = "SK", Name = "Slovakia" };

                    //Act
                    var result = await baseCountryRepository.CreateAsync(item);
                    Assert.NotNull(result);
                    Assert.Equal(1, result.Id);
                }
            }
        }
}

最后是存储库实现 (CRUD) 示例:

  public async Task<TModel> CreateAsync(TModel data)
    {
        var newItem = mapper.Map<Tdb>(data);

        var entity = await context.Set<Tdb>().AddAsync(newItem);
        await context.SaveChangesAsync();

        return mapper.Map<TModel>(entity.Entity);
    }

    public async Task<bool> DeleteAsync(long id)
    {
        var item = await context.Set<Tdb>().FindAsync(id).ConfigureAwait(false);
        if (item == null)
            throw new ArgumentNullException();

        var result = context.Set<Tdb>().Remove(item);
        await context.SaveChangesAsync(); 

        return (result.State == EntityState.Deleted || result.State == EntityState.Detached);
    }

如果我单独运行这些测试，它们中的每一个都可以毫无问题地通过。但是，如果我从 BaseRepositoryTests 运行所有测试然后我遇到随机问题，因为不知何故，数据库事务不会回滚，但数据会保存并在测试之间共享。

我已经检查过，确实，每笔交易都有自己唯一的 ID，因此它们不应该发生冲突。我在这里错过了什么吗？我的意思是，根据微软的说法，这是正确的方法，但显然我错过了一些东西。与我能找到的其他指南唯一不同的是，我正在使用 SaveChangesAsync在我的存储库实现中，而其他人使用 SaveChanges ...但是我相信这不应该是我的问题的根本原因。

如能提供有关此事的任何帮助，我们将不胜感激。

更新 1:

正如评论所建议的那样，我尝试了两种不同的方法。第一个是使用 CommitableTransaction像下面这样:

方法更新:

[Fact]
public async Task EntityCanBeSavedIfEntityIsValid()
{
    using (var transaction = new CommittableTransaction(new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
    {
        using (var context = fixture.CreateContext(transaction))
        {
            var baseCountryRepository = new BaseRepository<CountryDB, Country>(context, _mapper);
            var item = new Country() { Code = "SK", Name = "Slovakia" };

            //Act
            var result = await baseCountryRepository.CreateAsync(item);
            Assert.NotNull(result);
            Assert.Equal(1, result.Id);
        }
    }
}

共享夹具更新:

public ApplicationDbContext CreateContext(CommittableTransaction transaction = null)
{
    ... other code

    if (transaction != null)
    {
        context.Database.EnlistTransaction(transaction);
    }
   
    return context;
}

不幸的是，这在批量运行我的代码测试时以相同的结果结束(我保存的数据最终被递增并且在每次测试后没有被丢弃)

我尝试的第二件事是使用 TransactionScope像下面这样:

[Fact]
public async Task EntityCanBeModifiedIfEntityExistsAndIsValid()
{
    using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }, TransactionScopeAsyncFlowOption.Enabled))
    {
        using (var context = fixture.CreateContext())
        {
            var baseCountryRepository = new BaseRepository<CountryDB, Country>(context, _mapper);
            var item = new Country() { Code = "SK", Name = "Slovakia" };

            //Act
            var insertResult = await baseCountryRepository.CreateAsync(item);
            Assert.NotNull(insertResult);
            Assert.Equal(1, insertResult.Id);
            Assert.Equal("SK", insertResult.Code);
            Assert.Equal("Slovakia", insertResult.Name);

            //Act
            insertResult.Code = "SVK";

            var result = await baseCountryRepository.UpdateAsync(insertResult.Id, insertResult);
            Assert.Equal(1, result.Id);
            Assert.Equal("SVK", result.Code);
            Assert.Equal("Slovakia", result.Name);
        }

        scope.Complete();
    }
}

和之前一样，没有产生任何新的结果。

我尝试的最后一件事是删除 :IClassFixture<SharedDatabaseFixture>来自测试类，而是在构造函数中创建我的数据库夹具的新实例(每次测试运行都会触发)，如下所示:

public BaseRepositoryTests()
{
    this.fixture = new SharedDatabaseFixture();
    var config = new MapperConfiguration(opts =>
    {
        opts.AddProfile<CountriesDBMapper>();
        opts.AddProfile<EmployeeDBMapper>();
        opts.AddProfile<EmployeeAccountDBMapper>();
    });

    _mapper = config.CreateMapper();
}

和以前一样，这次更新没有新的结果。

工作设置

共享数据库装置(基本上是负责创建数据库的类...现在与以前版本的主要区别是，在构造函数中它接受创建数据库时使用的唯一 guid -> 以创建具有唯一名称的数据库。此外，我还添加了一个新方法 ForceDestroyDatabase()，它负责在测试完成后销毁数据库。我没有将它放在 Dispose() 方法中，因为有时您想检查数据库实际发生了什么，在那种情况下你只是不调用该方法......稍后见)

public class SharedDatabaseFixture : IDisposable
    {
        private static readonly object _lock = new object();
        private static bool _databaseInitialized;
        private string _DatabaseName = "FercamPortal.Server.Local.";
        private static IConfigurationRoot config;
        public SharedDatabaseFixture(string guid)
        {
            config = new ConfigurationBuilder()
               .AddJsonFile($"appsettings.Development.json", true, true)
               .Build();

            var test = config.GetValue<string>("DataSource");

            this._DatabaseName += guid;

            var connectionStringBuilder = new SqlConnectionStringBuilder
            {
                DataSource = config.GetValue<string>("DataSource"),
                InitialCatalog = _DatabaseName,
                IntegratedSecurity = true,
            };
            var connectionString = connectionStringBuilder.ToString();
            Connection = new SqlConnection(connectionString);

            CreateEmptyDatabaseAndSeedData();
            Connection.Open();
        }
         ...other code the same as above, skipped for clarity

private void CreateEmptyDatabaseAndSeedData()
            {
                lock (_lock)
                {
                    using (var context = CreateContext())
                    {
                        try
                        {
                            DestroyDatabaseRawSQL();
                        }
                        catch (Exception ex) { }
    
                        try
                        {
                            CreateDatabaseRawSQL();
                            context.Database.EnsureCreated();
                        }
    
                        catch (Exception) { }
    
                        if (ShouldSeedActualData)
                        {
                            List<UserDB> entities = new List<UserDB>()
                                {
                                    new UserDB() { Id = "Robert_Jokl@swissre.com", Name= "Robert Moq" },
                                    new UserDB() { Id = "Test_User@swissre.com", Name= "Test User" }
                                };
    
                            context.Users.AddRange(entities);
                            context.SaveChanges();
    
                            List<IdentityRole> roles = new List<IdentityRole>()
                                {
                                    new IdentityRole(){Id = "ADMIN",Name = nameof(FercamDefaultRoles.Admin), NormalizedName = nameof(FercamDefaultRoles.Admin)},
                                    new IdentityRole(){Id = "FINANCE",Name = nameof(FercamDefaultRoles.Finance), NormalizedName = nameof(FercamDefaultRoles.Finance)}
                                };
    
                            context.Roles.AddRange(roles);
                            context.SaveChanges();
    
                        }
                    }
    
                }
            }

        
        public void ForceDestroyDatabase()
        {
            DestroyDatabaseRawSQL();
        }

        public void Dispose()
        {
            Connection.Close();
            Connection.Dispose();
        }
    }

示例测试类:

public class DailyTransitDBRepositoryTests : IDisposable
    {
        private readonly SharedDatabaseFixture fixture;
        private readonly ApplicationDbContext context;

        private IMapper _mapper;

        public DailyTransitDBRepositoryTests()
        {
            this.fixture = new SharedDatabaseFixture(Guid.NewGuid().ToString("N"));
            this.context = this.fixture.CreateContext();
            this.context.Database.OpenConnection();

            var config = new MapperConfiguration(opts =>
            {
                opts.AddProfile<DailyTransitDBMapper>();
                opts.AddProfile<EmployeeDBMapper>();
                opts.AddProfile<EmployeeAccountDBMapper>();
                opts.AddProfile<CountriesDBMapper>();
            });

            _mapper = config.CreateMapper();
        }


        ...other code ommited for clarity

        public void Dispose()
        {
            this.context.Database.CloseConnection();
            this.context.Dispose();

            this.fixture.ForceDestroyDatabase();
            this.fixture.Dispose();
        }

        [Fact]
        public async Task GetTransitsForYearAndMonthOnlyReturnsValidItems()
        {
            var employees = await PopulateEmployeesAndReturnThemAsList(context);
            var countries = await PopulateCountriesAndReturnThemAsList(context);

            var transitRepository = new DailyTransitDBRepository(context, _mapper);

            var transitItems = new List<DailyTransit>() {
                    new DailyTransit()
                    {
                        Country = countries.First(),
                        Employee = employees.First(),
                        Date = DateTime.Now,
                        TransitionDurationType = DailyTransitDurationEnum.FullDay
                    },
                    new DailyTransit()
                    {
                        Country = countries.First(),
                        Employee = employees.Last(),
                        Date = DateTime.Now.AddDays(1),
                        TransitionDurationType = DailyTransitDurationEnum.FullDay
                    },
                    new DailyTransit()
                    {
                        Country = countries.First(),
                        Employee = employees.Last(),
                        Date = DateTime.Now.AddMonths(1),
                        TransitionDurationType = DailyTransitDurationEnum.FullDay
                    }
                    };

            //Act
            await transitRepository.CreateRangeAsync(transitItems);

            //retrieve all items
            using (var context2 = fixture.CreateContext())
            {
                var transitRepository2 = new DailyTransitDBRepository(context2, _mapper);
                var items = await transitRepository2.GetEmployeeTransitsForYearAndMonth(DateTime.Now.Year, DateTime.Now.Month);

                Assert.Equal(2, items.Count());
                Assert.Equal("Janko", items.First().Employee.Name);
                Assert.Equal("John", items.Last().Employee.Name);
            }
        }
       
    }

Answer 1

罗伯特，很高兴它有所帮助!根据您的要求，I re-submit the answer for anyone that could find this answer helpful as you.

我了解到尝试通过 IClassFixture 或 CollectionFixtures 共享 Entity Framework 数据库上下文最终会导致测试被另一个测试数据污染或死锁/由于 xUnit 的并行执行导致的竞争条件， Entity Framework 抛出异常，因为它已经跟踪了具有给定 Id 的对象，以及更多类似的问题。就个人而言，我建议您针对您的特定使用原因，将数据库上下文创建/清理放在 constructor/dispose 替代方案中，例如:

    public class TestClass : IDisposable
    {
        DatabaseContext DatabaseContext;

        public TestClass()
        {
            var options = new DbContextOptionsBuilder<DatabaseContext>()
              .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
              .Options;

            DatabaseContext = new DatabaseContext(options);

            //insert the data that you want to be seeded for each test method:
            DatabaseContext.Set<Product>().Add(new Product() { Id = 1, Name = Guid.NewGuid().ToString() });
            DatabaseContext.SaveChanges();
        }

        [Fact]
        public void FirstTest()
        {
            var product = DatabaseContext.Set<Product>().FirstOrDefault(x => x.Id == 1).Name;
            //product evaluates to => 0f25a10b-1dfd-4b4b-a69d-4ec587fb465b
        }

        [Fact]
        public void SecondTest()
        {
            var product = DatabaseContext.Set<Product>().FirstOrDefault(x => x.Id == 1).Name;
            //product evaluates to => eb43d382-40a5-45d2-8da9-236d49b68c7a
            //It's different from firstTest because is another object
        }

        public void Dispose()
        {
            DatabaseContext.Dispose();
        }
    }

当然你总是可以做一些改进，但想法就在那里