EF Core ValueConverter string to bool for nullable string column

[apro-neirelo]

I started this question in Stackoverflow and am now posting here to try to get feedback from the source. Basically I want to convert a nullable string in the database to a non-null bool entity property. I'm using a ValueConverter to do the conversion, checking for the null or non-null db value and then converting this to the appropriate non-null bool that is given to the entity. Than I'm also doing the reverse where I'm taking the non-null entity property value and converting it to a correct string value I want to represent true or false as a string in the db.

Maybe it helps to understand that I'm trying to cover the fact that this is being used to access a database and tables that already exist with nullable string columns which I want to hide using the ValueConverter and non-null properties.

I've not been able to get my non-null bool entity property, the entity configuration, and the migration to all work together in a way that will allow this to work. The SO post describes what I've done but if I need to repost the contents of it here let me know.

https://stackoverflow.com/questions/79149074/ef-core-valueconverter-string-to-bool-for-nullable-string-column

I'm looking for some feedback to help understand if what I'm trying to accomplish is possible. I have a database column that is a nullable string type (mostly varchar) and I am using a ValueConverter to convert the string db value into an entity bool value based on if the db value is null, 'N' (false) or 'Y' (true).

I also wanted to eliminate the nullable bool? type on the entity property using the EntityConfiguration. The problem I'm now having is that when I make the entity property a non-null bool instead of bool? the migration fails saying that the type must be nullable.

First I have this ValueConverter to convert my string values into a non-nullable bool.

internal class NullableStringToBoolValueConverter : ValueConverter<bool, string?>
{
    public NullableStringToBoolValueConverter() : base(ToDbValue, ToEntityValue) { }

    private static HashSet<string> YesTypes = new(StringComparer.OrdinalIgnoreCase) { "1", "Y", "Yes" };
    private static HashSet<string> NoTypes = new(StringComparer.OrdinalIgnoreCase) { "0", "N", "No" };

    private static Expression<Func<string?, bool>> ToEntityValue
        => value => value != null && YesTypes.Contains(value) ? true : false;

    private static Expression<Func<bool, string?>> ToDbValue
        => value => value == false ? "N" : "Y";

    /// <summary>
    /// Allow nulls to pass into converter for conversion.
    /// </summary>
    public override bool ConvertsNulls => true;
}

I then have the entity using a non-nullable bool property.

public class EntityName
{
    public bool IsActive { get; set; }
}

Lastly, I have the following configuration for this property.

public class EntityNameConfiguration : ...omitted
{
    builder.Property(e => e.IsActive)
        .HasConversion<NullableStringToBoolValueConverter>()
        .HasMaxLength(10)
        .IsUnicode(false)
        .IsRequired(false) // Throws runtime error about requires a non-null entity property
}

I've now created a local db that is created using the non-nullable property which of course creates a non-nullable column. I then change the column to be nullable, just to test, and leave the EF stuff as non-nullable. This gives me a new error which looks like EF internally is expecting a non-nullable db column and finds a null column instead.

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
   at Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at lambda_method165(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)

I'm starting to think that using a non-null entity property with a nullable column isn't possible. I thought (hoped) that the ValueConverter would allow this to work.

... EF Core version: 8.0.10 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 8.0 Operating system: IDE: (e.g. Visual Studio 2022 17.4)

[AndriySvyryd]

I'm starting to think that using a non-null entity property with a nullable column isn't possible.

That's correct. We will enable this with https://github.com/dotnet/efcore/issues/13850

[apro-neirelo]

@AndriySvyryd thank you for the update

[apro-neirelo]

@AndriySvyryd, I'm confused. I just checked the backlog and see that the PR was merged. Does this mean that it's been completed and this should work?

[AndriySvyryd]

There has been some initial work, but the issue is still open

[ajcvickers]

I believe this is a duplicate of #24685, but I also have some other thoughts. Removing from milestone to discuss.

[ajcvickers]

Closing as a duplicate; will comment soon on https://github.com/dotnet/efcore/issues/13850.