This post improves the usability the Achievement Quantifier project by adding a small feature - reporting - and showcases a complex LINQ query behind it.

The reporting command counts the number of completed achievements today, yesterday, and past week:

> aq stats
┌────────────────────────────────────┬───────┐
│ Statistic                          │ Value │
├────────────────────────────────────┼───────┤
│ Achievements Completed Today       │ 6     │
│ Achievements Completed Yesterday   │ 13    │
│ Achievements Completed Past 7 Days │ 42    │
└────────────────────────────────────┴───────┘

Access the project’s source code on GitHub 📁, check the initial state 0️⃣, explore the pull request ➡️, and view the final state 1️⃣.

Get the Classes Ready

The first commit defines the structure of the classes and provides dummy implementation. In particular, the Program.cs file configures all components, so the app has no runtime issues. Here is the output:

> dotnet run --project AQ.Console -- stats
1
2
3

There are two components: the ReportingService class to implement the query and return results in the SummaryStatisticsSnapshot class and the ShowStats command to display this information.

public sealed class ShowStats(
    IAnsiConsole console,
    IReportingService reportingService
) : AsyncCommand<EmptyCommandSettings>
{
    public override async Task<int> ExecuteAsync(CommandContext context, EmptyCommandSettings settings)
    {
        SummaryStatisticsSnapshot snapshot = await reportingService.GetSummaryStatisticsSnapshot();
        console.WriteLine(snapshot.TodayCompletionCount.ToString());
        console.WriteLine(snapshot.YesterdayCompletionCount.ToString());
        console.WriteLine(snapshot.PastSevenDaysCompletionCount.ToString());
        return 0;
    }
}

public class ReportingService : IReportingService
{
    public async Task<SummaryStatisticsSnapshot> GetSummaryStatisticsSnapshot()
    {
        SummaryStatisticsSnapshot result = new(1, 2, 3);
        return await Task.FromResult(result);
    }
}

public sealed record SummaryStatisticsSnapshot(
    int TodayCompletionCount,
    int YesterdayCompletionCount,
    int PastSevenDaysCompletionCount
);

This code organisation differs from the rest of the codebase. Elsewhere, commands directly implement queries without an intermediary service. However, testing queries in this manner requires checking the console output. While this approach is manageable for simple queries, the complexity of the reporting feature justifies the introduction of an intermediary service.

A few words on naming. By definition, a statistic is "any quantity computed from values in a sample which is considered for a statistical purpose". In this case, a sample is the past seven days of data. Different days lead to different samples. The statistical purpose is to summarise the data.

Three statistics are considered useful: today's completion count, yesterday's completion count, and the past seven days' completion count. Consequently, plural names are used for class and method names: ShowStats, GetSummaryStatisticsSnapshot, and SummaryStatisticsSnapshot. The "snapshot" suffix prevents plural class names, making it easier to name variables that hold a single SummaryStatisticsSnapshot object or an array of such objects.

Display Results in a Table

The second commit implements the command to display the summary statistics in a table plus a test case to ensure that the console output is exactly as expected:

public class ShowStatsTests : CommandTestsBase
{
    private readonly Mock<IReportingService> _reportingServiceStub = new();
    [Fact]
    public async Task TypicalCase()
    {
        // Arrange
        SummaryStatisticsSnapshot snapshot = new(5, 1, 30);
        _reportingServiceStub
            .Setup(x => x.GetSummaryStatisticsSnapshot())
            .ReturnsAsync(snapshot);
        ShowStats command = new(Console, _reportingServiceStub.Object);
        string expectedOutput = """
                                ┌────────────────────────────────────┬───────┐
                                │ Statistic                          │ Value │
                                ├────────────────────────────────────┼───────┤
                                │ Achievements Completed Today       │ 5     │
                                │ Achievements Completed Yesterday   │ 1     │
                                │ Achievements Completed Past 7 Days │ 30    │
                                └────────────────────────────────────┴───────┘

                                """;

        // Act
        int result = await command.ExecuteAsync(CommandContext, new EmptyCommandSettings());

        // Assert
        Assert.Equal(0, result);
        Assert.Equal(expectedOutput, Console.Output);
    }
}

The test stubs the reporting service to return the specified summary statistics snapshot. It also constructs the expected output for this snapshot, and then verifies that the expected and actual outputs are the same.

The test is brittle, in the sense that any change to the command is likely to fail the test. This is by design. The purpose of the test is to verify that the output is exactly as expected. When the command is modified, either the new output is copy-pasted to this test or the changes are reverted.

Implement the Query

The third commit and the fourth commit set up tests for the query, including the following edge cases: 1) days beyond the past seven days 2) days with no achievements 3) days with multiple achievements.

public static IEnumerable<object[]> GetQuantities()
{
	yield return [
		(int[][]) [[1], [2], [4], [8], [16], [32], [64]],
		new SummaryStatisticsSnapshot(64, 32, 127),
	];
	yield return [
		(int[][]) [[10000], [1, 1], [], [], [], [2], [], [1, 3, 1]],
		new SummaryStatisticsSnapshot(5, 0, 9),
	];
}

[Theory]
[MemberData(nameof(GetQuantities))]
public async Task ShouldGetCorrectStatistics(int[][] quantities, SummaryStatisticsSnapshot expected)
{
	// Arrange
	for (int i = 0; i < quantities.Length; i++)
	{
		_timeProvider.Advance(TimeSpan.FromDays(1));
		DateOnly date = DateOnly.FromDateTime(_timeProvider.GetUtcNow().Date);
		foreach (int quantity in quantities[i])
		{
			Achievement achievement = new()
			{
				CompletedDate = date,
				Quantity = quantity,
				AchievementClass = _achievementClass,
			};
			_dataContext.Achievements.Add(achievement);
			await _dataContext.SaveChangesAsync();
		}
	}
	
	// Act
	SummaryStatisticsSnapshot result = await _reportingService.GetSummaryStatisticsSnapshot();

	// Assert
	Assert.Equal(expected, result);
}

The test arrange phase represents a typical example of time-based testing: the fake time provider advances a day, new achievements are recorded, and the process repeats.

The test class inherits from DbTestsBase, which inherits from CommandTestsBase. In the past, all database tests were also command tests, which is no longer the case. The problem is noted, acknowledged, and ignored, because it does not matter for the reporting feature.

The fifth commit implements the query:

public class ReportingService(
    DataContext context,
    TimeProvider timeProvider
    ) : IReportingService
{
    public async Task<SummaryStatisticsSnapshot> GetSummaryStatisticsSnapshot()
    {
        DateOnly today = DateOnly.FromDateTime(timeProvider.GetUtcNow().Date);
        DateOnly yesterday = today.AddDays(-1);
        DateOnly reportingDateStart = today.AddDays(-6);
        Dictionary<DateOnly, int> counts = await context.Achievements
            .Where(a => a.CompletedDate >= reportingDateStart)
            .GroupBy(a => a.CompletedDate)
            .ToDictionaryAsync(group => group.Key, group => group.Sum(a => a.Quantity));
        SummaryStatisticsSnapshot result = new(
            counts.GetValueOrDefault(today, 0), 
            counts.GetValueOrDefault(yesterday, 0), 
            counts.Values.Sum());
        return result;
    }
}

The implementation is correct because it passes all tests, but it is suboptimal because it queries all achievements before summarising them. This can be seen by examining the generated SQL:

SELECT "a"."CompletedDate", "a"."Id", "a"."AchievementClassId", "a"."Quantity"
FROM "Achievement" AS "a"
WHERE "a"."CompletedDate" >= @__reportingDateStart_0
ORDER BY "a"."CompletedDate"

This is fixed in the sixth commit by forcing the aggregation to be done at the database level:

Dictionary<DateOnly, int> counts = await context.Achievements
	.Where(a => a.CompletedDate >= reportingDateStart)
	.GroupBy(a => a.CompletedDate)
	.Select(group => new { Date = group.Key, Sum = group.Sum(a => a.Quantity) })
	.ToDictionaryAsync(group => group.Date, group => group.Sum);

Here is the resulting SQL, which now returns at most 7 rows:

SELECT "a"."CompletedDate" AS "Date", COALESCE(SUM("a"."Quantity"), 0) AS "Sum"
FROM "Achievement" AS "a"
WHERE "a"."CompletedDate" >= @__reportingDateStart_0
GROUP BY "a"."CompletedDate"

Conclusion

With the reporting feature providing a summary of recently completed achievements, the Achievement Quantifier project takes another small step towards becoming a useful app.