DEV Community: Nelson Ciofi

The best way to store and read relational data with Dapper

Nelson Ciofi — Mon, 03 Jul 2023 02:31:18 +0000

To start with, I need to make something very clear: I’m “lazy”. Knowing that, I need to reveal a secret; if there is one thing I don’t like to do is write query or commands in sql. Usually the structures are all beautiful and wonderful in my imagination, and when I need to put them in the code, with a lot of “wheres” and “joins”, I already feel dizzy. Because of that, I have been a very assiduous user of ChatGpt, which has helped me a lot by doing the boring part.
And what does all this have to do with this article? I started to explore different ways of storing and retrieving data from complex objects or relationships using C#, Dapper and Sql Server, and I will portray the very summarized summary of these experiences.

Objects and Relationships

To start these tests, we need some objects that have at least one relationship of one to many between them, after all, the goal of this study is to find the most efficient way to bring this relationship assembled at runtime. Currently in my day-to-day I work a lot with the financial part, so I suggest an account and its installments. Like this:

public class Account
{
    public Guid Id { get; set; }
    public string Description { get; set; } 
    public decimal TotalValue { get; set; }
    public List<Installment> Installments { get; set; } 
}

public class Installment
{
    public Guid Id { get; set; }
    public DateTime DueDate { get; set; }
    public decimal Value { get; set; }
    public Guid AccountId { get; set; }
}

It is very common that objects in this configuration are stored in a relational database each in its table with foreign keys serving as reference, creating the relationship. Here is the example of SQL that I used:

 CREATE TABLE Accounts
  (
     Id          UNIQUEIDENTIFIER PRIMARY KEY,
     Description NVARCHAR(255) NOT NULL,
     TotalValue  DECIMAL(18, 2) NOT NULL
  ); 

CREATE TABLE Installments
  (
     Id        UNIQUEIDENTIFIER PRIMARY KEY,
     DueDate   DATETIME NOT NULL,
     Value     DECIMAL(18, 2) NOT NULL,
     AccountId UNIQUEIDENTIFIER NOT NULL,
     FOREIGN KEY(AccountId) REFERENCES Accounts(Id)
  );

Most common approaches

The most common way I have come across to retrieve this type of information is using a single sql query with join and then performing a mapping, which in my case is from installments to their accounts.

public const string ClassicQuery = @"
    SELECT acc.*, i.*  FROM Accounts acc
    inner join Installments i ON i.AccountId = acc.Id";

public List<Account> GetAllAccounts()
{
    var lookup = new Dictionary<Guid, Account>();

    _ = sqlConnection.Query<Account, Installment, Account>(SqlConstants.ClassicQuery,
        (acc, ins) =>
        {
            if (!lookup.TryGetValue(acc.Id, out var accEntry))
            {
                accEntry = acc;
                accEntry.Installments ??= new List<Installment>();
                lookup.Add(acc.Id, accEntry);
            }

            accEntry.Installments.Add(ins);
            return accEntry;

        }, splitOn: "Id");

    return lookup.Values.AsList();
}

Brief explanations:

For those who are already used to how sql works, know that this type of query generates the cartesian product of the tables, so it is necessary to use a 'lookup' during the mapping. Try to do without it and you'll end up with duplicate accounts and one-to-one relationships.
I'm not absolutely sure, but I believe that the C# compiler is capable of realizing that a hardcoded string can be optimized as constant or static, but I preferred to make it explicit so there are no doubts left, and since we will have benchmark tests addition soon, I don't want string allocations to mess up the results.
The AsList() method comes from Dapper and considers that, if an enumerable is already implemented as a List<T>, it reuses that list instead of allocating another one, as would happen if using ToList().

Another very common way is to use the .Read<T>() command multiple times over the result of a .QueryMultiple(). Like this:

public const string MultipleQuery = @"
    SELECT * FROM Accounts;
    SELECT * FROM Installments;";

public List<Account> GetAllAccounts()
{
    var res = sqlConnection.QueryMultiple(SqlConstants.MultipleQuery);

    var acc = res.Read<Account>();
    var installments = res.Read<Installment>();

    foreach (var account in acc)
    {
        account.Installments = installments.Where(i => i.AccountId == account.Id).ToList();
    }

    return acc.AsList();
}

The code is very simple and straight forward, it doesn't have any magic. So far, I've never questioned the performance, usability, or readability of this, and in fact, in the real world, there's never been a requirement to try to improve anything along these lines. But since I'm 'lazy' I started to get sick of writing this kind of code several times.

What if...Json?

For those who have already ventured with non-relational databases, such as MongoDB or Azure Cosmos, you know that you can store this entire object as a big and beautiful json. And if we analyze it coldly, we can try to mix a little json in the relational database, why not, right? All mainstream engines, including Sql Server, already have guidelines for dealing with json in tables, be it listing, inserting, filtering, and several other common activities. In the code snippet below, I represent the one-to-many relationship in json and use a custom Dapper mapper to automatically read and write account installments in that format.

public const string CreateAccountsJson = @"
    CREATE TABLE AccountsJson(
        Id UNIQUEIDENTIFIER PRIMARY KEY,
        Description NVARCHAR(255) NOT NULL,
        TotalValue DECIMAL(18,2) NOT NULL,
        Installments NVARCHAR(MAX) NULL
    );";


public class AccountJson
{
    public Guid Id { get; set; }
    public string Description { get; set; } 
    public decimal TotalValue { get; set; }
    public List<InstallmentJson> Installments { get; set; } 
}

public struct InstallmentJson
{
    public DateTime DueDate { get; set; }
    public decimal Value { get; set; }
}


public class InstallmentJsonTypeMapper 
    : SqlMapper.TypeHandler<List<InstallmentJson>>
{
    public override List<InstallmentJson> Parse(object value)
    {
        if (value is null) return new List<InstallmentJson>();

        var json = value.ToString();

        if (string.IsNullOrWhiteSpace(json)) return new List<InstallmentJson>();

        var res = JsonSerializer.Deserialize<List<InstallmentJson>>(json);

        if (res is null) return new List<InstallmentJson>();

        return res;
    }

    public override void SetValue(IDbDataParameter parameter, 
                                  List<InstallmentJson> value)
    {
        parameter.Value = value is null ? null : JsonSerializer.Serialize(value);
    }
}

I do know it works, but is it worth? Can you use it in production and feel safe? Let's go for a benchmark, using BenchmarkDotNet. I seeded a local database with 10 accounts with 10 installments each. And the results were:

|     Method      | Accounts | Installments |   Mean    |  Gen0   |  Gen1  | Allocated  |
|-----------------|----------|--------------|----------:|--------:|-------:|-----------:|
|  ClassicQuery   |    10    |      1       | 153.5 us |  1.4648 |   -    |   9.38 KB  |
| MultipleQuery   |    10    |      1       | 135.7 us |  1.4648 |   -    |  10.16 KB  |
|    JsonQuery    |    10    |      1       | 146.0 us |  2.1973 |   -    |  14.51 KB  |
|  ClassicQuery   |    10    |      5       | 287.4 us |  3.9063 |   -    |  26.34 KB  |
| MultipleQuery   |    10    |      5       | 162.9 us |  2.9297 |   -    |  19.32 KB  |
|    JsonQuery    |    10    |      5       | 182.8 us |  5.1270 |   -    |  32.47 KB  |
|  ClassicQuery   |    10    |     10       | 376.6 us |  7.8125 |   -    |  48.88 KB  |
| MultipleQuery   |    10    |     10       | 204.1 us |  4.8828 |   -    |  31.19 KB  |
|    JsonQuery    |    10    |     10       | 214.3 us |  9.0332 | 0.2441 |  56.28 KB  |

Honestly, I was expecting something a lot worse for the json method. The memory allocation was higher than the others but the execution time matched QueryMultiple(). For me this is already becoming a good deal.
We can also stop considering the classical approach. When considering between allocation and time, she was the worst.

What if... data structures?

Did you notice a prank? The MultipleQuery test is using Linq and a very simplistic way to map the installments to its accounts. We could use a lookup trick similar to the one used in the classic approach. Look:

public List<Account> GetAllAccounts()
    {
        var res = sqlConnection.QueryMultiple(SqlConstants.MultipleQuery);

        var accounts = res.Read<Account>(buffered: true).AsList();
        var installments = res.Read<Installment>();

        var lookup = new Dictionary<Guid, int>();

        for (int i = 0; i < accounts.Count; i++)
        {
            lookup.Add(accounts[i].Id, i);
        }

        foreach (var installment in installments)
        {
            if (lookup.TryGetValue(installment.AccountId, out int i))
            {
                accounts[i].Installments.Add(installment);
            }
        }

        return accounts;
    }

If the data structures are really a thing, this version will be the best so far, so let's get to the tests.

|       Method       | Accounts | Installments |     Mean     |   Allocated  |
|:------------------:|:--------:|:------------:|------------:|------------:|
|   MultipleQuery    |     1    |      10      |    129.8 us |     6.18 KB  |
|     JsonQuery      |     1    |      10      |    121.9 us |     7.63 KB  |
|    MultipleLookup  |     1    |      10      |    121.8 us |     6.22 KB  |
|   MultipleQuery    |     1    |     100      |    188.2 us |     26.7 KB  |
|     JsonQuery      |     1    |     100      |    204.3 us |    48.57 KB  |
|    MultipleLookup  |     1    |     100      |    190.7 us |    26.74 KB  |
|   MultipleQuery    |    10    |      10      |    223.5 us |    31.19 KB  |
|     JsonQuery      |    10    |      10      |    236.4 us |    56.34 KB  |
|    MultipleLookup  |    10    |      10      |    197.2 us |    30.38 KB  |
|   MultipleQuery    |    10    |     100      |    720.0 us |   232.26 KB  |
|     JsonQuery      |    10    |     100      |  1,256.1 us |   465.83 KB  |
|    MultipleLookup  |    10    |     100      |    719.4 us |   231.43 KB  |
|   MultipleQuery    |   100    |      10      |  1,318.5 us |   276.86 KB  |
|     JsonQuery      |   100    |      10      |  1,244.9 us |   541.36 KB  |
|    MultipleLookup  |   100    |      10      |    751.1 us |   269.34 KB  |
|   MultipleQuery    |   100    |     100      | 13,786.4 us |  2386.42 KB  |
|     JsonQuery      |   100    |     100      | 12,036.7 us |  4635.98 KB  |
|    MultipleLookup  |   100    |     100      |  8,879.9 us |  2379.31 KB  |

To help read the results, we need to understand that, although numbers like a hundred installments per account is not a very realistic case, we can consider that in another context an object can have a hundred children in its hierarchy. And that's what makes the test more interesting.
In general, the MultipleLookup method shined, and that brings me a certain sadness, because I have to accept that I need to worry more about sql to guarantee a better performance.
But like all good 'lazy' people, I don't give up easily, I keep trying to always do as little as possible.

What if...SPANS?

It's been a while since Span<T> has been used to optimize routines someway, and what I'm going to propose here is a sinister approach that is totally unusual but can pay off. Our relational case study objects are represented by a collection, and therefore eligible to become a Span<T>.
The question that remains then is how to convert a set of installments into a Span of installments and save it in the database, and the answer to that may be in binary data, see:

public const string CreateAccountsSpan = @"
      CREATE TABLE AccountsSpan(
          Id UNIQUEIDENTIFIER PRIMARY KEY,
          Description NVARCHAR(255) NOT NULL,
          TotalValue DECIMAL(18,2) NOT NULL,
          Installments VARBINARY(MAX) NULL
      );";

public class InstallmentSpanTypeMapper 
    : SqlMapper.TypeHandler<List<InstallmentSpan>>
{
    public override List<InstallmentSpan> Parse(object value)
    {
        if (value is not byte[] bytes)
        {
            return new List<InstallmentSpan>();
        }

        var span = bytes.AsSpan();
        var structSpan = MemoryMarshal.Cast<byte, InstallmentSpan>(span);
        return  structSpan.ToArray().ToList();       
    }

    public override void SetValue(IDbDataParameter parameter,
                                  List<InstallmentSpan> value)
    {
        var s = CollectionsMarshal.AsSpan(value);
        Span<byte> span = MemoryMarshal.AsBytes(s);
        parameter.Value = span.ToArray();
    }
}

It's weird to say the least, but it works. If the collection of installments in the account were an Installment[] instead of a List<Installment> it would be possible to avoid the .ToArray().ToList(). There must be some way to convert directly to list, but I'm lazy, remember?
Shall we go to the tests?

|         Method | Accounts | Installments |        Mean |  Allocated |
|--------------- |--------- |------------- |------------:|-----------:|
|      JsonQuery |        1 |           10 |    127.2 us |    7.64 KB |
|      SpanQuery |        1 |           10 |    107.7 us |    3.55 KB |
| MultipleLookup |        1 |           10 |    124.9 us |    6.23 KB |
|      JsonQuery |        1 |          100 |    205.9 us |   48.57 KB |
|      SpanQuery |        1 |          100 |    113.9 us |      12 KB |
| MultipleLookup |        1 |          100 |    188.0 us |   26.73 KB |
|      JsonQuery |       10 |           10 |    211.9 us |    56.2 KB |
|      SpanQuery |       10 |           10 |    124.5 us |   15.34 KB |
| MultipleLookup |       10 |           10 |    197.9 us |   30.45 KB |
|      JsonQuery |       10 |          100 |  1,237.3 us |  465.74 KB |
|      SpanQuery |       10 |          100 |    192.0 us |   99.84 KB |
| MultipleLookup |       10 |          100 |    674.6 us |  231.42 KB |
|      JsonQuery |      100 |           10 |  1,207.0 us |  541.52 KB |
|      SpanQuery |      100 |           10 |    268.6 us |  132.97 KB |
| MultipleLookup |      100 |           10 |    747.2 us |  269.28 KB |
|      JsonQuery |      100 |          100 | 11,859.5 us | 4636.01 KB |
|      SpanQuery |      100 |          100 |  1,234.6 us |  976.76 KB |
| MultipleLookup |      100 |          100 |  9,424.9 us | 2379.24 KB |

Yes! It is very fast and allocates much less memory than the others, but it has a disadvantage, if someone needs to read the database through a tool like Sql Server Management Studio, he won't be able to see what is there and if he needs some filter, it also won't be possible (lazy thinking).

Conclusion

I really liked the Span<T> version because it simplifies the database query. On the other hand, I don't know to what extent it would be really useful in a real case scenario, with real objects that have many more properties or even with a deep nested hierarchy. It was worth investigating because now it's more of an open door. The version with json is practical, in more recent versions of Sql Server it is already possible to query and manipulate json directly in the database, so I think it is valid in cases where a very complex object needs to be stored and will always be used as a whole thing and there is no effort to add a NoSql database to the team stack. In the end, we can reflect on the fact that we don't normally write a new table and new repository in the code every hour, so my suggestion is to stick with QueryMultiple, apply a good data structure to each case when mapping the objects, and if you really need to optimize something, try this version with spans in binaries in the database and don't make up your mind before running a benchmark.

Want the code? Come here.

PS(s):

The test environment used a local database connection to minimize network time, and had these settings: BenchmarkDotNet=v0.13.5, OS=Windows 11 (10.0.22621.1848/22H2/2022Update/SunValley2) Intel Core i7-8700 CPU 3.20GHz (Coffee Lake), 1 CPU, 12 logical and 6 physical cores .NET SDK=7.0.300-preview.23122.5 [Host] : .NET 6.0.14 (6.0.1423.7309), X64 RyuJIT AVX2 DefaultJob : .NET 6.0.14 (6.0.1423.7309), X64 RyuJIT AVX2 Microsoft SQL Server 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64)
I'm not really lazy, the fictional character in the article is the lazy one. Dapper's performance cases have already been extensively tested, what I really wanted to show was this workaround with spans in the database.
It wasn't any AI that gave me this idea to use spans, I thought of it myself :)

A melhor forma de armazenar e recuperar dados relacionados com o Dapper

Nelson Ciofi — Mon, 03 Jul 2023 02:31:13 +0000

In english? Go

Pra começo de conversa, eu preciso deixar algo bem transparente: eu sou "lazy". E em inglês assim fica mais palatável do que a tradução, então é "lazy" mesmo. Sabendo disso, eu preciso revelar um segredo; se tem uma coisa que eu não gosto de fazer é escrever consulta ou comandos em sql. Normalmente as estruturas estão todas lindas e maravilhosas no meu imaginário, e quando eu preciso colocá-las no código, com um monte de "wheres" e "joins", já sinto vertigem. Por causa disso, eu tenho sido um usuário muito assíduo do ChatGpt, que tem me ajudado bastante fazendo a parte entediante.
E o que isso tudo tem a ver com esse artigo? Eu comecei a explorar formas diferentes de armazenar e recuperar dados de objetos ou relacionamentos complexos usando C#, Dapper e Sql Server, e eu vou retratar o resumo bem resumido dessas experiências.

Objetos e Relacionamentos

Para começar esses testes, precisamos de alguns objetos que possuam ao menos um relacionamento de um para muitos entre si, afinal, o objetivo desse estudo é descobrir a forma mais eficiente de trazer esse relacionamento montado em tempo de execução. Como atualmente no meu dia-a-dia eu trabalho muito com a parte financeira, sugiro uma conta e seus parcelamentos. Assim:

public class Account
{
    public Guid Id { get; set; }
    public string Description { get; set; } 
    public decimal TotalValue { get; set; }
    public List<Installment> Installments { get; set; } 
}

public class Installment
{
    public Guid Id { get; set; }
    public DateTime DueDate { get; set; }
    public decimal Value { get; set; }
    public Guid AccountId { get; set; }
}

É muito comum que objetos nessa configuração sejam armazenados num banco de dados relacional cada um em sua tabela com chaves estrangeiras servindo de referência, aka relacionamento. Segue o exemplo de SQL que usei:

 CREATE TABLE Accounts
  (
     Id          UNIQUEIDENTIFIER PRIMARY KEY,
     Description NVARCHAR(255) NOT NULL,
     TotalValue  DECIMAL(18, 2) NOT NULL
  ); 

CREATE TABLE Installments
  (
     Id        UNIQUEIDENTIFIER PRIMARY KEY,
     DueDate   DATETIME NOT NULL,
     Value     DECIMAL(18, 2) NOT NULL,
     AccountId UNIQUEIDENTIFIER NOT NULL,
     FOREIGN KEY(AccountId) REFERENCES Accounts(Id)
  );

Abordagens mais comuns

A forma mais comum na qual já me deparei para recuperar esse tipo de informação é usando uma única consulta sql com join e então realizar um mapeamento, que no meu caso é de parcelas para suas contas.

public const string ClassicQuery = @"
    SELECT acc.*, i.*  FROM Accounts acc
    inner join Installments i ON i.AccountId = acc.Id";

public List<Account> GetAllAccounts()
{
    var lookup = new Dictionary<Guid, Account>();

    _ = sqlConnection.Query<Account, Installment, Account>(SqlConstants.ClassicQuery,
        (acc, ins) =>
        {
            if (!lookup.TryGetValue(acc.Id, out var accEntry))
            {
                accEntry = acc;
                accEntry.Installments ??= new List<Installment>();
                lookup.Add(acc.Id, accEntry);
            }

            accEntry.Installments.Add(ins);
            return accEntry;

        }, splitOn: "Id");

    return lookup.Values.AsList();
}

Breves explicações: 1. Para quem já está habituado com o funcionamento do sql, sabe que esse tipo de consulta gera o produto cartesiano das tabelas, então se faz necessário o auxílio de uma tabela 'lookup' durante o mapeamento. Experimente fazer sem isso e você acabará com contas duplicadas e relacionamentos de um pra um. 2. Eu não tenho certeza absoluta, mas acredito que o compilador do C# é capaz de perceber que uma string hardcoded pode ser otimizada como constante ou estática, mas eu preferi já deixar explícito para não ter dúvidas, já que logo menos teremos testes de benchmark disso, e eu não quero que alocações de strings atrapalhem os resultados. 3. O método AsList() vem do Dapper e considera que, caso um enumerável já seja implementado como uma List<T>, ele reaproveita essa lista ao invés de alocar outra, como aconteceria se usasse o ToList().

Outra forma muito comum é usar múltiplas vezes o comando .Read<T>() em cima do resultado de um .QueryMultiple(). Assim:

public const string MultipleQuery = @"
    SELECT * FROM Accounts;
    SELECT * FROM Installments;";

public List<Account> GetAllAccounts()
{
    var res = sqlConnection.QueryMultiple(SqlConstants.MultipleQuery);

    var acc = res.Read<Account>();
    var installments = res.Read<Installment>();

    foreach (var account in acc)
    {
        account.Installments = installments.Where(i => i.AccountId == account.Id).ToList();
    }

    return acc.AsList();
}

O código é bem simples e direto, não tem nenhum mágica. Até o momento, eu nunca havia questionado a performance, usabilidade ou legibilidade disso e de fato, no mundo real, nunca houve um requisito para tentar aprimorar nada nesse sentido. Mas como eu sou 'lazy' eu comecei a enjoar de escrever esse tipo de código várias vezes.

E se...Json?

Pra quem já se aventurou em bancos de dados não-relacionais, como MongoDb ou Azure Cosmos, sabe que dá pra armazenar esse objeto todo como um grande e magnífico json. E se a gente analisar friamente, podemos tentar misturar um pouco de json no banco relacional, porque não, né? Todas as engines mainstream, incluindo o Sql Server, já possuem diretrizes para lidar com json nas tabelas, seja listar, inserir, filtrar, e várias outras atividades comuns em bancos de dados. No trecho de código abaixo, represento o relacionamento de um para muitos em forma de json e uso um recurso de mapeamento personalizado do Dapper para ler e gravar automaticamente os parcelamentos das contas nesse formato.

public const string CreateAccountsJson = @"
    CREATE TABLE AccountsJson(
        Id UNIQUEIDENTIFIER PRIMARY KEY,
        Description NVARCHAR(255) NOT NULL,
        TotalValue DECIMAL(18,2) NOT NULL,
        Installments NVARCHAR(MAX) NULL
    );";


public class AccountJson
{
    public Guid Id { get; set; }
    public string Description { get; set; } 
    public decimal TotalValue { get; set; }
    public List<InstallmentJson> Installments { get; set; } 
}

public struct InstallmentJson
{
    public DateTime DueDate { get; set; }
    public decimal Value { get; set; }
}


public class InstallmentJsonTypeMapper 
    : SqlMapper.TypeHandler<List<InstallmentJson>>
{
    public override List<InstallmentJson> Parse(object value)
    {
        if (value is null) return new List<InstallmentJson>();

        var json = value.ToString();

        if (string.IsNullOrWhiteSpace(json)) return new List<InstallmentJson>();

        var res = JsonSerializer.Deserialize<List<InstallmentJson>>(json);

        if (res is null) return new List<InstallmentJson>();

        return res;
    }

    public override void SetValue(IDbDataParameter parameter, 
                                  List<InstallmentJson> value)
    {
        parameter.Value = value is null ? null : JsonSerializer.Serialize(value);
    }
}

Funcionar eu sei que funciona, mas será que presta? Será que dá pra usar em produção e se sentir seguro? Vamos para um benchmark, usando BenchmarkDotNet. Populei um banco de dados local com 10 contas com 10 parcelamentos cada. E os resultados foram:

|     Method      | Accounts | Installments |   Mean    |  Gen0   |  Gen1  | Allocated  |
|-----------------|----------|--------------|----------:|--------:|-------:|-----------:|
|  ClassicQuery   |    10    |      1       | 153.5 us |  1.4648 |   -    |   9.38 KB  |
| MultipleQuery   |    10    |      1       | 135.7 us |  1.4648 |   -    |  10.16 KB  |
|    JsonQuery    |    10    |      1       | 146.0 us |  2.1973 |   -    |  14.51 KB  |
|  ClassicQuery   |    10    |      5       | 287.4 us |  3.9063 |   -    |  26.34 KB  |
| MultipleQuery   |    10    |      5       | 162.9 us |  2.9297 |   -    |  19.32 KB  |
|    JsonQuery    |    10    |      5       | 182.8 us |  5.1270 |   -    |  32.47 KB  |
|  ClassicQuery   |    10    |     10       | 376.6 us |  7.8125 |   -    |  48.88 KB  |
| MultipleQuery   |    10    |     10       | 204.1 us |  4.8828 |   -    |  31.19 KB  |
|    JsonQuery    |    10    |     10       | 214.3 us |  9.0332 | 0.2441 |  56.28 KB  |

Sinceramente eu estava esperando algo bem pior para a versão que usa json. A alocação de memória ficou mais alta do que as demais mas o tempo de execução se equiparou ao QueryMultiple(). Para mim isso já está se tornando um bom negócio.
Também já podemos parar de considerar a abordagem clássica. Ponderando entre alocação e tempo, ela foi a pior.

E se...estrutura de dados?

Vocês notaram uma pegadinha? O teste MultipleQuery está usando Linq e uma forma bem simplista para mapear as parcelas das contas. Poderíamos usar um artíficio de lookup igual ao que foi usado na abordagem clássica. Veja:

public List<Account> GetAllAccounts()
    {
        var res = sqlConnection.QueryMultiple(SqlConstants.MultipleQuery);

        var accounts = res.Read<Account>(buffered: true).AsList();
        var installments = res.Read<Installment>();

        var lookup = new Dictionary<Guid, int>();

        for (int i = 0; i < accounts.Count; i++)
        {
            lookup.Add(accounts[i].Id, i);
        }

        foreach (var installment in installments)
        {
            if (lookup.TryGetValue(installment.AccountId, out int i))
            {
                accounts[i].Installments.Add(installment);
            }
        }

        return accounts;
    }

Se a fama das estruturas de dados fizerem jus, essa versão será a melhor de todas, então, vamos aos testes.

|       Method       | Accounts | Installments |     Mean     |   Allocated  |
|:------------------:|:--------:|:------------:|------------:|------------:|
|   MultipleQuery    |     1    |      10      |    129.8 us |     6.18 KB  |
|     JsonQuery      |     1    |      10      |    121.9 us |     7.63 KB  |
|    MultipleLookup  |     1    |      10      |    121.8 us |     6.22 KB  |
|   MultipleQuery    |     1    |     100      |    188.2 us |     26.7 KB  |
|     JsonQuery      |     1    |     100      |    204.3 us |    48.57 KB  |
|    MultipleLookup  |     1    |     100      |    190.7 us |    26.74 KB  |
|   MultipleQuery    |    10    |      10      |    223.5 us |    31.19 KB  |
|     JsonQuery      |    10    |      10      |    236.4 us |    56.34 KB  |
|    MultipleLookup  |    10    |      10      |    197.2 us |    30.38 KB  |
|   MultipleQuery    |    10    |     100      |    720.0 us |   232.26 KB  |
|     JsonQuery      |    10    |     100      |  1,256.1 us |   465.83 KB  |
|    MultipleLookup  |    10    |     100      |    719.4 us |   231.43 KB  |
|   MultipleQuery    |   100    |      10      |  1,318.5 us |   276.86 KB  |
|     JsonQuery      |   100    |      10      |  1,244.9 us |   541.36 KB  |
|    MultipleLookup  |   100    |      10      |    751.1 us |   269.34 KB  |
|   MultipleQuery    |   100    |     100      | 13,786.4 us |  2386.42 KB  |
|     JsonQuery      |   100    |     100      | 12,036.7 us |  4635.98 KB  |
|    MultipleLookup  |   100    |     100      |  8,879.9 us |  2379.31 KB  |

Para ajudar a ler os resultados, precisamos entender que, apesar de números como 100 parcelas por conta não seja um caso muito realista, podemos considerar que em outro contexto um objeto pode ter uma centena de filhos em sua hierarquia. E é isso que torna o teste mais interessante.
De modo geral, o método MultipleLookup brilhou, e isso me traz uma certa tristeza, por ter que aceitar que preciso me preocupar mais com sql para garantir uma performance agradável.
Mas como todo bom 'lazy', eu não desisto facilmente de tentar fazer sempre o menos possível.

E se...SPANS?

Já faz um bom tempo em que Span<T> é usado para otimizar rotinas de alguma forma, e o que eu vou propor aqui é uma abordagem sinistra totalmente incomum mas que pode render bons frutos. Os objetos relacionados do caso em estudo são representados por uma coleção, e portanto elegível para se tornar um Span<T>.
A dúvida que fica então é como converter um conjunto de parcelas em uma Span de parcelas e salvar no banco de dados, e a resposta para isso pode estar em dados binários, veja:

public const string CreateAccountsSpan = @"
      CREATE TABLE AccountsSpan(
          Id UNIQUEIDENTIFIER PRIMARY KEY,
          Description NVARCHAR(255) NOT NULL,
          TotalValue DECIMAL(18,2) NOT NULL,
          Installments VARBINARY(MAX) NULL
      );";

public class InstallmentSpanTypeMapper 
    : SqlMapper.TypeHandler<List<InstallmentSpan>>
{
    public override List<InstallmentSpan> Parse(object value)
    {
        if (value is not byte[] bytes)
        {
            return new List<InstallmentSpan>();
        }

        var span = bytes.AsSpan();
        var structSpan = MemoryMarshal.Cast<byte, InstallmentSpan>(span);
        return  structSpan.ToArray().ToList();       
    }

    public override void SetValue(IDbDataParameter parameter,
                                  List<InstallmentSpan> value)
    {
        var s = CollectionsMarshal.AsSpan(value);
        Span<byte> span = MemoryMarshal.AsBytes(s);
        parameter.Value = span.ToArray();
    }
}

É no mínimo estranho, mas funciona. Se a coleção de parcelas na conta fosse um Installment[] ao inves de List<Installment> seria possível evitar o .ToArray().ToList(). Deve haver alguma forma de converter direto para lista, mas fui 'lazy'.
Vamos aos testes?

|         Method | Accounts | Installments |        Mean |  Allocated |
|--------------- |--------- |------------- |------------:|-----------:|
|      JsonQuery |        1 |           10 |    127.2 us |    7.64 KB |
|      SpanQuery |        1 |           10 |    107.7 us |    3.55 KB |
| MultipleLookup |        1 |           10 |    124.9 us |    6.23 KB |
|      JsonQuery |        1 |          100 |    205.9 us |   48.57 KB |
|      SpanQuery |        1 |          100 |    113.9 us |      12 KB |
| MultipleLookup |        1 |          100 |    188.0 us |   26.73 KB |
|      JsonQuery |       10 |           10 |    211.9 us |    56.2 KB |
|      SpanQuery |       10 |           10 |    124.5 us |   15.34 KB |
| MultipleLookup |       10 |           10 |    197.9 us |   30.45 KB |
|      JsonQuery |       10 |          100 |  1,237.3 us |  465.74 KB |
|      SpanQuery |       10 |          100 |    192.0 us |   99.84 KB |
| MultipleLookup |       10 |          100 |    674.6 us |  231.42 KB |
|      JsonQuery |      100 |           10 |  1,207.0 us |  541.52 KB |
|      SpanQuery |      100 |           10 |    268.6 us |  132.97 KB |
| MultipleLookup |      100 |           10 |    747.2 us |  269.28 KB |
|      JsonQuery |      100 |          100 | 11,859.5 us | 4636.01 KB |
|      SpanQuery |      100 |          100 |  1,234.6 us |  976.76 KB |
| MultipleLookup |      100 |          100 |  9,424.9 us | 2379.24 KB |

Agora sim! É bem rápido e aloca muito menos memória que os demais, porém tem uma desvantagem, se alguém precisar ler o banco de dados através de alguma ferramenta como o Sql Server Management Studio, não vai conseguir ver o que tem lá e se precisar de algum filtro, também não será possível (eu só acho).

Conclusão

Eu gostei demais da versão com Span<T> pelo fato de simplificar a consulta ao banco de dados. Por outro lado não sei até que ponto ela seria realmente útil num cenário real, com objetos reais que possuem muitas outras propriedades ou ainda com uma hierarquia mais aninhada. Valeu a pena investigar porque agora é mais uma porta aberta. A versão com json é prática, em versões mais recentes do Sql Server já é possível consultar e manipular json diretamente no banco de dados, então acho válida em casos onde um objeto muito complexo precisa ser armazenado e sempre será usado por completo e não existe esforço para adicionar um banco de dados NoSql na stack da equipe. No final, podemos refletir a respeito do fato de que, normalmente, não escrevemos uma nova tabela e nem um novo repositório no código a cada hora, então, minha sugestão é ficar com o QueryMultiple, aplicar uma estrutura de dados boa para cada caso ao mapear os objetos, e se realmente precisar otimizar algo, experimente essa versão com spans em binários no banco de dados e não se decida antes de rodar um benchmark.

Quer o código? Vem aqui.

PS(s):

O ambiente de testes usou uma conexão com banco de dados local para reduzir ao máximo o tempo de rede, e estava com essas configurações: BenchmarkDotNet=v0.13.5, OS=Windows 11 (10.0.22621.1848/22H2/2022Update/SunValley2) Intel Core i7-8700 CPU 3.20GHz (Coffee Lake), 1 CPU, 12 logical and 6 physical cores .NET SDK=7.0.300-preview.23122.5 [Host] : .NET 6.0.14 (6.0.1423.7309), X64 RyuJIT AVX2 DefaultJob : .NET 6.0.14 (6.0.1423.7309), X64 RyuJIT AVX2 Microsoft SQL Server 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64)
Eu não sou lazy de verdade, quem é lazy é o personagem do artigo. Casos de performance do Dapper já foram amplamente testados, o que eu só queria mesmo era mostrar essa gambiarra com spans no banco de dados.
Não foi nenhuma IA que me deu essa ideia de usar spans, pensei nisso sozinho :)

Tirando de letra o princípio Open/Closed em C#

Nelson Ciofi — Sat, 06 May 2023 22:53:20 +0000

Desde que eu decidi seguir minha carreira em TI como desenvolvedor, eu tenho me deparado com inúmeros acrônimos mágicos voltados especificamente para regrar a forma com que eu escrevo meu código. E tem para todos os gostos, desde o começo do projeto até a concepção de uma pequena rotina, coisas como DDD, DRY, BDD, SOLID, YAGNI, KISS, TDD, GOF, OOP, etc.

OCP

Acontece que, durante toda a minha jornada profissional, e lá se foram uns 8 bons anos, uma dessas letrinhas sempre foi um desafio pra mim. Estou falando do O do SOLID, o princípio Aberto-Fechado (do inglês Open-Closed principle). SOLID é um acrônimo que reúne cinco princípios de design orientado a objetos, que visam melhorar a qualidade, a manutenibilidade e a extensibilidade do código. Alguns desses princípios já existem implementados implicitamente na linguagem de programação que eu uso, o C#, enquanto outros são menos óbvios de serem notados.

Recentemente eu tive uma iluminação e consegui perceber e aplicar um caso de uso para o OCP. Tudo começa pelo fato de que eu não gosto muito de seguir regras, mas só as de programação, principalmente porque a maioria delas vieram de um outro contexto, eventualmente totalmente diferente daquele em que o meu código está contido. Então, eu dou preferência a entender essas regras, princípios ou normas, incluindo entender as motivações que levaram cada uma delas a existir para só então aplicá-las no meu trabalho. No final, a tendência é que meu código acabe não parecendo o que uma pessoa costuma ver num tutorial do dia-a-dia no YouTube, mas atinge belamente todos os objetivos esperados.

A definição original do OCP, de Bertrand Meyer em 1988, é a seguinte:

Um módulo será dito aberto se ele ainda estiver disponível para extensão. Por exemplo, é possível adicionar campos aos dados registrados por um módulo ou novos elementos ao conjunto de funções que ele executa. Um módulo será dito fechado se ele estiver disponível para uso por outros módulos. Isso pressupõe que ao módulo tenha sido dada uma descrição bem definida e estável (interface) que não esteja sujeita a mudanças.

No meu contexto de hoje, com as metodologias ágeis, a velocidade com que uma mudança de regra de negócio ocorre e a forma com que os requisitos dos sistemas são gerados, costuma-se ser muito mais simples adicionar um campo em uma classe e aplicar um tratamento específico para ele nos momentos que se fazem necessários do que criar outra classe com herança ou composição. Então, pela definição, podemos considerar que essas classes podem até ter uma descrição bem definida, mas são bem instáveis. E você pode me perguntar: mas de onde é que saem essas mudanças? Uma hora é da cabeça de algum usuário, outrora é de um grupo de políticos que resolve mexer numa lei, tem de tudo. E é por isso que o OCP sempre foi um desafio pra mim, ele nunca fez muito sentido, até que...

Caos

Imagine um sistema de pagamentos que contenha uma lógica gigantesca para que os pagamentos sejam processados. Mas, em nome da simplicidade almejada no blog, teremos apenas as informações relevantes para o OCP. Esta abaixo é uma classe de contexto, um montinho de informações que podem ou não ser necessários para a nossa rotina.

public class PaymentContext
{
    public BankAccount BankAccount { get; set; }
    public PaymentType PaymentType { get; set; }
    public Bill[] Bills { get; set; }

    public PaymentContext(BankAccount bankAccount,
                          Bill[] bills,
                          PaymentType paymentType)
    {
        BankAccount = bankAccount;
        Bills = bills;
        PaymentType = paymentType;
    }
}

E agora o nosso processador de pagamentos. De novo, em nome da simplicidade, os métodos resultam apenas em um bool para atestar o sucesso da operação. Mas, na vida real, cada retorno desse requer lógicas ordens de magnitude maior que essa.


internal class PaymentProcessor : IPaymentProcessor
{
    public bool ProcessPayment(PaymentContext paymentContext)
    {
        switch (paymentContext.BankAccount.Bank)
        {
            case Bank.BankA:
                return ProcessPaymentForBankA(paymentContext);

            case Bank.BankB:
                return ProcessPaymentForBankB(paymentContext);

            default: return false;
        }
    }

    private static bool ProcessPaymentForBankA(PaymentContext paymentContext)
    {
        switch (paymentContext.PaymentType)
        {
            case PaymentType.InstantTransfer:
                return false;
            case PaymentType.SlowTransfer:
                return true;
            case PaymentType.Invoice:
                return true;
            case PaymentType.FastTransfer:
                return false;
            case PaymentType.Card:
                return true;

            default: return false;
        }
    }


    private static bool ProcessPaymentForBankB(PaymentContext paymentContext)
    {
        switch (paymentContext.PaymentType)
        {
            case PaymentType.InstantTransfer:
                return false;
            case PaymentType.SlowTransfer:
                return true;
            case PaymentType.Invoice:
                return true;
            case PaymentType.FastTransfer:
                return false;
            case PaymentType.Card:
                return true;

            default: return false;
        }
    }
}

Ao bater o olho nesse código já é possível notar algumas peculiaridades. A primeira delas é a quantidade imensa de possibilidades de processos, pois uma conta bancária pode pertencer a um de muitos bancos ao mesmo tempo em que o pagamento se refere a um tipo específico. A outra peculiaridade remete ao OCP: é complicado "fechar" essa classe se a qualquer momento algo novo pode surgir, como por exemplo surgiu o PIX no Brasil, sem que as implementações se tornem um caos na equipe. Esse é um dos casos em que não basta apenas adicionar um parâmetro e tudo ficará bem no final. Mas as soluções estão justamente na sopa de letrinhas dos acrônimos mencionados anteriormente adicionadas de algumas mágicas do C#.

Vamos à conversão de sem-OCP para full-OCP.

Responsabilidades

Partindo do princípio da responsabilidade única (S do SOLID), podemos definir que o nosso processador de pagamentos na verdade se trata de um tipo de direcionador ou seletor. Sua responsabilidade é descobrir qual banco e forma de pagamento do contexto em voga e enviá-lo para o processador correto. E assim temos que um processador de verdade é responsável por processar um pagamento de um banco com uma forma de pagamento específica. As interfaces abaixo refletem isso ao mesmo tempo que mantêm a interface original intocada a fim de evitar quebrar alguma outra parte que possa estar consumindo-a (e sim, eu sei que no exemplo aqui não tem outro consumidor, mas prefiro manter o garbo e elegância).

internal interface IPaymentProcessSelector
{
    IBankTypePaymentProcessor SelectPaymentProcessor(PaymentContext paymentContext);
}

internal interface IBankTypePaymentProcessor
{
    bool ProcessPayment(PaymentContext paymentContext);
}

Com elas, já podemos "fechar" o nosso processador de pagamentos, pois já temos o que é necessário para que ele cumpra seu papel.

internal sealed class PaymentProcessor : IPaymentProcessor
{
    private readonly IPaymentProcessSelector paymentProcessSelector;

    public PaymentProcessor(IPaymentProcessSelector paymentProcessSelector)
    {
        this.paymentProcessSelector = paymentProcessSelector;
    }

    public bool ProcessPayment(PaymentContext paymentContext)
    {
        return paymentProcessSelector.SelectPaymentProcessor(paymentContext)
                                     .ProcessPayment(paymentContext);        
    }
}

Notou o truque? Ele já nasce dependente de um IPaymentProcessSelector, que normalmente será injetado através de um container de injeção de dependências. Apesar do exemplo ainda não possuir uma implementação de DI, isso faz parte desta ideia de inclusão do OCP e já existe uma forma fácil de construir um desses nativamente no C#. Vamos continuar que chegaremos lá.

Separando o joio, do trigo, do joio, do trigo.

Agora na etapa em que falamos do protagonista da história, ele mesmo, o IPaymentProcessSelector. Sua responsabilidade é selecionar o processador de pagamentos correto para o contexto de dados que transita por ali.
Considerando o exemplo inicial, onde a seleção toda acontecia por meio de switches, se as lógicas implementadas fossem extremamente complexas para cada tipo de processamento, não bastaria isolar cada uma em seu próprio IBankTypePaymentProcessor, pois o problema continuaria nos switches, que continuariam exigindo a alteração direta para inclusão de novos processos.
A solução mais simples que me vem em mente seria encontrar uma forma de indexar todos os IBankTypePaymentProcessor e injetá-los na implementação do IPaymentProcessSelector de alguma forma que não fosse mais necessária a intervenção em nenhuma outra classe. Para chegarmos nessa solução, primeiro criamos uma estrutura que seja capaz de identificar um IBankTypePaymentProcessor, conforme abaixo:

internal readonly struct BankType
    : IEquatable<BankType>
{
    private readonly Bank bank;
    private readonly PaymentType type;

    public BankType(Bank bank,
                    PaymentType type)
    {
        this.bank = bank;
        this.type = type;
    }

    public override bool Equals(object? obj)
        => obj is BankType choice
           && Equals(choice);

    public bool Equals(BankType other)
    {
        return type == other.type &&
               bank == other.bank;
    }

    //GetHashCode() and other equality operators here
}

internal interface IBankTypePaymentProcessor
{
    BankType PaymentBankType { get; }
    bool ProcessPayment(PaymentContext paymentContext);
}

Agora que um IBankTypePaymentProcessor já pode ser identificado, vamos criar algumas implementações, assim:


internal class BankAInstantTransferPaymentProcessor
    : IBankTypePaymentProcessor
{
    public BankType PaymentBankType 
        => new(Bank.BankA, PaymentType.InstantTransfer);

    public bool ProcessPayment(PaymentContext paymentContext)
    {
        return true;
    }
}

internal class BankBSlowTransferPaymentProcessor
    : IBankTypePaymentProcessor
{
    public BankType PaymentBankType
        => new(Bank.BankB, PaymentType.SlowTransfer);

    public bool ProcessPayment(PaymentContext paymentContext)
    {
        return false;
    }
}

O próximo passo vai ficar a cargo do sistema de injeção de dependências do .NET, aquele que eu mencionei que já existe e iríamos usar. Há mais ou menos um mês, eu fiquei sabendo que é possível registrar múltiplas implementações para a mesma interface e usá-las injetadas como IEnumerable.

var services = new ServiceCollection();

services.AddSingleton<IPaymentProcessSelector, PaymentProcessSelector>();   
services.AddSingleton<IPaymentProcessor, PaymentProcessor>();

services.AddSingleton<IBankTypePaymentProcessor, BankAInstantTransferPaymentProcessor>();
services.AddSingleton<IBankTypePaymentProcessor, BankBSlowTransferPaymentProcessor>();

var serviceProvider = services.BuildServiceProvider();

internal sealed class PaymentProcessSelector : IPaymentProcessSelector
{
    private readonly Dictionary<BankType, IBankTypePaymentProcessor> bankTypePaymentProcessors = new();

    public PaymentProcessSelector(IEnumerable<IBankTypePaymentProcessor> bankTypePaymentProcessors)
    {
        foreach (var processor in bankTypePaymentProcessors)
        {
            if (!this.bankTypePaymentProcessors.TryAdd(processor.PaymentBankType, processor))
            {
                throw new ArgumentException($"There are more than one processor for {processor.PaymentBankType}.");
            }         
        }
    }

    public IBankTypePaymentProcessor SelectPaymentProcessor(PaymentContext paymentContext)
    {
        var bankType = new BankType(paymentContext.BankAccount.Bank, paymentContext.PaymentType);

        if (!bankTypePaymentProcessors.TryGetValue(bankType, out var processor))
        {
            throw new ArgumentException($"A payment processor for {bankType} is not available.");
        }

        return processor;
    }
}

Antes, eu não sabia que isso existia e daria certo, mas se você executar um código assim, no construtor do PaymentProcessSelector você receberá os dois IBankTypePaymentProcessor registrados na coleção de serviços. Isso é muito fantástico, pois acabamos de "fechar" mais uma classe já que o nosso seletor não precisa mais de modificações e cumpre sua missão com êxito.

Porém existe um contraponto, que pra mim é insignificante se a análise do sistema estiver estável. Caso você queira injetar IBankTypePaymentProcessor específico, isso não é possível, pois quando o IServiceProvider resolve a dependência, ele seleciona a última que foi registrada. No caso abaixo, por exemplo, receberíamos no construtor a BankBSlowTransferPaymentProcessor. Mas isso não me soa muito razoável, pois se você precisa de um processador específico, use o seletor, ele está lá pra isso.

internal class SingleDependencyBankType
{
    private readonly IBankTypePaymentProcessor bankTypePaymentProcessor;

    public SingleDependencyBankType(IBankTypePaymentProcessor bankTypePaymentProcessor)
    {
        this.bankTypePaymentProcessor = bankTypePaymentProcessor;
    }
}

O fino ajuste.

Se você chegou até aqui, deve ter percebido que ainda restou um "ponto aberto", ou seja, ainda existe no código um momento em que não é possível escapar da terrível alteração que fere o OCP. O ponto que me refiro é o registro das dependências na coleção de serviços. Da forma como está implementado, ainda é necessário que, após criar uma nova classe do tipo IBankTypePaymentProcessor, tal classe seja registrada manualmente. Então, depois de toda essa engenharia, tudo me parece voltar à estaca zero e a intervenção manual é obrigatória. Não é!

Usando reflection é possível encontrar todas as implementações de uma interface existentes num assembly, e depois, usando um outro método menos comum do IServiceCollection, registrar todas. Saca só:

var type = typeof(IBankTypePaymentProcessor);
var implementations = Assembly.GetExecutingAssembly().GetTypes()    
    .Where(p => type.IsAssignableFrom(p))
    .Where(p => !p.IsInterface);

foreach (var implementation in implementations)
{ 
    services.Add(new ServiceDescriptor(type, implementation, ServiceLifetime.Singleton));
}

Pronto! Mais uma rotina "fechada". A partir de agora, toda nova implementação de IBankTypePaymentProcessor já será automaticamente registrada e injetada. Talvez eu seja preguiçoso, mas eu adoro isso e acho que é muito prático para o dia-a-dia do programador.

Tem um repositório com o código todo. Veja AQUI.

Conclusão

Concluindo, eu sinto que o OCP é um princípio importante que deve ser seguido de forma inteligente visando a extensibilidade e a manutenibilidade do código. No entanto, em ambientes com regras de negócio muito voláteis, sempre foi difícil aplicá-lo de maneira eficiente, sendo mais simples realizar uma alteração rápida. Para esses casos, o uso bem definido do princípio de responsabilidade única pode ser uma alternativa mais adequada, pois se cada regra está isolada, alterá-la não é um risco, mas uma necessidade. Por fim, é importante salientar que cada situação é única e requer uma análise cuidadosa, não sigam esse post como regra.

PS.: Será que algum dia eu, como dev, serei substituído por uma IA? Pelo menos meu texto é mais legal que o do ChatGPT.
PS2.: Porquê eu fiz a implementação do IServiceProvider manualmente? Eu me acostumei a fazer isso em aplicações Windows Forms e Console onde não existe um container padrão implementado. No ASP.Net já tem, fuça lá.

Open/Closed principle like a boss in C#

Nelson Ciofi — Sat, 06 May 2023 22:53:17 +0000

Since I decided to follow my career in IT as a developer, I have come across countless magical acronyms specifically aimed at ruling the way I write my code. And there is something for everyone, from the beginning of the project to the conception of a small routine, things like DDD, DRY, BDD, SOLID, YAGNI, KISS, TDD, GOF, OOP, etc.

OCP

It turns out that, throughout my professional journey, and there have been about 8 good years, one of these letters has always been a challenge for me. I’m talking about the O of SOLID, the Open-Closed principle. SOLID is an acronym that brings together five principles of object-oriented design, which aim to improve the quality, maintainability and extensibility of the code. Some of these principles already exist implemented implicitly in the programming language that I use, C#, while others are less obvious to be noticed.

Recently I had an enlightenment and managed to perceive and apply a use case for OCP. It all starts with the fact that I don’t like to follow rules very much, but only the programming ones, mainly because most of them came from another context, eventually totally different from the one in which my code is contained. So, I prefer to understand these rules, principles or norms, including understanding the motivations that led each one of them to exist and only then apply them in my work. In the end, the tendency is that my code ends up not looking like what a person usually sees in a day-to-day tutorial on YouTube, but beautifully achieves all the expected objectives.

The original definition of OCP, by Bertrand Meyer in 1988, is as follows:

A module will be said to be open if it is still available for extension. For example, it should be possible to add fields to the data structures it contains, or new elements to the set of functions it performs. A module will be said to be closed if [it] is available for use by other modules. This assumes that the module has been given a well-defined, stable description (the interface in the sense of information hiding).

In my current context, with agile methodologies, the speed with which a business rule change occurs and the way system requirements are generated, it is usually much simpler to add a field in a class and apply a specific treatment for it in the moments that are necessary than to create another class with inheritance or composition. So, by definition, we can consider that these classes may even have a well-defined description, but they are very unstable. And you can ask me: but where do these changes come from? Sometimes it’s from the head of some user, sometimes it’s from a group of politicians who decide to mess with a law, there’s everything. And that’s why OCP has always been a challenge for me, it never made much sense, until…

Chaos

Imagine a payment system that contains a huge logic for payments to be processed. But, in the name of simplicity sought in the blog, we will only have the information relevant to OCP. This below is a context class, a bunch of information that may or may not be necessary for our routine.

public class PaymentContext
{
    public BankAccount BankAccount { get; set; }
    public PaymentType PaymentType { get; set; }
    public Bill[] Bills { get; set; }

    public PaymentContext(BankAccount bankAccount,
                          Bill[] bills,
                          PaymentType paymentType)
    {
        BankAccount = bankAccount;
        Bills = bills;
        PaymentType = paymentType;
    }
}

And now our payment processor. Again, for the sake of simplicity, the methods result in only a bool to attest to the success of the operation. But, in real life, each of these returns requires logic orders of magnitude greater than this.


internal class PaymentProcessor : IPaymentProcessor
{
    public bool ProcessPayment(PaymentContext paymentContext)
    {
        switch (paymentContext.BankAccount.Bank)
        {
            case Bank.BankA:
                return ProcessPaymentForBankA(paymentContext);

            case Bank.BankB:
                return ProcessPaymentForBankB(paymentContext);

            default: return false;
        }
    }

    private static bool ProcessPaymentForBankA(PaymentContext paymentContext)
    {
        switch (paymentContext.PaymentType)
        {
            case PaymentType.InstantTransfer:
                return false;
            case PaymentType.SlowTransfer:
                return true;
            case PaymentType.Invoice:
                return true;
            case PaymentType.FastTransfer:
                return false;
            case PaymentType.Card:
                return true;

            default: return false;
        }
    }


    private static bool ProcessPaymentForBankB(PaymentContext paymentContext)
    {
        switch (paymentContext.PaymentType)
        {
            case PaymentType.InstantTransfer:
                return false;
            case PaymentType.SlowTransfer:
                return true;
            case PaymentType.Invoice:
                return true;
            case PaymentType.FastTransfer:
                return false;
            case PaymentType.Card:
                return true;

            default: return false;
        }
    }
}

By looking at this code it is possible to notice some peculiarities. The first one is the immense amount of possibilities of processes, since a bank account can belong to one of many banks at the same time that the payment refers to a specific type. The other peculiarity refers to OCP: it is complicated to “close” this class if at any moment something new can appear, such as PIX appeared in Brazil, without the implementations becoming a chaos in the team. This is one of the cases where it is not enough to just add a parameter and everything will be fine in the end. But the solutions are precisely in the soup of letters of the acronyms mentioned earlier added with some magic of C#.

Let’s go to the conversion from non-OCP to full-OCP.

Responsabilities

Starting from the principle of single responsibility (S of SOLID), we can define that our payment processor is actually a type of director or selector. Its responsibility is to find out which bank and form of payment of the context in vogue and send it to the correct processor. And so we have that a real processor is responsible for processing a payment from a bank with a specific form of payment. The interfaces below reflect this while keeping the original interface untouched in order to avoid breaking any other part that may be consuming it (and yes, I know that in the example here there is no other consumer, but I prefer to keep the garbo and elegance).

internal interface IPaymentProcessSelector
{
    IBankTypePaymentProcessor SelectPaymentProcessor(PaymentContext paymentContext);
}

internal interface IBankTypePaymentProcessor
{
    bool ProcessPayment(PaymentContext paymentContext);
}

With them, we can already “close” our payment processor, because we already have what is necessary for it to fulfill its role.

internal sealed class PaymentProcessor : IPaymentProcessor
{
    private readonly IPaymentProcessSelector paymentProcessSelector;

    public PaymentProcessor(IPaymentProcessSelector paymentProcessSelector)
    {
        this.paymentProcessSelector = paymentProcessSelector;
    }

    public bool ProcessPayment(PaymentContext paymentContext)
    {
        return paymentProcessSelector.SelectPaymentProcessor(paymentContext)
                                     .ProcessPayment(paymentContext);        
    }
}

Did you notice the trick? It is already born dependent on an IPaymentProcessSelector, which will normally be injected through a dependency injection container. Although the example still does not have a DI implementation, this is part of this idea of including OCP and there is already an easy way to build one of these natively in C#. Let’s continue and we’ll get there.

Separating the wheat from the chaff, from the wheat, from the chaff.

And here comes the step where we talk about the protagonist of the story, himself, the IPaymentProcessSelector. Its responsibility is to select the correct payment processor for the context of data that transits through there.
Considering the initial example, where the selection was done by switches, if the implemented logics were extremely complex for each type of processing, it would not be enough to isolate each one in its own IBankTypePaymentProcessor, because the problem would continue in the switches, which would continue to require direct change to include new processes. The simplest solution that comes to my mind would be to find a way to index all IBankTypePaymentProcessors and inject them into the IPaymentProcessSelector implementation in such a way that no intervention in any other class was necessary. To get to this solution, we first create a structure that is capable of identifying an IBankTypePaymentProcessor, as follows:

internal readonly struct BankType
    : IEquatable<BankType>
{
    private readonly Bank bank;
    private readonly PaymentType type;

    public BankType(Bank bank,
                    PaymentType type)
    {
        this.bank = bank;
        this.type = type;
    }

    public override bool Equals(object? obj)
        => obj is BankType choice
           && Equals(choice);

    public bool Equals(BankType other)
    {
        return type == other.type &&
               bank == other.bank;
    }

    //GetHashCode() and other equality operators here
}

internal interface IBankTypePaymentProcessor
{
    BankType PaymentBankType { get; }
    bool ProcessPayment(PaymentContext paymentContext);
}

Now that an IBankTypePaymentProcessor can already be identified, let’s create some implementations, like this:


internal class BankAInstantTransferPaymentProcessor
    : IBankTypePaymentProcessor
{
    public BankType PaymentBankType 
        => new(Bank.BankA, PaymentType.InstantTransfer);

    public bool ProcessPayment(PaymentContext paymentContext)
    {
        return true;
    }
}

internal class BankBSlowTransferPaymentProcessor
    : IBankTypePaymentProcessor
{
    public BankType PaymentBankType
        => new(Bank.BankB, PaymentType.SlowTransfer);

    public bool ProcessPayment(PaymentContext paymentContext)
    {
        return false;
    }
}

The next step will be in charge of the .NET dependency injection system, the one I mentioned that already exists and we would use. About a month ago, I found out that it is possible to register multiple implementations for the same interface and use them injected as IEnumerable.

var services = new ServiceCollection();

services.AddSingleton<IPaymentProcessSelector, PaymentProcessSelector>();   
services.AddSingleton<IPaymentProcessor, PaymentProcessor>();

services.AddSingleton<IBankTypePaymentProcessor, BankAInstantTransferPaymentProcessor>();
services.AddSingleton<IBankTypePaymentProcessor, BankBSlowTransferPaymentProcessor>();

var serviceProvider = services.BuildServiceProvider();

internal sealed class PaymentProcessSelector : IPaymentProcessSelector
{
    private readonly Dictionary<BankType, IBankTypePaymentProcessor> bankTypePaymentProcessors = new();

    public PaymentProcessSelector(IEnumerable<IBankTypePaymentProcessor> bankTypePaymentProcessors)
    {
        foreach (var processor in bankTypePaymentProcessors)
        {
            if (!this.bankTypePaymentProcessors.TryAdd(processor.PaymentBankType, processor))
            {
                throw new ArgumentException($"There are more than one processor for {processor.PaymentBankType}.");
            }         
        }
    }

    public IBankTypePaymentProcessor SelectPaymentProcessor(PaymentContext paymentContext)
    {
        var bankType = new BankType(paymentContext.BankAccount.Bank, paymentContext.PaymentType);

        if (!bankTypePaymentProcessors.TryGetValue(bankType, out var processor))
        {
            throw new ArgumentException($"A payment processor for {bankType} is not available.");
        }

        return processor;
    }
}

Before, I didn’t know that this existed and would work, but if you run a code like this, in the constructor of PaymentProcessSelector you will receive the two IBankTypePaymentProcessor registered in the service collection. This is very fantastic, because we just “closed” one more class since our selector no longer needs modifications and fulfills its mission successfully.

But there is a counterpoint, which for me is insignificant if the system analysis is stable. If you want to inject a specific IBankTypePaymentProcessor, that is not possible, because when the IServiceProvider resolves the dependency, it selects the last one that was registered. In the case below, for example, we would receive in the constructor the BankBSlowTransferPaymentProcessor. But this doesn’t sound very reasonable to me, because if you need a specific processor, use the selector, it’s there for that.

internal class SingleDependencyBankType
{
    private readonly IBankTypePaymentProcessor bankTypePaymentProcessor;

    public SingleDependencyBankType(IBankTypePaymentProcessor bankTypePaymentProcessor)
    {
        this.bankTypePaymentProcessor = bankTypePaymentProcessor;
    }
}

The fine tunning.

If you got this far, you must have realized that there was still an “open point”, that is, there is still a moment in the code where it is not possible to escape the terrible change that violates OCP. The point I’m referring to is the registration of dependencies in the service collection. The way it is implemented, it is still necessary that, after creating a new class of type IBankTypePaymentProcessor, such class be registered manually. So, after all this engineering, everything seems to me to go back to square one and manual intervention is mandatory. It’s not!

Using reflection it is possible to find all implementations of an interface existing in an assembly, and then, using another less common method of IServiceCollection, register them all. Check it out:

var type = typeof(IBankTypePaymentProcessor);
var implementations = Assembly.GetExecutingAssembly().GetTypes()    
    .Where(p => type.IsAssignableFrom(p))
    .Where(p => !p.IsInterface);

foreach (var implementation in implementations)
{ 
    services.Add(new ServiceDescriptor(type, implementation, ServiceLifetime.Singleton));
}

Done! One more routine “closed”. From now on, every new implementation of IBankTypePaymentProcessor will be automatically registered and injected. Maybe I’m lazy, but I love this and I think it’s very practical for the programmer’s day-to-day.

There is a repository with all the code. See it HERE.

Conclusion

In conclusion, I feel that OCP is an important principle that should be followed aiming at the extensibility and maintainability of the code. However, in environments with very volatile business rules, it has always been difficult to apply it efficiently, being simpler to make a small quick change. For these cases, the well-defined use of the single responsibility principle can be a more suitable alternative, because if each rule is isolated, changing it is not a risk, but a necessity. Finally, it is important to stress that each situation is unique and requires careful analysis, do not follow this post as a rule.

PS.: Will I ever be replaced by an AI as a dev? At least my text is cooler than ChatGPT’s.
PS2.: Why did I implement the IServiceProvider manually? I got used to doing this in Windows Forms and Console applications where there is no standard container implemented. In ASP.Net there is, look there.
PS3.: This post was translated from my own native language post with the help of AI. Original is here.

Standardizing a Value Object representation in C#

Nelson Ciofi — Sat, 15 Apr 2023 16:51:08 +0000

I. Introduction

Have you ever struggled with inconsistent storage and retrieval of domain value objects in your code? As a seasoned developer, I know firsthand how frustrating and time-consuming it can be to juggle different representations of the same data.

Recently, I encountered this problem with a specific value object called Competence Month, and I developed a simple and elegant solution to standardize its storage and retrieval. In this post, I'll share with you my step-by-step implementation process using C# code, and highlight the benefits of standardizing data types and ensuring consistency throughout your system. Whether you're a beginner or an experienced developer, this post will provide you with practical tips and best practices for handling data consistency in your own software projects.

II. The Problem

The principle of competence is a fundamental accounting principle that ensures that revenues and expenses are recognized in financial statements in the period in which they are earned or incurred, regardless of when payment is received or made. This is important for accurate financial reporting and to provide a clear picture of a company's financial health.

In the context of software development, Competence is implemented as a domain value object that encapsulates a month and year combination. However, this simple concept can become quite complex when it comes to storing and retrieving this information. Different representations of Competence, such as a DateTime object with day one of the given month, a string in the format "MM/yyyy", or a full datetime as nvarchar “yyyy-MM-dd hh:mm:ss”, can be used interchangeably, leading to confusion, errors, and time-consuming conversion. Additionally, inexperienced or "tourist" developers may introduce their own representations, further complicating the situation.

Look at the code below, as it illustrate a situation with the given issues:

internal class AccountsReceivable
{
    public AccountsReceivable(string competence, decimal value)
    {
        if (string.IsNullOrWhiteSpace(competence))
        {
            throw new ArgumentException($"'{nameof(competence)}' cannot be null or whitespace.", nameof(competence));
        }

        competence = "01" + competence;

        if (!DateTime.TryParse(competence, out DateTime date))
        {
            throw new ArgumentException("Invalid format.", nameof(competence));
        }

        Competence = date;
        Value = value;
    }

    public decimal Value { get; set; }
    public DateTime Competence { get; }
}

internal class PaymentOrder
{
    public PaymentOrder(string competence, DateTime dueDate, AccountsReceivable[] accountsReceivables)
    {
        if (string.IsNullOrWhiteSpace(competence)) throw new ArgumentNullException(nameof(competence));
        if (dueDate < DateTime.Today) throw new ArgumentOutOfRangeException(nameof(dueDate));
        if (!accountsReceivables.Any()) throw new ArgumentException("Payment order must have at least one account receivable.", nameof(accountsReceivables));

        Competence = competence;
        AccountsReceivables = accountsReceivables;
        DueDate = dueDate;
        Value = accountsReceivables.Sum(a => a.Value);
    }

    public DateTime DueDate { get; }
    public decimal Value { get; }
    public string Competence { get; }

    public bool Paid { get; set; }

    public AccountsReceivable[] AccountsReceivables { get; }
}


internal class PaymentServiceWithIssues
{
    public void PayOrder()
    {
        var service1AccountReceivable = new AccountsReceivable("04/2023", 10);
        var service2AccountReceivable = new AccountsReceivable("04/2023", 15);

        var servicesPaymentOrder = new PaymentOrder("04/2023",
                                                    new DateTime(2023, 04, 15),
                                                    new AccountsReceivable[] {
                                                        service1AccountReceivable,
                                                        service2AccountReceivable
                                                    });

        var paymentCompetence = DateTime.Parse(servicesPaymentOrder.Competence);

        if (service1AccountReceivable.Competence.Month != paymentCompetence.Month ||
           service2AccountReceivable.Competence.Month != paymentCompetence.Month)
        {
            throw new InvalidOperationException("Payment can only happen in the same competence month of accounts receivable.");
        }

        //Some payment logic here.

        servicesPaymentOrder.Paid = true;

        Console.WriteLine("The order was paid successfully!");
    }
}

III. Solution Overview

Our solution involves creating a new struct called CompetenceMonth, which handles the different scenarios in which the month competence information can appear. The struct will include several properties and methods that allow us to convert the competence to different formats, perform arithmetic operations with competences, and compare competences with each other in a very simplistic way.

Here is the code for the CompetenceMonth struct:


public readonly struct CompetenceMonth : IEquatable<CompetenceMonth>
{
    private readonly int year;
    private readonly int month;

    public CompetenceMonth(int month, int year)
    {
        if (year <= 0)
        {
            throw new ArgumentOutOfRangeException(nameof(year));
        }

        if (month <= 0 || month > 12)
        {
            throw new ArgumentOutOfRangeException(nameof(month));
        }

        this.year = year;
        this.month = month;
    }

    public CompetenceMonth(DateTime dt)
    {
        year = dt.Year;
        month = dt.Month;
    }

    public CompetenceMonth(string txt)
    {
        if (string.IsNullOrWhiteSpace(txt))
        {
            throw new ArgumentNullException(nameof(txt));
        }

        var txtDt = "01" + txt;

        if (!DateTime.TryParse(txtDt, out var dt))
        {
            throw new ArgumentException("Invalid format", nameof(txt));
        }

        this.year = dt.Year;
        this.month = dt.Month;
    }

    public int Year => year;
    public int Month => month;

    public string ToCompetenceText() => $"{month:D2}/{year}";

    public override bool Equals(object? obj) 
        => obj is CompetenceMonth month && Equals(month);

    public bool Equals(CompetenceMonth other) 
        => year == other.year && month == other.month;

    public override int GetHashCode() 
        => HashCode.Combine(year, month);

    public override string ToString() 
        => ToCompetenceText();


    public static bool operator ==(CompetenceMonth left, 
                                   CompetenceMonth right) 
        => left.Equals(right);

    public static bool operator !=(CompetenceMonth left, 
                                   CompetenceMonth right)
        => !(left == right);
}

While the CompetenceMonth struct provides a solid foundation for representing and manipulating month competences, it is currently lacking some important functionality. For example, if we want to use the CompetenceMonth struct with data that is stored in a database, we will need to be able to convert it to and from various formats such as nvarchar and datetime.

At the moment, the CompetenceMonth struct does not provide any methods for such conversions, which means that we would need to write custom conversion logic every time we want to work with month competences in a different format. This is not ideal, as it can be time-consuming and error-prone.

To address this issue, we will need to add some additional functionality to it.

public readonly struct CompetenceMonth : IEquatable<CompetenceMonth>
{
    private readonly int year;
    private readonly int month;

    public CompetenceMonth(int month, int year)
    {
        if (year <= 0)
        {
            throw new ArgumentOutOfRangeException(nameof(year));
        }

        if (month <= 0)
        {
            throw new ArgumentOutOfRangeException(nameof(month));
        }

        this.year = year;
        this.month = month;
    }

    public CompetenceMonth(int monthCount)
    {
        if (monthCount <= 0)
        {
            throw new ArgumentOutOfRangeException(nameof(monthCount));
        }

        CalculateCompetenceFromMonthCount(monthCount, out int month, out int year);

        this.year = year;
        this.month = month;
    }

    public CompetenceMonth(string txt)
    {
        if (string.IsNullOrWhiteSpace(txt))
        {
            throw new ArgumentNullException(nameof(txt));
        }

        ConvertCompetenceFromText(txt, out int month, out int year);

        this.year = year;
        this.month = month;
    }

    public CompetenceMonth(DateTime dt)
    {
        year = dt.Year;
        month = dt.Month;
    }

    public int Year => year;
    public int Month => month;


    public int ToMonthCount() => (year * 12) + month;
    public DateTime ToDateTime() => new(year, month, 1);
    public string ToCompetenceText() => $"{month:D2}/{year}";

    //IEquatable and other equality comparers here.

    private static void CalculateCompetenceFromMonthCount(int monthCount, out int month, out int year)
    {
        year = monthCount / 12;
        month = monthCount % 12;

        while (month < 1)
        {
            month += 12;
            year--;
        }

        while (month > 12)
        {
            month -= 12;
            year++;
        }
    }

    private static readonly char[] validChars = new char[] { '/', '-' };

    private static void ConvertCompetenceFromText(string txt, out int month, out int year)
    {
        var index = txt.IndexOfAny(validChars);

        if (index < 0)
        {
            throw new ArgumentException("Invalid format.");
        }

        var txtSpn = txt.AsSpan();

        var p1 = txtSpn[..index];
        var p2 = txtSpn[(index + 1)..];

        if (p1.Length == 2)
        {
            if (!int.TryParse(p1, out month))
            {
                throw new InvalidOperationException();
            }

            if (!int.TryParse(p2, out year))
            {
                throw new InvalidOperationException();
            }
        }
        else if (p1.Length == 4)
        {
            if (!int.TryParse(p2, out month))
            {
                throw new InvalidOperationException();
            }

            if (!int.TryParse(p1, out year))
            {
                throw new InvalidOperationException();
            }
        }
        else
        {
            throw new ArgumentException("Invalid format.");
        }
    }
}

The implementation of the conversion methods and constructors allow for the conversion of a CompetenceMonth instance to other formats such as month count, DateTime, and text. These methods are very useful in scenarios where the CompetenceMonth instance needs to be stored or displayed in a different format.

However, the current solution may still lack practical conversions to work with other objects. To overcome this limitation, we can take advantage of a C# feature called implicit operator overrides.

Implicit operator overrides allow us to define implicit conversions between types, meaning that we can convert an instance of one type to another type without explicitly calling a conversion method. By defining these implicit operator overrides, we can simplify the conversion process and make it more intuitive. For example, instead of calling a conversion method like ToDateTime() to convert a CompetenceMonth instance to a DateTime, we can simply assign the CompetenceMonth instance to a DateTime variable, and the implicit operator override will handle the conversion automatically.

In summary, implicit operator overrides are a powerful feature in C# that allow for intuitive and easy conversion between types. By leveraging this feature in the CompetenceMonth struct, we can add more practical conversions to database formats such as nvarchar and datetime, making the struct even more versatile and useful.

public readonly struct CompetenceMonth : IEquatable<CompetenceMonth>
{
    private readonly int year;
    private readonly int month;

    //Constructors here

    //Conversion methods here

    //IEquatable and other equality comparers here.

    public static implicit operator int(CompetenceMonth competenceMonth)
       => competenceMonth.ToMonthCount();

    public static implicit operator CompetenceMonth(int monthCount)
        => new(monthCount);

    public static implicit operator DateTime(CompetenceMonth competenceMonth)
        => competenceMonth.ToDateTime();

    public static implicit operator CompetenceMonth(DateTime dateTime)
        => new(dateTime);

    public static implicit operator string(CompetenceMonth competenceMonth)
        => competenceMonth.ToCompetenceText();

    public static implicit operator CompetenceMonth(string txt)
        => new(txt);

}

IV. Data Storage and Retrieval

When working with databases, we may encounter tables with columns that have different formats than the C# types we are using in our code. Two of the most commonly used C# solutions for database handling are EntityFramework and Dapper.

When using Dapper to read data from the database, it automatically converts the database types to the corresponding C# types. With the help of implicit operators, we can then easily convert those types to CompetenceMonth instances. However, we may encounter scenarios where we need to handle the data in a more specialized way. In such cases, we can create specialized mappers using Dapper's TypeHandler class.

For example, the following code creates a TypeHandler that maps CompetenceMonth instances to the month count database format:

public class CompetenceMonthTypeHandlerAsMonthCount : SqlMapper.TypeHandler<CompetenceMonth>
{
    public override CompetenceMonth Parse(object value)
    {
        if (value is int v)
        {
            return new CompetenceMonth(v);
        }

        return new CompetenceMonth();
    }

    public override void SetValue(IDbDataParameter parameter, CompetenceMonth value)
    {
        parameter.Value = value.ToMonthCount();
    }  
}

When using EntityFramework Core, we can map the CompetenceMonth property directly using the Fluent API, as shown in the following example:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public CompetenceMonth Competence { get; set; }
}

public class EmployeeContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
            .Property(e => e.Competence)
            .HasConversion(
                v => v.ToMonthCount(),
                v => new CompetenceMonth(v));
    }
}

Alternatively, we can create a generic value converter that can be used for any entity with a CompetenceMonth property:

public class CompetenceMonthValueConverterAsMonthCount : ValueConverter<CompetenceMonth, int>
{
    public CompetenceMonthValueConverterAsMonthCount() : base(
        v => v.ToMonthCount(),
        v => new CompetenceMonth(v))
    { }
}

We can then add this converter at the DbContext level by calling the following extension method:

  public static ModelConfigurationBuilder AddDefaultMonthCompetenceConverter(this ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder.Properties<CompetenceMonth>().HaveConversion<CompetenceMonthValueConverterAsMonthCount>();
        return configurationBuilder;
    }

One important aspect to consider in the implementation of the CompetenceMonth struct is the use of the month count representation as an integer. This decision provides a very practical way to store and retrieve the information from databases, as most database engines can efficiently handle integer data types.

Storing CompetenceMonth as an integer can also provide an easy way to select ranges of data. For instance, it is possible to retrieve all records from a database within a given range of CompetenceMonth values by using a simple SQL query with the BETWEEN operator.

On the other hand, if CompetenceMonth was stored as a string, selecting a range of data would require the use of the IN clause, which can be less performatic. Similarly, storing CompetenceMonth as a DateTime object would require additional handling of the days, which can be error-prone.

Therefore, the decision to represent CompetenceMonth as an integer provides a practical and efficient way to store and retrieve the data from databases, which can be very useful in real-world scenarios where the amount of data can be very large.

The extension methods below provide an easy way to find all that ranges whenever they are needed.

public static IEnumerable<string> CompetenceTextRangeTo(this CompetenceMonth cmFrom, CompetenceMonth cmTo)
    {
        if (cmFrom > cmTo)
        {
            (cmTo, cmFrom) = (cmFrom, cmTo);
        }

        while (cmFrom <= cmTo)
        {
            yield return cmFrom.ToCompetenceText();
            cmFrom = cmFrom.AddMonths(1);
        }
    }    

    public static (DateTime, DateTime) DateTimeRangeTo(this CompetenceMonth cmFrom, CompetenceMonth cmTo)
    {
        if (cmFrom > cmTo)
        {
            (cmTo, cmFrom) = (cmFrom, cmTo);
        }

        DateTime fromDate = cmFrom;
        DateTime toDate = cmTo;

        toDate = toDate.AddMonths(1).AddDays(-1);

        return (fromDate, toDate);
    }

    public static (int, int) MonthCountRangeTo(this CompetenceMonth cmFrom, CompetenceMonth cmTo)
    {
        if (cmFrom > cmTo)
        {
            (cmTo, cmFrom) = (cmFrom, cmTo);
        }

        int fromDate = cmFrom;
        int toDate = cmTo;        

        return (fromDate, toDate);
    }

V. Conclusion

In this post, we discussed the problem of inconsistent data storage and retrieval in software development, and how it can be addressed by creating and standardizing value objects. Specifically, we introduced the CompetenceMonth struct, which provides a standardized way of representing and converting month competences in C# code.

We went through the step-by-step implementation of the CompetenceMonth struct, which included defining the struct, implementing constructors and argument validation, defining properties and methods for easy access and conversion of month competences, implementing equality comparisons and hashing, and implementing operators for easy conversion between CompetenceMonth and int/DateTime types. We also discussed the importance of implementing the IEquatable and IComparable interfaces, as well as implicit operator overrides, extension methods, database converters and mappers for EF Core and Dapper.

We also emphasized that these techniques can be applied to any DDD value object, not just CompetenceMonth. By creating and standardizing value objects, we can improve code readability, ensure data consistency, and make it easier to implement algorithms and collections that work with domain concepts.

In conclusion, we encourage readers to apply the discussed techniques to their own DDD value objects, as it can help to prevent common issues with data consistency and provide a standardized way of representing domain concepts in code.

VI. GitHub Repository

I have created a GitHub repository with the code examples discussed in this post, as well as additional implementations, unit tests, examples of how to use the CompetenceMonth struct with Entity Framework Core and Dapper, and of course, a solution for the presented problem. We encourage readers to check out the repository and experiment with the code to gain a deeper understanding of how to implement and use value objects in their own projects. The repository can be found at github.com/nelsonciofi/Competence.

I would love to hear your thoughts on this post and how I handled this value object inconsistency. Have you encountered similar challenges in your projects? How did you handle them? Feel free to leave a comment below and join the discussion.

P.S.: This post was written with the help of AI, but the implementations are all mine.
P.S.2: The cover image was also created with AI.
P.S.3: Do we still need a blog after the invention of GPT?