AssemblyTesting

Testing DB read/write (Assembly testing)

There are some tests, which require database connection. First type of such tests would be making sure your C# data contracts (POCO DTO to database) are aligned with database object structure (like, Table DTO == Table structure). Another type of tests might be desire to actually ensure whether data save by ICommand and read by IQuery are actually working with real database engine.

• Setting up assembly test
  • Collection definition
  • Base class
    • DB preparation
  • Fake object factory
• DTO == Table test
• Data write/read test

Then there are Query and Command SQL Syntax assembly tests which also require same setup as describe below.

Setting up assembly test project

• Create new XUnit test project in visual studio.
• Add reference to Salix.Dapper.Cqrs.MsSql.Testing.XUnit package.

Dapper.Cqrs.Testing package contains XUnit test Fixture, which is capable of creating database connection and reusing it for all these tests (Collection Fixture). Due to some limitations in XUnit engine, there are still some things you should take care of in your test project:

Creating Collection Definition

Collection definition is very simple empty class with [CollectionDefinition] attribute, which then should be put on all tests, which are to be part of database tests collection. Here is sample of such collection definition class:

using Salix.Dapper.Cqrs.MsSql.Testing.XUnit;

// <summary>
/// Collection attribute for Database tests, which requires the same (single) setup and dispose/cleanup.
/// This attribute should be put on all tests classes which require actual database to work with.
/// (Or on base class for all such tests).
/// </summary>
[CollectionDefinition(nameof(SqlTestsCollectionAttr))]
public class SqlTestsCollectionAttr : ICollectionFixture<SqlDatabaseFixture>
{
    // This class has no code, and is never created. Its purpose is simply
    // to be the placed on tests/test class to apply [CollectionDefinition]
}  

Here SqlDatabaseFixture is implemented fixture in Dapper.Cqrs.Testing package.You can choose other name of this collection class - it doesn't matter. Most important is the name of collection in [CollectionDefinition(name)] attribute. You must use exactly the same name on [Collection] attribute on test class. Thus - best to use nameof directive on collection class as shown in example.

There is template to create this class together with base class (below) - see Productivity

Creating base class for test classes

And in turn - it should be based on another base class MsSqlTestBase in Dapper.Cqrs.Testing package. Your base class can be decorated with [Collection] attribute to avoid doing it for all actual testing classes. Also this base class should implement GetSqlConnectionString() method with something returning actual SQL Database connection string for tests to use. Here is example:

using Salix.Dapper.Cqrs.MsSql.Testing.XUnit;
using Xunit;

[Collection(nameof(SqlTestsCollectionAttr))]
public abstract class DatabaseTestsBase : MsSqlTestBase
{
    protected override string GetSqlConnectionString()
    {
        // Should get it from some configuration!
        return "Data Source=localhost;Initial Catalog=Chinook;Integrated Security=True;";
    }
}

Database preparation

In case your database need to be "prepared" for test run (adding some data or changing structure), there is overridable method to be implemented in your base class. This method is called only once when entire database assembly tests are run (right before first test). You can see example in Sample solution, here is how it is done:

[Collection(nameof(SqlTestsCollectionAttr))]
public abstract class DatabaseTestsBase : MsSqlTestBase
{
    protected override string GetSqlConnectionString()
    {
        // Described above
    }

    protected override void PrepareDatabase()
    {
        this.TestFixture.WriteOutput("Preparing database. Creating test objects.");

        // Changing structure - adding some objects in database, if needed
        if (!this.TestFixture.Db.Query(new TableOrViewExistsQuery("ArtistAlbumView")))
        {
            this.TestFixture.Db.Execute(new ArtistAlbumViewCreateCommand());
        }

        // This can automatically add CheckSql function for syntax checking to database
        if (!this.TestFixture.Db.Query(new FunctionExistsQuery("CheckSql")))
        {
            this.TestFixture.Db.Execute(new CheckSqlFuctionCreateCommand());
        }

        // Can execute command to prepare data in database
        this.TestFixture.Db.Execute(new AddTestDataCommand());

        // As transaction is per-test (Rollback usually), we have to Commit these changes, 
        // so they stay permanantly in database for all tests
        this.TestFixture.Db.CommitTransaction();
    }
}

There is template to create this class together collection attribute class (above) - see Productivity

Fake object factory

As Commands are all about object saving into database, integration tests require more-or-less actual objects to perform such task. There is an interface in Dapper.Cqrs.Testing package, called ITestObjectFactory to be implemented in your testing project.

In Sample project and in general suggestion would be to use some random generated Database object factory helper - Bogus is best suited for this task. Here is factory implementation for Bogus, capable of supplying close-to-reality DB objects for integration tests:

using Salix.Dapper.Cqrs.MsSql.Testing.XUnit;
using MyApp.Domain;

namespace Sample.AspNet5Api.Database.Tests.Faker
{
    public sealed class TestObjectFactory : ITestObjectFactory
    {
        /// <summary>
        /// Dictionary with DB Class names and method to execute which creates appropriate object.
        /// </summary>
        private readonly Dictionary<string, Func<object>> _instantiators = new();

        /// <summary>
        /// Explicit static constructor to tell C# compiler not to mark type as beforefieldinit.
        /// </summary>
        static TestObjectFactory() {}

        /// <summary>
        /// Prevents a default instance of the <see cref="TestObjectFactory"/> class from being created.
        /// </summary>
        private TestObjectFactory()
        {
            // Add all DDD database objects here with their Faker implementations.
            // Key = exact type full name of DB POCO Class;  Value = Function which needs to be called when is asked for this object fake
            _instantiators.Add(typeof(Artist).FullName, ArtistBogus.GetFake);
            _instantiators.Add(typeof(Album).FullName, AlbumBogus.GetFake);
        }
        
        public static TestObjectFactory Instance { get; } = new();

        /// <summary>
        /// Determines whether Factory has fake generator for the specified type (full name).
        /// </summary>
        public bool HasFakeFor(string fullName) => _instantiators.ContainsKey(fullName);

        /// <summary>
        /// Returns a test object (filled with dummy, but "real" data) for testing purposes.
        /// </summary>
        public T GetTestObject<T>() where T : class
        {
            string classFullName = typeof(T).FullName;
            if (!this.HasFakeFor(classFullName))
                throw new ArgumentException($"There is no defined instantiation for {classFullName} object.");
            return (T)_instantiators[classFullName]();
        }

        /// <summary>
        /// Returns a test object (filled with dummy, but "real" data) for testing purposes.
        /// </summary>
        public object GetTestObject(Type objectType)
        {
            string classFullName = objectType.FullName;
            if (!this.HasFakeFor(classFullName))
                throw new ArgumentException($"There is no defined instantiation for {classFullName} object.");
            return _instantiators[classFullName]();
        }
    }
}

This Factory is then to be passed into instance of dummy parameters helper method HelperQueryCommandClasses.CreateDummyParametersForType(commandClassType, TestObjectFactory.Instance); so it can use these fake objects when generating dummy parameters in tests (see below).

For fake objects generation itself as mentioned - Bogus Faker is used, and here is simplistic class to generate Chinook DB Artist object:

public static class ArtistBogus
{
    public static Faker<Artist> GetBogus() =>
        new Faker<Artist>()
            .RuleFor(p => p.ArtistId, f => f.Random.Int(min: 1))
            .RuleFor(p => p.Name, f => f.Person.FullName)
    ;

    public static Artist GetFake() => GetBogus().Generate();
}

DTO == Table/View

Dapper.Cqrs.Testing package contains some helper classes/methods to perform comparison of database data contract (C# POCO DTO) with database object structure (e.g. matching table columns).

This would make sure changes in database structure are validated against POCO classes to ensure both are aligned.

Here is sample test for Chinook Artist table to match its DTO object (with same name - Artist):

public class ArtistTests : DatabaseTestsBase
{
    public ArtistTests(ITestOutputHelper outputHelper, SqlDatabaseFixture testFixture) =>
        this.InitializeTestContext(outputHelper, testFixture);

    [Fact]
    public async Task Columns_Db2Dto_Match()
    {
        this.TestFixture.ReopenTransaction(); // Required for assembly tests
        
        List<string> compareProblems = await HelperDatabaseTests.CompareDatabaseWithContract<Artist>(this.TestFixture);
        foreach (string problem in compareProblems)
        {
            this.TestFixture.WriteOutput(problem);
        }

        compareProblems.Should().BeEmpty("DB and Data contract should match, but there are {0} problems found with that. See Standard output", compareProblems.Count);
    }
}

Helper method has this contract:

/// <summary>
/// Compares the database column descriptions with data contract properties
/// and reports any misalignment (missing columns, missing properties, incompatible types used, null-ability for both).
/// </summary>
/// <typeparam name="T">Type of data contract.</typeparam>
/// <param name="fixture">The database tests fixture for getting database object columns metadata.</param>
/// <param name="tableName">Name of the table. Default (if not used) = data contract name.</param>
/// <param name="exceptPocoProperties">The exceptions to be used for data contract properties (extra properties, which should not be considered). When not specified - uses all found properties.</param>
/// <param name="exceptDatabaseFields">The exceptions to database column names (extra fields not used to map data to data contract). When not specified - uses all found columns in database object.</param>
public static async Task<List<string>> CompareDatabaseWithContract<T>(
    SqlDatabaseFixture fixture,
    string tableName = null,
    HashSet<string> exceptPocoProperties = null,
    HashSet<string> exceptDatabaseFields = null)

It returns list of found inconsistencies or empty list (not null!) if everything is OK.

NOTE: It will check also whether data types are matching, so if you have string/char on either side and other type on other side - it will report a problem. Also nullability is getting checked, so if one side has nullable field/type - it expect other side also to be nullable.

So if your DTO has the same name as table/view in database and properties in DTO are to be exactly the same as columns in database table/view - you can call it supplying just test fixture.

If table/view name is different - supply its name.

In case you have either extra properties in DTO or extra fields in database object and you want to exclude them from checking - supply list of those names in either of additional parameters:

exceptPocoProperties: new HashSet<string> { "DoNotCheckProperty", "ExcludeThisAlso" }

Data write-read

Another types of tests to be written are actual data writing and reading from database. These are straight forward tests and some are showcased in Sample application.

For writing you would prepare DTO object (see Bogus Test object factory above), then execute ICommand to save data, followed by IQuery to read saved data and compare both for equality.

When database has foreign keys to other business entities, you wither provide correct reference ID values or create dependencies in test as well.

public class AlbumTests : DatabaseTestsBase
{
    public AlbumTests(ITestOutputHelper outputHelper, SqlDatabaseFixture testFixture) =>
        this.InitializeTestContext(outputHelper, testFixture);

    [Fact]
    public async Task Album_SaveRead_Match()
    {
        this.TestFixture.ReopenTransaction();

        // As Album has dependency on Artist - we create that first
        Artist dependencyBogus = TestObjectFactory.Instance.GetTestObject<Artist>();
        int dependencyBogusArtistId = await this.TestFixture.Db.ExecuteAsync(new ArtistCreateCommand(dependencyBogus));
 
        // Preparing and creating new Album record - one we are interested in.
        Album testBogus = TestObjectFactory.Instance.GetTestObject<Album>();
        testBogus.ArtistId = dependencyBogusArtistId;
        int testBogusAlbumId = await this.TestFixture.Db.ExecuteAsync(new AlbumCreateCommand(testBogus));

        // Now read back saved data in testable object
        Album testable = await this.TestFixture.Db.QueryAsync(new AlbumByIdQuery(testBogusAlbumId));

        // To avoid leaving saved data in database
        // (Do this before Asserts as asserting may fail test and not run this if it is the last statement in test)
        this.TestFixture.RollbackTransaction();

        testable.Should().NotBeNull();
        testBogus.AlbumId = testBogusAlbumId; // Make initial bogus ID equivalent to auto-incremented value for object comparison below.
        testable.Should().BeEquivalentTo(testBogus);
    }
}