using System;
using Microsoft.EntityFrameworkCore;

public class DatabaseFixture : IDisposable
{
    private readonly DbContext _context;

    // Constructor is called only once before all tests
    public DatabaseFixture()
    {
        // Create random database name suffix as a safety measure
        var id = Guid.NewGuid().ToString().Replace("-", "");

        // Create a template database name and store it for later use
        TemplateDatabaseName = $"my_db_tmpl_{id}";

        // Create connection string and store it for later use
        Connection = $"Host=my_host;Database={TemplateDatabaseName};Username=my_user;Password=my_pw";

        // Configure DbContext
        var optionsBuilder = new DbContextOptionsBuilder();
        optionsBuilder.UseNpgsql(Connection);

        // Create instance of you application's DbContext
        _context = new MyApplicationDbContext(optionsBuilder.Options);

        // Create database schema
        _context.Database.EnsureCreated();

        // todo: Insert common data here

        // Close template database connection, we will not need it for now
        _context.Database.CloseConnection();
    }

    // We'll use this later
    public string TemplateDatabaseName { get; }

    // We'll use this later
    public string Connection { get; }

    // Dispose is called only once after all tests
    public void Dispose()
    {
        // Remove template database
        _context.Database.EnsureDeleted();
    }
}

using Xunit;

[CollectionDefinition("Database")]
public class DatabaseCollectionFixture : ICollectionFixture<DatabaseFixture>
{
}

using System;
using Microsoft.EntityFrameworkCore;
using Npgsql;

public abstract class DatabaseTestCase : IDisposable
{
    // Constructor is called before every test
    protected DatabaseTestCase(DatabaseFixture databaseFixture)
    {
        // Create random database suffix as a safety measure
        var id = Guid.NewGuid().ToString().Replace("-", "");

        // And create test database name
        var databaseName = $"my_db_test_{id}";

        // Open connection to database and create clone of template database
        using (var tmplConnection = new NpgsqlConnection(databaseFixture.Connection))
        {
            tmplConnection.Open();

            using (var cmd = new NpgsqlCommand($"CREATE DATABASE {databaseName} WITH TEMPLATE {databaseFixture.TemplateDatabaseName}", tmplConnection))
            {
                cmd.ExecuteNonQuery();
            }
        }

        // Create connection string
        var connection = $"Host=my_host;Database={databaseName};Username=my_user;Password=my_pw";

        // Configure DbContext
        var optionsBuilder = new DbContextOptionsBuilder();
        optionsBuilder.UseNpgsql(connection);

        // Create an instance of your application's DbContext
        DbContext = new MyApplicationDbContext(optionsBuilder.Options);
    }

    // Store application's DbContext for use in tests
    public MyApplicationDbContext DbContext { get; }

    // Dispose is called after every test
    public void Dispose()
    {
        DbContext.Database.EnsureDeleted();
    }
}

using System.Threading;
using System.Threading.Tasks;
using Xunit;

[Collection("Database")]
public class FindUserByIdHandlerTest : DatabaseTestCase
{
    private readonly FindUserByIdHandler _handler;

    public FindUserByIdHandlerTest(DatabaseFixture databaseFixture)
        : base(databaseFixture)
    {
        _handler = new FindUserByIdHandler(DbContext);
    }

    [Fact]
    public async Task Handle()
    {
        var user = new User("john@doe.com");

        await DbContext.AddAsync(user);
        await DbContext.SaveChangesAsync();

        var command = new FindUserByIdCommand(user.Id);
        var found = await _handler.Handle(command, CancellationToken.None);

        Assert.NotNull(found);
        Assert.Equal(user.Id, found.Id);
    }
}