CODEWITHSUNDEEP
c#mysqlentity-framework-core
eerick
eerick

Reputation: 447

How do I use a boolean on my model that wraps a string value from the database?

Weird question, and there may not be an answer... I inherited an old mysql database where unfortunately many of the "boolean" fields were set up as VARCHAR instead of the more appropriate TINYINT(1)...

I'm now running Entity Framework Core on top of the database. Since I don't know everywhere the boolean values are used, I can't update the database quite yet, but I would like to be able to actually work with the string values as booleans in EF... Is there a way to change the model types so the model wraps up everything neatly as bool but it still treats the fields as strings when I push it to the database?

Essentially, in my code, I want to be able to do this: object.IsGood = true; Instead of this: object.IsGood = "TRUE"

And my model would just discreetly handle the value conversion, something like this for writing to the database (and I'd need another converter for reading the boolean values back from the database):

string databaseValue = "";
if (object.IsGood)
{
  databaseValue = "TRUE";
}
else
{
  databaseValue = "FALSE";
}

Any way I can see of doing it, I'd be actually changing the database when I change the model... I know I could wrap the model itself into another class, but that seems confusing. I'd love to just update the database, but that could be a huge pain to unravel all the possible places that code touches these values... so I was hoping there was an intermediate solution.

Upvotes: 2

Views: 2094

Answers (2)

eerick
eerick

Reputation: 447

I always search for this type of thing under the terms "mapping" or "wrapper" and those were not bringing up anything from the documentation that seemed useful... Christopher's comment got me on the right track and I got what I was searching for.

Apparently "Value Conversions" are what I was looking for: Value Conversions EF Core

Edit: I've removed the docs example since anyone can look that up. I'm adding my real example below.

Since I wanted to convert back and forth between a string and a boolean, I created this converter as a static value so I could reuse it in a couple of models:

public static class EntityFrameworkCoreValueConverters
{
    /// <summary>
    /// converts values stored in the database as VARCHAR to a nullable bool
    /// handles "TRUE", "FALSE", or DBNULL
    /// </summary>
    public static ValueConverter<bool?, string> dbVarCharNullableToBoolNullableConverter = new ValueConverter<bool?, string>(
        v => v == true ? "TRUE" : v == false ? "FALSE" : null,
        v => v.ToUpper() == "TRUE" ? true : false
    );
}

Note that there already exists a BoolToStringConverter as part of the Microsoft.EntityFrameworkCore.Storage.ValueConversion namespace, but this doesn't appear to handle null values, which I needed.

Then I can change my model values to bool? instead of string, but leave alone the actual database value types.

The dbVarCharNullableToBoolNullableConverter can then be applied in my OnModelCreating method (or in my case Configure on the model itself which gets applied in the OnModelCreating method:

public void Configure(EntityTypeBuilder<MachineHelpRequests> builder)
        {
            //... model builder code above

            builder.Property(e => e.IsAcknowledged)
                .HasColumnName("acknowledged_mhr")
                .HasColumnType("varchar(45)")
                .HasConversion(EntityFrameworkCoreValueConverters.dbVarCharNullableToBoolNullableConverter);

            //... model builder code below
}

Upvotes: 7

user2908532
user2908532

Reputation: 21

This is probably not the best solution but it might get you by until you can refactor the DB.

Add a partial class to the Model and implement the appropriate getters and setters.

partial class Model
{
    [NotMapped]
    public bool FieldABool
    {
        get
        {
            return FieldA == "TRUE";
        }
        set
        {
            if (value == true)
            {
                    FieldA = "TRUE";
            }         
            else
            {
                FieldA = "FALSE";
            }
        }
    }
}

Upvotes: 1

Related Questions