0

This is a rewrite of my own

.Net 8 XUnit: Should my tests use real data or how to mock MySql with CQRS?

in a much more clear way:

The reason for rewriting my previous question is because in the previous one I didn't explained my problem correctly (and clearly) and one user suggested me that instead of editing and modifying a question which already has answers, I should create a new question, so here it is:

I'm creating integration tests for our .Net 8 CQRS API. We use MySql for data storage and CQRS pattern for queries.

We have a "Lab" class we use to initialize tests where I should create a reduced set of sample data to be used then in the cqrs query handler, and this is my problem, not being able / knowing how to mock MySql data in our "Lab" class context so the "QueryAsync" method in the query handler will return that data, instead of working with the real database.

In our query handlers we inject an interface with a "QueryAsync" method which implementation creates a connection to MySql for the queries using just MySqlConnection, but don't know how to test the data part in the integration test.

I was able to mock that interface (code shown below) to return a set of data I create myself in the lab, and with that I can assert the data is traveling through the layers and arrives to destiny, but I'm not testing the data returned is actually the data expected this way, because I haven't faked the database, and this is something I also should do.

In resume, I'm doing an integration test of a get controller endpoint and would need to have a mocked MySql database where the data will be picked of in the query handler (instead of using the real database), and I don't know how to do it.

I'll paste some relevant pieces of code in case it helps.

Program.cs:

await RunServer.RunAsync<Startup>(
    args,
    (builder, options) =>
    {
        ...
        options.OtherServices = () =>
        {
            builder.Services.AddScoped<ICloudStackDbUnitOfWork, CloudStackDbUnitOfWork>();
            builder.Services.AddScoped<ICloudStackCoreConnectionFactory, MySqlCloudStackCoreConnectionFactory>();
            ...
        };
        ...
    },
);

ICloudStackCoreConnectionFactory (connection to MySql):

public interface ICloudStackCoreConnectionFactory
{
    DbConnection CreateConnection(Region region);

    Task<(int Count, TOut[] Data)> QuerySearchAsync<TDbItem, TOut>(IRegionRepository regionRepository, string fetchDataQuery, string? countDataQuery = null, ... more parameters) where TOut : class;

    Task<int> ExecuteAsync(IRegionRepository regionRepository, string sql, object? @params = null, Func<Region, bool>? filter = null, CancellationToken ct = default(CancellationToken));
}

MySqlCloudStackCoreConnectionFactory (just the skeleton):

public sealed class MySqlCloudStackCoreConnectionFactory : ICloudStackCoreConnectionFactory
{
    public MySqlCloudStackCoreConnectionFactory()
    {
        
    }

    public DbConnection CreateConnection(Region region)
    {
        return new MySqlConnection(GetConnString(region));
    }

    Public Async Task<(int Count, TOut[] Data)> QuerySearchAsync<TDbItem, TOut>(IRegionRepository regionRepository, string fetchDataQuery, string? countDataQuery = null, ... more parameters) where TOut : class;
    {
        ...
        DbConnection connection = CreateConnection(region);
        ...
    }

    public async Task<int> ExecuteAsync(IRegionRepository regionRepository, string sql, object? @params = null, Func<Region, bool>? filter = null, CancellationToken ct = default(CancellationToken))
    {
        ...
        DbConnection connection = CreateConnection(region);
        ...
    }
}

QueryHandler (CQRS): (retrieving data from MySql)

internal sealed class SearchQueryHandler : IVdcQueryHandler<SearchQuery, SearchResponse>
{
    private readonly IRegionRepository _regionRepository;
    private readonly ICloudStackCoreConnectionFactory _cloudStackCoreConnectionFactory;

    public SearchQueryHandler(
        ICloudStackCoreConnectionFactory cloudStackCoreConnectionFactory,
        IRegionRepository regionRepository)
    {
        _cloudStackCoreConnectionFactory = cloudStackCoreConnectionFactory;
        _regionRepository = regionRepository;
    }

    public async ValueTask<SearchResponse> HandleAsync(SearchQuery request, CancellationToken ct = default)
    {
        var sql = GetSearchQuerySql();

        var regionFilter = GetFilters(request.Filter, sql);

        var (count, data) = await _cloudStackCoreConnectionFactory.QuerySearchAsync<SearchResponse.Account, SearchResponse.Account>(
            _regionRepository,
            sql.fetch.ToString(),
            ... all the rest of parameters
        );

        return new SearchResponse(count, data);
    }

    private (StringBuilder fetch, StringBuilder count) GetSearchQuerySql()
    {
        var fetch = new StringBuilder($"""
            SELECT 
                UUID AS {nameof(SearchResponse.Account.Id)}, 
                account_name AS {nameof(SearchResponse.Account.Name)} 
            FROM cloud.account acc 
            WHERE acc.state != 'disabled' AND removed IS NULL
        """);

        return (fetch, ...);
    }
}

Unit Testing:

In this next lab pay attention on how I do mock the results of QuerySearchAsync through the implementation MySqlMock and IAccountList:

What I do that way is to test the flow of data from beginning to end, and not the actual data.

I should also mock the database and test the returned data is the expected one.

Lab we use to initialize tests:

public sealed class Lab
{    
    /// <summary>
    /// Create a lab with the requirement of this project
    /// </summary>
    public static Lab Create(ITestOutputHelper output)
    {
        var appFactory = Vdc.Libs.AspNet.Testing.Setup.Factory<Startup, InnerDbContextRead, InnerDbContextWrite>(
            out var client,
            out var authorize,
            out var identity,
            out var faker,
            role: $"{VdcSecurity.Role.Management},{VdcSecurity.Role.ManagementAdmin}",
            output: output,
            setup: services =>
            {
                services.AddSingleton<IAccountList, AccountList>();
                services.AddScoped<ICloudStackCoreConnectionFactory, MySqlMock>();
            }
        );
        return new Lab(identity, appFactory, client, authorize, faker);
    }

    public class AccountList : IAccountList
    {
        SearchResponse.Account[] IAccountList.accounts => [
            CreateAccount("Account1", AccountTypes.Managed),
            CreateAccount("Account2", AccountTypes.Managed),
            CreateAccount("Account3", AccountTypes.Unmanaged)
        ];

        private SearchResponse.Account CreateAccount(string accountName, AccountTypes typeId)
        {
            SearchResponse.Account account;
            account = new SearchResponse.Account
            {
                Id = Guid.NewGuid(),
                Name = accountName,
                AccountOrder = 0,
                RegionId = 1
            };
            return account;
        }
    }   
}

MySqlMock:

public interface IAccountList
{
    SearchResponse.Account[] accounts { get; }
}

public class MySqlMock : ICloudStackCoreConnectionFactory
{
    IAccountList _accounts;
    public MySqlMock(IAccountList accounts)
    {
        _accounts = accounts;
    }

    public DbConnection CreateConnection(Vdc.Libs.Region region)
    {
        return new MySqlConnection();
    }

    public async Task<(int Count, TOut[] Data)> QuerySearchAsync<TDbItem, TOut>(
        IRegionRepository regionRepository, 
        string fetchDataQuery, 
        ... all the parameters) where TOut : class
    {
        return (_accounts.accounts.Length, _accounts.accounts as TOut[]);
    }
}

My Test:

[Theory]
[InlineData(0, 5)]
public async Task GetFirst5Element_OrderByName_ReturnOnly5ElementOrderByName(int page, int expectedElements)
{
    using var scope = _lab.AppFactory.Services.CreateScope();
    var provider = scope.ServiceProvider;

    // Arrange
    var events = await _lab.InitAsync(provider, false);

    // Act 
    var request = new SearchRequest(
        null,
        new Paging { Page = page, PageSize = expectedElements },
        [new Vdc.Libs.Web.ColumnNameRequest("Name")) { Asc = true }]
    );
    var response = await _client.SearchAsync(_lab.Identity, request);

    // Assert

    //TODO: still need to check the results with the sample data I've created
    response.Results.GetType().Should().Be(typeof(List<SearchResponse.Account>));
    response.Results.Count().Should().Be(expectedElements);
}

IApiService (used to call the controller when testing):

public interface IAccountsApiService : IApiService
{
    Task<SearchResponse> SearchAsync(IdentityInfo identity, SearchRequest request, string? correlationId = null, CancellationToken ct = default);
}

public sealed class AccountsApiService : BaseApiService, IAccountsApiService
{
    private readonly AuthorizeOptions _options;

    public AccountsApiService(IOptions<AuthorizeOptions> options, IApiClient apiClient, ILogger<AccountsApiService> logger) 
        : base(apiClient, logger: logger)
    {
        _options = options.Value;
    }

    public async Task<SearchResponse> SearchAsync(IdentityInfo identity, SearchRequest request, string? correlationId = null, CancellationToken ct = default)
    {
        var (response, _) = await ApiClient.SendAsync<SearchResponse>(
            HttpMethod.Get,
            "/api/accounts",
            message => message.AddAuthorization(identity, _options.ApiKey, correlationId),
            model: request,
            ct: ct
        );
        return response!;
    }
}

Controller (the actual controller I'm testing):

[ApiController]
[Route("api/[controller]")]
[Produces("application/json")]
[Authorize(Roles = ...]
public class AccountsController : ControllerBase
{
    private readonly IServiceProvider _provider;

    public AccountsController(IServiceProvider provider)
    {
        _provider = provider;
    }
    
    [HttpGet]
    public async Task<ActionResult<SearchResponse>> Get(
        [FromServices][IsSensitive] IQueryDispatcher dispatcher,
        [FromQuery] SearchRequest request,
        [IsSensitive] CancellationToken ct = default
    )
    {
        var identity = HttpContext.GetIdentity();
        var query = new SearchQuery(identity, HttpContext.TraceIdentifier)
        {
            Filter = request.Filter,
            Order = request.Order?.ToColumnName() ?? Constants.Page.Sorting,
            Paging = request.Paging ?? Constants.Page.Paging,
        };
        var result = await dispatcher.DispatchAsync(_provider, query, ct);

        return result.ToActionResult(this);
    }
}

Any help / documentation urls / code to mock MySql database so I can test that the results (when testing) are the expected results will be welcome.

Edit 1: How we do it with MSSQL (by request of @Ewan to understand what I'm trying to replicate with MySql).

Lab.cs:

private readonly Job[] _data;

private Lab(IdentityInfo identity, WebApplicationFactory<Startup> appFactory, IApiClient apiClient, AuthorizeOptions authorize, Faker faker)
{
    _faker = faker;
    _identity = identity;
    _appFactory = appFactory;
    _apiClient = apiClient;
    _authorize = authorize;

    _data = [
        GetJob(JobInfo.UsageAllocationName, UsageAllocationJobInEuropeWithCompleteStatusIndex + 1, StatusValues.Completed, new JobInfo.Args(2024, 1, 1, Region.Europe, null)),
        GetJob(JobInfo.UsageAllocationName, UsageAllocationJobInAsiaWithCompleteStatusIndex + 1, StatusValues.Completed, new JobInfo.Args(2024, 1, 1, Region.Asia, null)),
        GetJob(JobInfo.UsageAllocationName, UsageAllocationJobInNAWithCompleteStatusIndex + 1, StatusValues.Completed, new JobInfo.Args(2024, 1, 1, Region.NA, null)),
        GetJob(JobInfo.UsageDedicatedName, UsageDedicatedJobInEuropeWithCompleteStatusIndex + 1, StatusValues.Completed, new JobInfo.Args(2024, 2, 1, Region.Europe, null)),
        GetJob(JobInfo.BillingProcessName, BillingJobInEuropeWithPendingStatusIndex + 1, StatusValues.Pending, new JobInfo.Args(2024, 1, 1, Region.Europe, null)),
        GetJob(JobInfo.ObjectStorageAllocationName, ObjectStorageAllocationJobInEuropeWithCancelledStatusIndex + 1, StatusValues.Cancelled, new JobInfo.Args(2024, 1, 1, Region.Europe, null)),
        GetJob(JobInfo.BillingProcessName, BillingJobInEuropeWithAssignedStatusIndex + 1, StatusValues.Assigned, new JobInfo.Args(2024, 2, 1, Region.Europe, null)),
   ];
}

public async Task<EventSend?> InitAsync(IServiceProvider provider, bool useEvents = false, FilterObject? filter = null)
{
    var unitOfWork = provider.GetRequiredService<IVdcUnitOfWork>();

    PopulateJobs(provider, _data, filter); //=> set of sample data for testing.
    await unitOfWork.SaveChangesAsync();

    EventSend? events = null;
    if (useEvents)
        Events.Capture(provider, events = new EventSend());

    return events;

    // ==============================================================================================================
    static void PopulateJobs(IServiceProvider provider, Job[] dataSample, FilterObject? filter)
    {
        var jobRepository = provider.GetRequiredService<IVdcRepository<Job>>();

        IEnumerable<Job> data = dataSample;
        if (filter is not null)
        {
            if (filter.Status is not null)
                data = data.Where(x => filter.Status(x.StatusId));
        }
        jobRepository.AddRange(data.ToArray());
    }
}

private Job GetJob(string jobName, int id, StatusValues status, JobInfo.Args args)
{
    Job job;
    var serializer = AppFactory.Services.GetRequiredService<IJsonSerializer>();
    job = new Job
    {
        Id = id,
        Name = jobName,
        Assigned = null,
        Description = $"{jobName} description",
        Notes = _faker.Random.String(20),
        Progress = 0,
        StatusId = status,
        TimeOut = JobInfo.GetTimeOut(jobName),
        TriggerId = TriggerValues.Scheduled,
        WorkerId = _faker.Random.String2(6),
        Created = _faker.Date.Past()
    };
    job.Params = JobInfo.GetArgs(serializer, jobName, args);
    job.JobLogs = new HashSet<JobLog>();
    job.Started = null;
    job.Completed = null;
    ...
}

IVdcUnitOfWork:

public interface IVdcUnitOfWork : IUnitOfWork, IDisposable, IDbContextWrapper
{
}

IUnitOfWork:

public interface IUnitOfWork : IDisposable
{
    int SaveChanges();

    Task<int> SaveChangesAsync(CancellationToken ct = default(CancellationToken));

    IEnumerable<Type> GetModelEntityTypes();
}

IDbContextWrapper:

public interface IDbContextWrapper
{
    DbContext GetDbContext();
}

Jobs query handler (minified):

private readonly IVdcQueryRepository<Job> _jobQueryRepository;

public async ValueTask<SearchJobsResponse> HandleAsync(SearchQuery request, CancellationToken ct = default)
{
    IQueryable<Job> query = _jobQueryRepository
        .FindAll()
        .Include(i => i.Status)
        .Include(i => i.Trigger);

        ...

    var (count, data) = await query.ToSearchAsync(request.Paging, request.Order, ct);
    return new SearchJobsResponse(count, _mapper.Map<JobResponse[]>(data)!);
}

IJobRepository:

public interface IJobRepository
{
    Task<int> ExpireJobsAsync(CancellationToken ct = default);
    Task<int> FailAsync(int jobId, CancellationToken ct = default);
    Task<Job> CreateAsync(Job job, CancellationToken ct = default);
    Task<int> CompleteAsync(int jobId, string note, CancellationToken ct = default);
    Task<int> UpdateJobAsync(int jobId, int progress, string notes, CancellationToken ct = default);
    Task<int> AssignAsync(int id, CancellationToken ct= default);
}
public sealed class JobRepository : IJobRepository
{
    private readonly ISysClock _clock;
    private readonly IVdcUnitOfWork _unitOfWork;
    private readonly IVdcRepository<Job> _jobRepository;

    public JobRepository(ISysClock clock, IVdcUnitOfWork unitOfWork, IVdcRepository<Job> jobRepository)
    {
        _clock = clock;
        _unitOfWork = unitOfWork;
        _jobRepository = jobRepository;
    }


    public Task<int> FailAsync(int jobId, CancellationToken ct = default)
    {
        var jobIdParam = new SqlParameter("@id", jobId);
        return _unitOfWork.GetDbContext().Database.ExecuteSqlRawAsync(
            "exec FailJob @id", 
            [jobIdParam], 
            ct
        );
    }

    public Task<int> CompleteAsync(int jobId, string notes, CancellationToken ct = default)
    {
        var notesParam = new SqlParameter("@notes", notes);
        var jobIdParam = new SqlParameter("@id", jobId);
        return _unitOfWork.GetDbContext().Database.ExecuteSqlRawAsync(
            "exec CompleteJob @id, @notes", 
            [jobIdParam, notesParam],
            ct
        );
    }

    public Task<Job> CreateAsync(Job job, CancellationToken ct = default) => Task.Run(() =>
    {
        var query = _unitOfWork.GetDbContext().Set<Job>().FromSql(
            $"""
            [RequestJob] 
                @Name={job.Name}, 
                @TriggerId={job.TriggerId:D}, 
                @Description={job.Description}, 
                @TimeOut={job.TimeOut}, 
                @Params={job.Params}, 
                @Notes={job.Notes} 
            """
        );
        return query.ToArray()[0];
    });

    public Task<int> UpdateJobAsync(int jobId, int progress, string notes, CancellationToken ct = default)
    {
        var notesParam = new SqlParameter("@notes", notes);
        var progressParam = new SqlParameter("@progress", progress);
        var jobIdParam = new SqlParameter("@id", jobId);
        return _unitOfWork.GetDbContext().Database.ExecuteSqlRawAsync(
            "exec UpdateJob @id, @progress, @notes", 
            [jobIdParam, progressParam, notesParam], 
            ct
        );
    }

    public async Task<int> ExpireJobsAsync(CancellationToken ct = default)
    {
        var utcNow = _clock.UtcNow;
        var jobs = await _jobRepository.FindAll()
            .Include(i => i.JobLogs)
            .Where(p =>
                (p.StatusId == StatusValues.Assigned && EF.Functions.DateDiffSecond(p.Assigned, utcNow) >= p.TimeOut) ||
                (p.StatusId == StatusValues.InProgress && EF.Functions.DateDiffSecond(p.Started, utcNow) >= p.TimeOut)
            )
            .ToArrayAsync(ct);

        foreach (var job in jobs)
        {
            job.Completed = utcNow;
            job.StatusId = StatusValues.Expired;
            job.JobLogs!.Add(new JobLog { Created = utcNow, Note = "Time out" });
        }
        return await _unitOfWork.SaveChangesAsync(ct);
    }

    public Task<int> AssignAsync(int jobId, CancellationToken ct = default)
    {
        var jobIdParam = new SqlParameter("@id", jobId);
        return _unitOfWork.GetDbContext().Database.ExecuteSqlRawAsync("exec AssignJob @id", jobIdParam);
    }
}

IVdcRepository:

public interface IVdcRepository<TEntity> : IEFRepository<DbContextWrite, TEntity>, IRepository<TEntity>, IQueryRepository<TEntity>, IVdcQueryRepository<TEntity> where TEntity : class
{
}

DbContextWrite:

public abstract class DbContextWrite : DbContext
{
    protected DbContextWrite([NotNull] DbContextOptions options)
        : base(options)
    {
    }
}

Job test:

[Theory]
[InlineData(0, 5)]
[InlineData(1, 2)]
public async Task GetFirst5Element_OrderById_ReturnOnly5ElementOrderById(int page, int expectedElements)
{
    using var scope = _lab.AppFactory.Services.CreateScope();
    var provider = scope.ServiceProvider;

    // Arrange
    var events = await _lab.InitAsync(provider, true);
    var statusRepository = provider.GetRequiredService<IVdcQueryRepository<Status>>();

    // Act 
    var request = new SearchJobsRequest(
        null,
        new Paging { Page = page, PageSize = 5 },
        [new Vdc.Libs.Web.ColumnNameRequest(nameof(JobResponse.Id)) { Asc = true }]
    );
    var response = await _client.SearchAsync(_lab.Identity, request);

    // Assert
    response.Count.Should().Be(_lab.Jobs.Length);
    response.Results.Count().Should().Be(expectedElements);
}

IQueryRepository:

public interface IQueryRepository<TEntity> where TEntity : class
{
    IQueryable<TEntity> FindAll();

    ValueTask<TEntity?> FindAsync(object[] keyValues, CancellationToken ct = default(CancellationToken));
}

DesignTimeDbContextFactory:

public sealed class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<InnerDbContextWrite>
{
    public const string ConnString = "JobManagerDb";

    public InnerDbContextWrite CreateDbContext(string[] args)
    {
        var builder = new ConfigurationBuilder()
            .AddJsonFile(Path.Combine(Directory.GetCurrentDirectory(), "appsettings.Development.json"))
            .Build();
        var connString = builder.GetConnectionString(ConnString);

        var builderOptions = new DbContextOptionsBuilder<InnerDbContextWrite>();
        SqlServerInitHelper.SQLWriteBuider<InnerDbContextWrite>(connString!, builderOptions);

        return new InnerDbContextWrite(builderOptions.Options);
    }
}

InnerDbContextWrite:

public sealed class InnerDbContextWrite : DbContextWrite
{
    public InnerDbContextWrite([NotNull] DbContextOptions<InnerDbContextWrite> options) :
        base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        this.OnModelCreating(typeof(EntityTypeConfiguration<>), builder, _ => true);
    }
}
26
  • 2
    dude no one is going to read past the first code block. Also you don't say what the problem with the previous answers was. What do you mean by "mock a mysql db"? you mean run an in memory db? mock one of your classes? what?
    – Ewan
    Commented Jul 9 at 16:20
  • what would be useful is if you showed how the MSsql test worked and highlighted the bit you can't do in MYsql
    – Ewan
    Commented Jul 9 at 16:22
  • 1
    Note the SE site format is for only one version of each question. not 2, 3 or more different version of the same question. If you want to clarify your former version, please do so by editing (BTW, when I wrote "please shorten your code", I meant to shorten it to its essence, which is probably 10-20% of the current size.
    – Doc Brown
    Commented Jul 9 at 18:42
  • 1
    also, you still ignore all the questions peopel ask you for more info
    – Ewan
    Commented Jul 9 at 19:51
  • 1
    I've tried to ask you 4 questions and no answers. The reason i'm nagging you about this is because it's this kind of thing that will make your question clear and mean you get good answers in future
    – Ewan
    Commented Jul 9 at 20:05

3 Answers 3

1

OK so to summarise the EF tests

  • Lab setup is not shown. But we can see a WebApplicationFactory is passed in and the IVdcRepository<Job> implementation is extracted from the DI container and the test data inserted directly into it with AddRange

  • This is presumably using the EF DbContext.AddRange although it looks like you are overriding a lot of default EF stuff. This means the rows will be added to the EF collection, but not written to the DB until SaveChanges() is called.

  • The test then continues, it looks like it calls the controller via the WebApplicationFactory, which will then pick up the same IVdcRepository<Job> implmentation, which will return the lab data which was added during lab setup.

We can't see how the EF context is setup, there could be a real db behind it, it could be running in in memory mode, or maybe it will just work with no connection string as it never tries to SaveChanges().

This is a sneaky way of testing I have not seen before. I would run EF in inmemory mode and just populate the Jobs via some other controller method. But maybe that is hard for some reason in this case.

Now for the MySql side.

  • You don't include the critical QuerySearchAsync method, but presumably this actually calls a database as you are passing in an SQL string

  • This means there isn't really an equivalent AddRange<Job> method you can use to add phantom data.

  • If you mock the QuerySearchAsync method to just return the sample data when passed the correct SQL string your tests will work, the downside is the fragility of that hardcoded SQL string in your test, and not really knowing if your SQL is correct.

  • This worry doesn't really apply to the EF version, as thats Microsoft's code and you assume they have tested it.

My Recommendation

For all DB tests my recommendation is to spin up a real database in a container, using your migration scripts or base DB version to make and populate the DB with its base data and schema.

Then add test data using publicly exposed add methods

Then retrieve the test data using the method you want to test.

You haven't included anything about how you manage the database deployment, if you have migration scripts or use a tool to store it in source control. So I really can't give any practical advice on how to achieve that.

Summary

The way you are phrasing your question with at the same time too much and too little code implies to me that you don't fully understand how it works.

I don't think this is a reflection on you however, because the code seems overly complicated with multiple levels of abstraction hiding what amounts to a simple select * from jobs and then a whole load of unnecessary test framework thrown in on top of that.

Obviously I don't know all the compromises that have been made, but with a real database your test code could look like:

[Theory]
[InlineData(0, 5)]
[InlineData(1, 2)]
public async Task SearchAsync_Works(int page, int expectedElements)
{
    // Arrange
    var _client = GetWebFacClient();

    _client.AddJob(new Job() {...}); //add seven jobs

    // Act 
    var request = new SearchJobsRequest(
        null,
        new Paging { Page = page, PageSize = 5 },
        [new Vdc.Libs.Web.ColumnNameRequest(nameof(JobResponse.Id)) { Asc = true }]
    );
    var response = await _client.SearchAsync(_lab.Identity, request);

    // Assert
    response.Count.Should().Be(expectedElements);
    response.Results.Count().Should().Be(expectedElements);
}

Where/Why Software Engineering failed

There is an over reliance on the questioner knowing what to ask and how to phrase the question. Here The question has nothing to do with ,net 8, xunit, crqs or mocking! It barely concerns MySql and there is a tonne of information which is irrelevant. But how is the questioner supposed to know this?

4
  • Hello Mr @Ewan, thanks so much 4 your time and effort in your response (I'll definitely mark it as correct). U are right in all your assumptions. The code 4 "QuerySearchAsync" is too long, that's why I haven't included it at first, but given your answer, I've just added it 2 my own answer just in case (you'll see an edit). I totally agree with u that the code is overcomplicated with many ef overrides and abstractions, and my college (who did all this) is on holidays, so I'm complete alone with this, but I have 2 go on, which I presume it will be as I explain in my own answer. Commented Jul 11 at 7:46
  • I'll go giving the "QuerySearchAsync" method works as a fact (as if it were from Microsoft, as you say), faking a set of "ghost" data inside my fake implementation. Of course I'm not testing the methd (which does a lot of things like ordering and paging) this way, but at least I can finish my tests with something functional, testing the flow of data. Of course at first I was not able to communicate my problem correctly, but not for the lack of knowledge, but for this complex project where I'm not even sure if I'm doing integration or unit testing. Commented Jul 11 at 7:49
  • And of course I was expecting to test the whole, including this QuerySearchAsync method, test the sort and paging options and that sort of things too, but cannot go and create a Docker database and such complicated stuff, because I was not asked to do that (at least for now). That's why I think my decision on how to go is the most appropiate for now, but your input was really very valuable, so again, I appreciate your time very much :) Commented Jul 11 at 7:52
  • I dare to assert, @Ewan, that as "QuerySearchAsync" method is a generic query method for MySql located in a separate library, what I'm doing -give as a fact that that method works- is correct, in the end, that method -as it is not part of this project- should be tested in its own project. Still not sure if what we have here are integration or just unit tests, but thanks to this long thread and all your input I know I have much more clear all that surrounds the tests I have to do, and mostly, that I'm going in the most appropriate way given what I have. I hope all this will help someone else :) Commented Jul 11 at 8:06
1

Integration test use real data and real databases to ensure that your components communicate across boundaries. If you are doing an integration test, you need to figure out how to make that work, not how to mock it.

Integration test should not be about do you get the expected results - result set with 5 rows or 6, are all the same. Performance and results matter, deserialization matters. I don’t see your (single) actual test, directly testing anything that matters.

I haven’t done it myself (a co-worker has), but it’s possible to create a new database, populate it with a reasonable amount of RELEVANT test data, run your test, and drop the new database once you’re done testing in a reasonable amount of time for an integration test (not for a unit test) done after a build on a build server. If you need to do integration test with a database, it should be an actual database, this does not mean it needs to be a production database.

Now this does have limitations, but it’s at least worth looking into if you have multiple things that need to work together (database, rabbitmq, multiple micro services, etc).

As an aside, I don’t know what you should be testing, but I’m pretty sure your test above is useless and all of the non-test code above is irrelevant.

1
  • Thanks 4 your input @jmoreno. I asked my team leader and he says "as far as I know we have unit testing, and not integration tests", so I'm not sure I have formulated the question correctly, but in the end what I should test is that the result from the controller coincide with a predefined set of data created in the lab. Commented Jul 10 at 9:40
1

What I ended up doing is to mock the interface who has the MySql methods and inject the expected data from the Lab class, then in the test I call the controller and compare result with the result get from calling just the implementation of the MySql interface separately:

Lab.cs:

public sealed class Lab
{
    private readonly Faker _faker;
    private readonly IdentityInfo _identity;
    private readonly WebApplicationFactory<Startup> _appFactory;
    private readonly IApiClient _apiClient;
    private readonly AuthorizeOptions _authorize;

    private static Account[]? _accounts;

    private Lab(IdentityInfo identity, 
                WebApplicationFactory<Startup> appFactory, 
                IApiClient apiClient, 
                AuthorizeOptions authorize, 
                Faker faker)
    {
        _faker = faker;
        _identity = identity;
        _appFactory = appFactory;
        _apiClient = apiClient;
        _authorize = authorize;
    }

    public Faker Faker => _faker;
    public IdentityInfo Identity => _identity;
    public IApiClient ApiClient => _apiClient;
    public AuthorizeOptions Authorize => _authorize;
    public WebApplicationFactory<Startup> AppFactory => _appFactory;
    public static Account[]? Accounts => _accounts;

    public EventSend? InitAsync(IServiceProvider provider, bool useEvents = false, int expectedElements = 5)
    {
        var unitOfWork = provider.GetRequiredService<IVdcUnitOfWork>();

        _accounts = PopulateAccounts(_faker, expectedElements);

        EventSend? events = null;
        if (useEvents)
            Events.Capture(provider, events = new EventSend());

        return events;

        Account[] PopulateAccounts(Faker faker, int expectedElements)
        {
            //var accounts = new Account[expectedElements];
            var accounts = new List<Account>();
            for (var i = 0; i < expectedElements; i++) 
            {
                var account = new Account
                {
                    Id = faker.Random.Uuid(),
                    Name = faker.Random.AlphaNumeric(10),
                    AccountOrder = faker.Random.Int(),
                    RegionId = (Region)faker.Random.Int(1, Enum.GetNames<Region>().Length)
                };
                accounts.Add(account);
            }
            return accounts.ToArray();
        }
    }

    public static Lab Create(ITestOutputHelper output)
    {
        var instance = new FakeData();
        var appFactory = Vdc.Libs.AspNet.Testing.Setup.Factory<Startup, InnerDbContextRead, InnerDbContextWrite>(
                out var client,
                out var authorize,
                out var identity,
                out var faker,
                role: $"{VdcSecurity.Role.Management},{VdcSecurity.Role.ManagementAdmin}",
                output: output,
                setup: services =>
                {
                    services.AddSingleton<IData>(instance);
                    services.AddSingleton<ICloudStackCoreConnectionFactory, MySqlMock>();
                }
            );

        return new Lab(identity, appFactory, client, authorize, faker);
    }

    public class FakeData : IData
    {
        Account[]? IData.Accounts => Accounts;
    }
}

MySqlMock:

public interface IData
{
    Account[]? Accounts { get; }
}

public class MySqlMock : ICloudStackCoreConnectionFactory
{
    IData _data;
    public MySqlMock(IData data)
    {
        _data = data;
    }

    public DbConnection CreateConnection(Vdc.Libs.Region region)
    {
        return new MySqlConnection();
    }

    public async Task<(int Count, TOut[] Data)> QuerySearchAsync<TDbItem, TOut>(
        IRegionRepository regionRepository, 
        string fetchDataQuery, 
        string? countDataQuery = null, ...) where TOut : class
    {
        var type = typeof(TOut);

        if (type == typeof(Account)) 
        {
            var accounts = _data.Accounts;
            return (accounts.Length, accounts as TOut[]);
        }

        return (0, []);
    }

    public Task<int> ExecuteAsync(IRegionRepository regionRepository, string sql, object? @params = null, Func<Vdc.Libs.Region, bool>? filter = null, CancellationToken ct = default)
    {
        throw new NotImplementedException();
    }
}

Accounts test:

[Theory]
[InlineData(0, 8)]
public async Task GetFirst8Elements_ReturnOnly8Elements(int page, int expectedElements)
{
    using var scope = _lab.AppFactory.Services.CreateScope();
    var provider = scope.ServiceProvider;

    // Arrange
    var events = _lab.InitAsync(provider, false, expectedElements);
    var accountsRepository = provider.GetRequiredService<ICloudStackCoreConnectionFactory>();
    var regionRepository = provider.GetRequiredService<IRegionRepository>();

    // Act 
    var request = new SearchRequest(
        null,
        new Paging { Page = page, PageSize = expectedElements },
        [new Vdc.Libs.Web.ColumnNameRequest(nameof(SearchResponse.Account.Name)) { Asc = true }]
    );
    var response = await _client.SearchAsync(_lab.Identity, request);

    // Assert
    var accounts = await accountsRepository.QuerySearchAsync<SearchResponse.Account, SearchResponse.Account>(
        regionRepository, "", null, null, null, null, null, null, null, null, false, default);

    response.Results.Should().BeEquivalentTo(accounts.Data);
    response.Results.GetType().Should().Be(typeof(List<SearchResponse.Account>));
    response.Results.Count().Should().Be(expectedElements);
}

I know this way at least I'm testing the flow of data from beginning to end.

What I was expecting, to mock the MySql database to do a full test, including the QuerySearchAsync method is much more complex, and maybe it does not even worth, in the end, this is the way I've seen more or less it's done in other APIs.

Sorry I was not able to express myself more correctly from the beginning, but I was confused, and today I was being told our tests are unit tests, and not integration tests.

Edit 1: Adding two more methods for clarification.

IAccountsApiService (the http client to call the controller:

public interface IAccountsApiService : IApiService
{
    Task<SearchResponse> SearchAsync(IdentityInfo identity, SearchRequest request, string? correlationId = null, CancellationToken ct = default);
}

public sealed class AccountsApiService : BaseApiService, IAccountsApiService
{
    private readonly AuthorizeOptions _options;

    public AccountsApiService(IOptions<AuthorizeOptions> options, IApiClient apiClient, ILogger<AccountsApiService> logger) 
        : base(apiClient, logger: logger)
    {
        _options = options.Value;
    }

    public async Task<SearchResponse> SearchAsync(IdentityInfo identity, SearchRequest request, string? correlationId = null, CancellationToken ct = default)
    {
        var (response, _) = await ApiClient.SendAsync<SearchResponse>(
            HttpMethod.Get,
            "/api/accounts",
            message => message.AddAuthorization(identity, _options.ApiKey, correlationId),
            model: request,
            ct: ct
        );
        return response!;
    }

    public async Task<SearchResponse> SearchAsync(IdentityInfo identity, string? correlationId = null, CancellationToken ct = default)
    {
        var (response, _) = await ApiClient.SendAsync<SearchResponse>(
            HttpMethod.Get,
            "/api/accounts",
            message => message.AddAuthorization(identity, _options.ApiKey, correlationId),
            ct: ct
        );
        return response!;
    }
}

AccountsController:

[ApiController]
[Route("api/[controller]")]
[Produces("application/json")]
[Authorize(Roles = $"{VdcSecurity.Role.Management},{VdcSecurity.Role.ManagementAdmin}")]
public class AccountsController : ControllerBase
{
    private readonly IServiceProvider _provider;

    public AccountsController(IServiceProvider provider)
    {
        _provider = provider;
    }

    [HttpGet]
    public async Task<ActionResult<SearchResponse>> Get(
        [FromServices][IsSensitive] IQueryDispatcher dispatcher,
        [FromQuery] SearchRequest request,
        [IsSensitive] CancellationToken ct = default
    )
    {
        var identity = HttpContext.GetIdentity();
        var query = new SearchQuery(identity, HttpContext.TraceIdentifier)
        {
            Filter = request.Filter,
            Order = request.Order?.ToColumnName() ?? Constants.Page.Sorting,
            Paging = request.Paging ?? Constants.Page.Paging,
        };
        var result = await dispatcher.DispatchAsync(_provider, query, ct);

        return result.ToActionResult(this);
    }
}

MySqlCloudStackCoreConnectionFactory:

public sealed class MySqlCloudStackCoreConnectionFactory : ICloudStackCoreConnectionFactory
{
    private sealed record QTaskInfo<TDbItem>(Region Region, Task<int> CountTask, Task<IEnumerable<TDbItem>> DataTask);

    private readonly CloudStackDBOptions _configuration;

    private readonly ILogger<MySqlCloudStackCoreConnectionFactory> _logger;

    private static readonly ConcurrentDictionary<Region, bool> _connections = new ConcurrentDictionary<Region, bool>();

    public MySqlCloudStackCoreConnectionFactory(IOptions<CloudStackDBOptions> options, ILogger<MySqlCloudStackCoreConnectionFactory> logger)
    {
        _configuration = options.Value;
        _logger = logger;
    }

    public DbConnection CreateConnection(Region region)
    {
        return new MySqlConnection(GetConnString(region));
    }

    public async Task<(int Count, TOut[] Data)> QuerySearchAsync<TDbItem, TOut>(IRegionRepository regionRepository, string fetchDataQuery, string? countDataQuery = null, Dictionary<string, string>? columnModelModel = null, Paging? paging = null, ColumnName[]? order = null, string[]? excludedOrder = null, Func<Region, TDbItem, TOut>? transform = null, object? param = null, Func<Region, bool>? filter = null, bool skipDbPaging = false, CancellationToken ct = default(CancellationToken)) where TOut : class
    {
        string[] excludedOrder2 = excludedOrder;
        int total = 0;
        List<TOut> response = new List<TOut>();
        List<DbConnection> connections = new List<DbConnection>();
        StringBuilder stringBuilder = new StringBuilder(fetchDataQuery);
        if (order != null && excludedOrder2 != null && excludedOrder2.Length != 0)
        {
            order = order.Where((ColumnName o) => !excludedOrder2.Contains(o.Name)).ToArray();
        }

        if (order != null && order.Length != 0)
        {
            stringBuilder.Append(" ORDER BY");
            ColumnName[] array = order;
            foreach (ColumnName columnName in array)
            {
                string value = ((columnModelModel != null) ? columnModelModel[columnName.Name] : columnName.Name);
                stringBuilder.Append(' ').Append(value).Append(columnName.Asc ? " ASC," : " DESC,");
            }

            stringBuilder.Remove(stringBuilder.Length - 1, 1);
        }

        if (paging != null && !skipDbPaging)
        {
            stringBuilder.Append(" LIMIT ").Append((paging.Page + 1) * paging.PageSize).Append(';');
        }

        fetchDataQuery = stringBuilder.ToString();
        QTaskInfo<TDbItem>[] array2 = ParallelizeTask<TDbItem>(fetchDataQuery, countDataQuery, param, connections, await GetOnlineRegion(regionRepository, filter, ct));
        Task[] array3 = new Task[2 * array2.Length];
        for (int j = 0; j < array3.Length; j += 2)
        {
            int num = j / 2;
            array3[j] = array2[num].CountTask;
            array3[j + 1] = array2[num].DataTask;
        }

        Task.WaitAll(array3, ct);
        ValueTask[] array4 = CloseConnection(connections);
        for (int k = 0; k < array2.Length; k++)
        {
            var (region2, task3, task4) = array2[k];
            try
            {
                IEnumerable<TDbItem> result2 = task4.Result;
                int result3 = task3.Result;
                total += result3;
                foreach (TDbItem item in result2)
                {
                    TOut val = ((transform != null) ? transform(region2, item) : null) ?? (item as TOut);
                    if (val != null)
                    {
                        response.Add(val);
                    }
                }
            }
            catch (Exception exception)
            {
                regionRepository.SetStatus(region2, online: false);
                _logger.LogError(exception, "Error request region: {Region}", region2);
            }
        }

        IQueryable<TOut> result = response.AsQueryable().ApplySearch(paging, order);
        _logger.LogInformation("Dispose all connection created");
        ValueTask[] array5 = array4;
        for (int l = 0; l < array5.Length; l++)
        {
            ValueTask valueTask = array5[l];
            await valueTask;
        }

        TOut[] array6 = result.ToArray();
        return ((countDataQuery == null) ? array6.Length : total, array6);
    }

    public async Task<int> ExecuteAsync(IRegionRepository regionRepository, string sql, object? @params = null, Func<Region, bool>? filter = null, CancellationToken ct = default(CancellationToken))
    {
        string sql2 = sql;
        object params2 = @params;
        Region[] array = await GetOnlineRegion(regionRepository, filter, ct);
        List<DbConnection> connections = new List<DbConnection>();
        Region[] array2 = new Region[array.Length];
        Task<int>[] array3 = new Task<int>[array.Length];
        for (int i = 0; i < array3.Length; i++)
        {
            Region region = array[i];
            _logger.LogInformation("Creating connection for: {Region}", region);
            DbConnection connection = CreateConnection(region);
            Task<int> task = Task.Run(async delegate
            {
                try
                {
                    _logger.LogDebug("Creating connection for: {Region}", region);
                    return await connection.ExecuteAsync(sql2, params2);
                }
                catch (Exception exception2)
                {
                    _logger.LogWarning(exception2, "Error query {Region}", region);
                    return 0;
                }
            });
            array3[i] = task;
            array2[i] = region;
        }

        Task[] tasks = array3;
        Task.WaitAll(tasks, ct);
        ValueTask[] array4 = CloseConnection(connections);
        int total = 0;
        for (int j = 0; j < array3.Length; j++)
        {
            Task<int> task2 = array3[j];
            Region region2 = array2[j];
            try
            {
                int result = task2.Result;
                total += result;
            }
            catch (Exception exception)
            {
                regionRepository.SetStatus(region2, online: false);
                _logger.LogError(exception, "Error request region: {Region}", region2);
            }
        }

        _logger.LogInformation("Dispose all connection created");
        ValueTask[] array5 = array4;
        for (int k = 0; k < array5.Length; k++)
        {
            ValueTask valueTask = array5[k];
            await valueTask;
        }

        return total;
    }

    private static ValueTask[] CloseConnection(List<DbConnection> connections)
    {
        return connections.Select((DbConnection s) => s.DisposeAsync()).ToArray();
    }

    private string GetConnString(Region region)
    {
        return region switch
        {
            Region.Europe => _configuration.Europe,
            Region.Asia => _configuration.Asia,
            Region.NA => _configuration.NA,
            Region.Lab => _configuration.Lab,
            _ => throw new NotSupportedException("Region is not supported"),
        };
    }

    private static async Task<Region[]> GetOnlineRegion(IRegionRepository regionRepository, Func<Region, bool>? filter = null, CancellationToken ct = default(CancellationToken))
    {
        Func<Region, bool> filter2 = filter;
        return (from p in await regionRepository.GetOnlineAsync(ct)
                where p != Region.Lab
                where filter2?.Invoke(p) ?? true
                select p).ToArray();
    }

    private QTaskInfo<TDbItem>[] ParallelizeTask<TDbItem>(string fetchDataQuery, string? countDataQuery, object? param, List<DbConnection> connections, Region[] onlineRegions)
    {
        string fetchDataQuery2 = fetchDataQuery;
        object param2 = param;
        string countDataQuery2 = countDataQuery;
        QTaskInfo<TDbItem>[] array = new QTaskInfo<TDbItem>[onlineRegions.Length];
        for (int i = 0; i < array.Length; i++)
        {
            Region region = onlineRegions[i];
            _logger.LogInformation("Creating connection for: {Region}", region);
            DbConnection dataConnection = CreateConnection(region);
            if (!_connections.GetOrAdd(region, value: false))
            {
                lock (_connections)
                {
                    if (!_connections.GetValueOrDefault(region))
                    {
                        dataConnection.Open();
                        _connections[region] = true;
                    }
                }
            }

            Task<IEnumerable<TDbItem>> dataTask = Task.Run(async delegate
            {
                try
                {
                    _logger.LogDebug("Run Query {Query} with {Args}", fetchDataQuery2, param2);
                    return await dataConnection.QueryAsync<TDbItem>(fetchDataQuery2, param2);
                }
                catch (Exception exception2)
                {
                    _logger.LogWarning(exception2, "Error query {Region}", region);
                    return Array.Empty<TDbItem>();
                }
            });
            Task<int> countTask;
            if (!string.IsNullOrEmpty(countDataQuery2))
            {
                DbConnection countConnection = CreateConnection(region);
                countTask = Task.Run(async delegate
                {
                    try
                    {
                        _logger.LogDebug("Run Query {Query} with {Args}", countDataQuery2, param2);
                        return await countConnection.ExecuteScalarAsync<int>(countDataQuery2, param2);
                    }
                    catch (Exception exception)
                    {
                        _logger.LogWarning(exception, "Error query {Region}", region);
                        return 0;
                    }
                });
                connections.Add(countConnection);
            }
            else
            {
                countTask = Task.FromResult(0);
            }

            connections.Add(dataConnection);
            array[i] = new QTaskInfo<TDbItem>(region, countTask, dataTask);
        }

        return array;
    }
}
3
  • Your CreateConnection method seems to be creating a real DB connection, which is presumably used. If so, I’d say that it’s coming very close to integration range, even if it never reads/writes to the database.
    – jmoreno
    Commented Jul 10 at 23:41
  • Also, your actual test appears to be testing _client.SearchAsync, but you don’t show its declaration, leaving us to guess as to what it is. I would guess it’s a httpClient and it is a MVC controller. In which case you should be testing the controller directly, and not as an http endpoint.
    – jmoreno
    Commented Jul 10 at 23:57
  • Your assumptions are right @jmoreno, _client is an http client used to connect to the controller. I've add it to my answer, as well as the controller for clarification. Thanks for your input :) Commented Jul 11 at 7:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.