Efcore: 2.2: System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.'

[dos32]

This issue has already been reported in:

13169

But we have difficulties with upgrading ef core in our our project from 2.1.1 to 2.2.6 due to this exception.
We using database first.
For example, lets say I have a model (in project we have many models with this problem):
```c#
[Table("Model", Schema = "Test")]
public class Model {
[Key]
public int ID { get; set; }
[Required]
public string Name { get; set; }
}

And table in database
```sql
CREATE TABLE [Test].[Model](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](100) NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
))

When I encounter a null value in "Name" property while loading entities from database I have exception mentioned in #13169 (System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.')
with stacktrace

   at System.Data.SqlClient.SqlBuffer.get_String()
   at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

On the other hand, when I save entities with this property set to null through context.SaveChanges, ef core engine does not notifies me that I'm saving not valid entities, so after saving changes I can't load previously saved entities. So, the code
c# using(var ctx = new MyContext()) { ctx.Models.Add(new Model { Name = null } ); ctx.SaveChanges(); var models = ctx.Models.ToList(); }
will throw an exception on ctx.Models.ToList().
Also, I've noticed that if I have an empty string in column "Name", exception is not throwing even with Required attribute. Thus, if this behaviour is designed as validation of data when loading, it works incorrectly, otherwise it is confusing because previous versions of ef core does not have this check and everything is loading fine until 2.2.0 version.
I know that we mistakenly set Required attribute on property that is set to nullable on the database side, but ef core versions prior to 2.2.0 does not take into account required attribute and we use this attribute to validate data on UI. In this way we can validate new entities and don't allow user to add entities with required properties is not set up while we still can load and show to user entities with null values (which have been added before we marked property as required) and let user to correct such entities. Thats why now we have many databases with some models where property that has required attribute in model is set to null in the database, so I want to disable this check when loading data. Is it possible?

[smitpatel]

Your database schema is supposed to match your EF model. In your case it does not. It may not have thrown exception in past but that was a bug. Reading database values which can be null requires additional check if the value came as null before reading the value as proper type. Which degrades the performance if nulls are not expected. Hence when something cannot be nullable we skip that check. It is more of an optimization then data validation.

In this way we can validate new entities and don't allow user to add entities with required properties is not set up while we still can load and show to user entities with null values (which have been added before we marked property as required) and let user to correct such entities.

You can use fluent API to override configuration set by data annotation. See https://docs.microsoft.com/en-us/ef/core/modeling/required-optional#fluent-api You can leave Required attribute for UI validation and override in OnModelCreating to make property nullable in EF model.

[dos32]

Thanks for your clarification! So, described behaviour is just kind of optimization of reading from database, I see.
Your solution is work nice for my situation, so I just write small snippet of code which compares models in my code and tables in database using reflection and information_schema to figure out which entities can cause this exception in our project.