Support for mapping single property to multiple database columns

[GintarasDev]

What problem are you trying to solve?

Usually, we are storing Enums to the database using their string representation as that makes it more readable for our data analysts and other interested parties that are accessing the raw data from our database.

For one of our properties - we decided to use flag Enums as we want to allow selection of multiple values. Storing flag Enum string representation to the database seems to be quite straightforward. However, data gets stored in a format similar to Option0; Option4; Option5. This is great for human readability, however, this means that when our app is querying the database to get all rows for which Option4 is selected - it has to use SQL CONTAINS or LIKE to find which columns include our string value (and writing C# LINQ that would compile and translate to that is non-trivial, also performance of such approach may not be great).

Describe the solution you'd like

The solution to this problem seems to be mapping the same flags Enum property to 2 different columns in the database. One - binary representation that would be easy to filter using queries, and the other - string representation that would be easy to read for human.

In IEntityTypeConfiguration it could look something like this:

builder.Property(e => e.Options)
    .HasColumnName("OptionsValue");

builder.Property(e => e.Options)
    .HasAdditionalColumn("OptionsHumanReadable") // Indicates that this property should be mapped to another column as well.
    .HasConversion(value => value.ToString());

Currently adding 2 separate configurations for the column simply results in the last one added being used.

To ensure that the columns cannot be edited separately using raw SQL the additional columns could be implemented using stored computed columns.

For our specific case we created the following prototype solution:

public static PropertyBuilder<string> HasHumanReadableColumn<TEntity, TProperty>(
    this EntityTypeBuilder<TEntity> builder,
    Expression<Func<TEntity, TProperty>> propertyExpression) where TProperty : Enum where TEntity : class
    {
        var targetColumnName = builder.Property(propertyExpression).Metadata.GetColumnName();

        var values = (int[])Enum.GetValues(typeof(TProperty));
        var sql = GenerateSQLCase(values[1]);
        foreach (var value in values[2..])
        {
            sql += " + '; ' + " + GenerateSQLCase(value);
        }

        return builder.Property<string>($"{targetColumnName}_HumanReadable")
            .HasComputedColumnSql(sql, stored: true);

        string GenerateSQLCase(int value) => $"CASE WHEN ({targetColumnName} & {value}) = {value} THEN '{Enum.GetName(typeof(TProperty), value)}' ELSE '' END";
    }

This, of course, is very specific to our use case.
The only way to generate 2 columns for the same property without using HasComputedColumnSql and raw SQL seems to be using property with 2 backing fields. However, that seems to be a little invasive and for the use case where the second (human readable) column is only needed in database and will not be used in code - it seems a bit excessive.

Or maybe we're missing something here and there is an easier way of achieving this?

[roji]

using property with 2 backing fields

This seems like a very reasonable solution to me - the human-readable backing field can be private so as not to show up on the type surface, etc. HasComputedColumnSql also seems like a decent approach if you want to keep this completely out of your .NET code (though I really think that's fine too).

We do have #31234 on the backlog for value-converting a single property to multiple columns in the database - that could make this possible. But given we shipped complex types in 8.0, we're unlikely to implement that any time soon. I'd advise implementing one of the above 2 strategies instead.

[roji]

Duplicate of #31234