title: How should I handle having a entities property be non-nullable while the column is nullable? - EF Core
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>An example Entity/Property to Table/Column that fails is virtually the same as the following sample.</p>
  
  <pre><code>public class Entity {
      public int Id { get; set; }
      public DateTime DueDate { get; set; }
  
      public class Configuration : BaseConfiguation&lt;Entity&gt; {
        protected override string TableName =&gt; "ENTITY_DUE_DATE";
        protected override void DoConfigure( EntityTypeBuilder&lt;Entity&gt; builder ) {
          builder.HasKey( x =&gt; new { x.Id } );
          builder.Property( x =&gt; x.Id ).HasColumnName( "ID" );
          builder.Property( x =&gt; x.DueDate ).HasColumnName( "DUEDATE" );
        }
      }
    }
  </code></pre>
  
  <p>with the SQL Schema</p>
  
  <pre><code>CREATE TABLE ENTITY_DUE_DATE  (
      ID INT NOT NULL,
      DUEDATE DATETIME NULL
  );
  
  </code></pre>
  
  <p>Where our DateTime mapping looks like</p>
  
  <pre><code>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 ) =&gt; new SqlServerDateTimeMapping();
  
      public override RelationalTypeMapping Clone( string storeType, int? size ) =&gt; new SqlServerDateTimeMapping();
  
      public override CoreTypeMapping Clone( ValueConverter converter ) =&gt; new SqlServerDateTimeMapping();
    }
  </code></pre>
  
  <p>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.</p>
  
  <p>For the sake of completeness, this is my converter currently. It's outright wrong though.</p>
  
  <pre><code>public class NullableDateTimeToDateTimeConverter : ValueConverter&lt;DateTime?, DateTime&gt; {
      public NullableDateTimeToDateTimeConverter() : base(s =&gt; s ?? DateTime.MaxValue, x =&gt; x ) { }
    }
  </code></pre>
  
  <p>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()).</p>
  
  <p>Whatever the solution is, it would be ideal if my queries can still handled database-side rather than client side. So if I did </p>
  
  <pre><code>context.EntityDueDates.Where(x =&gt; x.DueDate &gt; DateTime.UtcNow).ToList()
  </code></pre>
  
  <p>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 </p>
  
  <p><code>DueDate ?? DEFAULT_VALUE</code></p>
  
  <p>logic, however it fits in there.</p>
  
  <p>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.</p>
  
  <p>UPDATE:</p>
  
  <p>I have a new lead. In the SqlServerDateTimeMapping class, I can override the method:</p>
  
  <pre><code>public override MethodInfo GetDataReaderMethod() {
        var methodInfo = base.GetDataReaderMethod();
        return methodInfo;
  }
  </code></pre>
  
  <p>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</p>