How should I handle having a entities property be non-nullable while the column is nullable? - EF Core

c# entity-framework entity-framework-core

Question

The database has nullabble columns, which I was told it was safe to assume would never have null data in them. The entity framework code was modeled under this assumpion. Some customer databases, however, I have found do in fact have the occasional null value in these columns. Entity Framework proceeds to blow up internally on using SqlReader to read these column by, for example, calling getDateTime on a null value.

The database schema was designed a long time ago, and cannot at this time be updated. In short, columns that should not be null, are sometimes null. The software enforces these columns to have appropriate values stored, not the database.

Our Entity Framework code was modeled assuming that the software has correctly done it's job, and that these columns that were not supposed to ever be null were simply not null.

In testing, I have found two customer's databases that somehow have null values in columns that should not, and entity framework is blowing up internally on trying to parse the errors. These are historic databases that still get loaded, but were probably caused by the software being more buggy 10+ years ago. These parsing errors are all SqlReader erroring on failing to call getDateTime on a null value for a non-nullable DateTime object, for example.

I can trial and error where it blows up, and manually change each property to be nullable (and then change the code to now handle the chance of it being nullable), however I do not want to make every single property nullable. Every null value I've seen so far could have been defaulted to an appropriate value.

I tried refactoring a property to be "PropertySafe" (to not break existing code), then made a new "Property" that was wired up, and let "PropertySafe"'s getter call Property and return an appropriate default on null. This is better, as the rest of the code does not have to be aware of this database schema flaw, however it is cumbersome to do over and over again, and I find it a little ugly. I also fear that some EFContext queries that grab one of these "Safe" properties would confused EF and force it to run them client side, which would be much less performant.

I took a dive at RelationalTypeMapping and ValueConverter's, thinking maybe there I could handle going from sql's "datetime not null" columns to a CLR DateTime property, defaulting when I need, however that does not work because the database has already been parsed at that point. I would need to override whereever it specifically is calling getDateTime on the SqlReader and intercept it there.

An example Entity/Property to Table/Column that fails is virtually the same as the following sample.

public class Entity {
    public int Id { get; set; }
    public DateTime DueDate { get; set; }

    public class Configuration : BaseConfiguation<Entity> {
      protected override string TableName => "ENTITY_DUE_DATE";
      protected override void DoConfigure( EntityTypeBuilder<Entity> builder ) {
        builder.HasKey( x => new { x.Id } );
        builder.Property( x => x.Id ).HasColumnName( "ID" );
        builder.Property( x => x.DueDate ).HasColumnName( "DUEDATE" );
      }
    }
  }

with the SQL Schema

CREATE TABLE ENTITY_DUE_DATE  (
    ID INT NOT NULL,
    DUEDATE DATETIME NULL
);

Where our DateTime mapping looks like

public class SqlServerDateTimeMapping : DateTimeTypeMapping {

    public SqlServerDateTimeMapping() : base( "DateTime", System.Data.DbType.DateTime ) { }

    protected SqlServerDateTimeMapping( RelationalTypeMappingParameters parameters ) : base( parameters ) { }

    public override string GenerateSqlLiteral( object value ) {
      if ( value is DateTime date ) {
        if ( date.Date != date ) {
          System.Diagnostics.Debug.Fail( "Time portions are not supported" );
        }
        return date.ToString( "yyyy-MM-dd" );
      }
      return base.GenerateSqlLiteral( value );
    }

    public override RelationalTypeMapping Clone( in RelationalTypeMappingInfo mappingInfo ) => new SqlServerDateTimeMapping();

    public override RelationalTypeMapping Clone( string storeType, int? size ) => new SqlServerDateTimeMapping();

    public override CoreTypeMapping Clone( ValueConverter converter ) => new SqlServerDateTimeMapping();
  }

As I said, I tried making a converter to plug into the SqlServerDateTimeMapping's Converter override, but I could not figure out how to handle it BEFORE/DURING the parsing of Sql. It seems the converter lets you convert between two CLR types, so post-parsing.

For the sake of completeness, this is my converter currently. It's outright wrong though.

public class NullableDateTimeToDateTimeConverter : ValueConverter<DateTime?, DateTime> {
    public NullableDateTimeToDateTimeConverter() : base(s => s ?? DateTime.MaxValue, x => x ) { }
  }

I expect to be able to have my Entity class's Property be non-nullable, keep the database column nullable, and have the entity framework engine handle returning a default value when it finds a null, whereas it currently blows up on parsing the null (specifically in SqlBuffer.GetDateTime() called by SqlReader.GetDateTime() inside any Context query, whether its context.EntityDueDate.ToList() or context.Set()).

Whatever the solution is, it would be ideal if my queries can still handled database-side rather than client side. So if I did

context.EntityDueDates.Where(x => x.DueDate > DateTime.UtcNow).ToList()

It would be best to be able to run that query on the database and not have to be returned client-side to do the

DueDate ?? DEFAULT_VALUE

logic, however it fits in there.

I should clarify, the solution should work for more than just DateTime. I'm just picking on DateTime because the one I manually fixed was DateTime, but examining the databases shows me that this same scenario will likely occur for a few int/int? differences as well. I would hope the solution given though can be applied between any nullable/non-nullable data types.

UPDATE:

I have a new lead. In the SqlServerDateTimeMapping class, I can override the method:

public override MethodInfo GetDataReaderMethod() {
      var methodInfo = base.GetDataReaderMethod();
      return methodInfo;
}

and inspect methodInfo. Sure enough, this is the method "GetDateTime", the very same that is crashing on parsing. My thought process is maybe I can somehow return my own method that can be invoked on SqlDataReader that handles null checking, or subclass SqlDataReader to override this method. I notice now though that GetDateTime takes a int as the parameter, refering to which column. Internally it calls IsDBNull before invoking to make sure it is not null. I was hoping a string was being passed I could override the parsing of, however it looks like this simply takes a column and uses SqlBuffer to get_dateTime on, which is who does the parsing

1
4
5/17/2019 8:46:47 PM

Accepted Answer

I have solved the answer, however I am open to other solutions. This "solves" my answer, however it limits me to having one generic "Default" per type. Sometimes the "best" default might be DateTime.MinValue, sometimes DateTime.MaxValue, sometimes DateTime.UtcNow. Having one default has its flaws. I do think this answer does not create a performance hit, and I'm sure does not mess with the mappings in any way that might make work be done client side in a query.

My solution was to make a class, "ContextDbDataReaderDecorator", which can be implicitly converted back and forth between a DbDataReader. It stores the DbDataReader upon creation, and uses it for its own GetDataTimeSafe alternative method. The implicit conversion allows us to return it from the SqlServerDateTimeMapping's "GetDataReaderMethod" overload, as it can then be invoked on a DbDataReader despite not actually belonging to a DbDataReader.

My relational type mapping object looks like the following:

public class SqlServerDateTimeMapping : DateTimeTypeMapping {
    static MethodInfo s_getDateTimeSafeMethod = typeof( ContextDbDataReaderDecorator ).GetMethod( nameof( ContextDbDataReaderDecorator.GetDateTimeSafe ) );

    public SqlServerDateTimeMapping() : base( "DateTime", System.Data.DbType.DateTime ) {}

    protected SqlServerDateTimeMapping( RelationalTypeMappingParameters parameters ) : base( parameters ) {}

    public override string GenerateSqlLiteral( object value ) {
      if ( value is DateTime date ) {
        if ( date.Date != date ) {
          System.Diagnostics.Debug.Fail( "Time portions are not supported" );
        }
        return date.ToString( "yyyy-MM-dd" );
      }
      return base.GenerateSqlLiteral( value );
    }


    public override MethodInfo GetDataReaderMethod() {
      return s_getDateTimeSafeMethod;
    }

    public override RelationalTypeMapping Clone( in RelationalTypeMappingInfo mappingInfo ) => new SqlServerDateTimeMapping();
    public override RelationalTypeMapping Clone( string storeType, int? size ) => new SqlServerDateTimeMapping();
    public override CoreTypeMapping Clone( ValueConverter converter ) => new SqlServerDateTimeMapping();
  }

and the ContextDbDataReaderDecorator class I created looks like the following:

public class ContextDbDataReaderDecorator {
    public DbDataReader DbDataReader { get; }

    public ContextDbDataReaderDecorator( DbDataReader inner ) {
      DbDataReader = inner;
    }

    public static implicit operator DbDataReader( ContextDbDataReaderDecorator self )  => self.DbDataReader;
    public static implicit operator ContextDbDataReaderDecorator( DbDataReader other ) => new ContextDbDataReaderDecorator( other );
    public DateTime GetDateTimeSafe( int ordinal ) => (DbDataReader.GetValue( ordinal ) as DateTime?) ?? new DateTime( 3000, 1, 1 );
    public int GetIntSafe(int ordinal) => (DbDataReader.GetValue( ordinal ) as int?) ?? 0;
    public long GetLongSafe( int ordinal ) => (DbDataReader.GetValue( ordinal ) as long?) ?? 0;
    public float GetFloatSafe(int ordinal) => (DbDataReader.GetValue( ordinal ) as float?) ?? 0.0f;
  }
0
5/17/2019 9:42:32 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow