Integration Testing with ASP.NET Core and Entity Framework Core - How to Restore Test Data in Database Per Test?

0

I am a new .NET Core user. I am trying to perform integration testing on a ASP.NET.Core WebAPI that uses EF Core for managing data. Architecture is based on eShopContainers DDD sample. I am using Xunit for testing and have considered three approaches for resetting the database running in a docker-compose stack, to an initial state prior to running each test:

  1. Test Setup: Create and seed database with test data, TearDown: Remove database.
  2. Test Setup: Seed database table with test data, TearDown: Clear test data.
  3. Test Setup: Create a transaction, TearDown: Abort transaction.

The System Under Test executes create/delete/update operations within a transaction using BeginTransaction(). Not sure if EF Core can use nested transactions so have tried performing options 1 and 2 when testing.

The code below uses WebApiTestFactory as a class derived from WebApplicationFactory. This is provided as a class fixture to create a TestServer instance for testing.

    public class CoursesScenarios : CoursesScenarioBase, IClassFixture<WebApiTestFactory>, IDisposable
    {
        /// <summary> 
        /// With Xunit constructor gets run before every test
        /// Code in the Dispose method gets run after every test completed
        /// </summary>
        public CoursesScenarios(WebApiTestFactory factory) : base(factory)
        {
            Console.WriteLine("CoursesScenario test setup...");

            Server.Host
                .MigrateDbContext<CourseContext>((context, services) =>
                {
                    Console.WriteLine("Seeding lookup data");
                    var env = services.GetService<IWebHostEnvironment>();
                    var settings = services.GetService<IOptions<CourseSettings>>();
                    var logger = services.GetService<ILogger<CoursesContextSeed>>();

                    new CoursesContextSeed()
                        .SeedAsync(context, env, settings, logger)
                        .Wait();
                });

            // load the test data
            Server.Host
                .MigrateDbContext<CourseContext>((context, services) =>
                {
                    Console.WriteLine("Seeding the test data");
                    var env = services.GetService<IWebHostEnvironment>();
                    var settings = services.GetService<IOptions<CourseSettings>>();
                    var logger = services.GetService<ILogger<CoursesContextTestSeed>>();

                    new CoursesContextTestSeed()
                        .SeedAsync(context, env, settings, logger)
                        .Wait();
                });

            foreach(Course c in Context.Courses)
            {
                Console.WriteLine($"Course {c.Id}");
            }

        }

        // tests here
        [Fact]
        ...
        //

        ///
        /// <summary>
        /// This gets called after every test by XUnit
        /// </summary>
        ///
        public void Dispose()
        {
            // using (var command = Context.Database.GetDbConnection().CreateCommand())
            // {
            //     command.CommandText = @"DELETE FROM CourseManagement.Course;
            //         ALTER SEQUENCE coursemanagement.""unit_UnitID_seq"" RESTART WITH 1;";
            //     Context.Database.OpenConnection();
            //     int result = command.ExecuteNonQuery();
            //     Console.WriteLine($"{result} records deleted from course table");
            //     how to reload entities to match deleted state of rows in table?
            //     currently entities in memory are saved back to database
            // }
            Context.Database.EnsureDeleted();
        }

The following exception is raised on the server and received during testing. This is as a result of deleting the database, so the connection is lost.

Npgsql.NpgsqlException (0x80004005): Exception while connecting
 ---> System.Net.Sockets.SocketException (111): Connection refused
   at Npgsql.NpgsqlConnector.Connect(NpgsqlTimeout timeout)
   at Npgsql.NpgsqlConnector.Connect(NpgsqlTimeout timeout)
   at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.<>c__DisplayClass32_0.<<Open>g__OpenLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnection.Open()
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlDatabaseCreator.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at Microsoft.AspNetCore.Hosting.IWebHostExtensions.InvokeSeeder[TContext](Action`2 seeder, TContext context, IServiceProvider services) in /src/BuildingBlocks/WebHost.Customization/IWebHostExtensions.cs:line 76
   at Microsoft.AspNetCore.Hosting.IWebHostExtensions.<>c__DisplayClass1_0`1.<MigrateDbContext>b__2() in /src/BuildingBlocks/WebHost.Customization/IWebHostExtensions.cs:line 54
   at Polly.Policy.<>c__DisplayClass108_0.<Execute>b__0(Context ctx, CancellationToken ct)
   at Polly.Policy.<>c__DisplayClass138_0.<Implementation>b__0(Context ctx, CancellationToken token)
   at Polly.Retry.RetryEngine.Implementation[TResult](Func`3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates`1 shouldRetryResultPredicates, Action`4 onRetry, Int32 permittedRetryCount, IEnumerable`1 sleepDurationsEnumerable, Func`4 sleepDurationProvider)

Has anyone managed to successfully reset database state with EF Core for integration testing, either using delete database approach or seeding/unseeding table state? Have also tried the seed data/unseed data approach, resetting identity key and deleting records using raw SQL query, but then encountered difficulty resynchronising EF Core with the state of the deleted rows in the underlying table, EF Core sees them as new entities and adds them back. Maybe best way is to resort to using test data sql scripts and ADO.NET to initialise test data state?

c#
asp.net-core
entity-framework-core
asked on Stack Overflow Mar 3, 2020 by anon_dcs3spp

1 Answer

0

Solved, by using test setup/teardown per test as opposed to class fixture and restarting the identity on postgresql's new default identity column within the table, as suggested here.

using (context)
 {
     // could execute a DELETE statement via command on the course and units
    // to improve performance, otherwise a DELETE command is issued for each entity?
     context.Courses.RemoveRange(context.Courses);
     context.Units.RemoveRange(context.Units);
     await context.SaveChangesAsync();

     using (var command = context.Database.GetDbConnection().CreateCommand())
     {
         command.CommandText = @"ALTER TABLE CourseManagement.Unit ALTER COLUMN ""UnitID"" RESTART WITH 1";
         context.Database.OpenConnection();
         await command.ExecuteNonQueryAsync();
     }

     _logger.LogInformation($"------ {nameof(TestSeed)} deseeded test data");
}
answered on Stack Overflow Mar 12, 2020 by anon_dcs3spp

User contributions licensed under CC BY-SA 3.0