efcore: 2.2.0-preview1: System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.'

Hi,

I just upgraded to 2.2.0-preview1, and started getting an exception on previously working code.

The exception is: System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.' and occurs inside System.Linq.AsyncEnumerable.SingleOrDefault()

The stack trace is:

   at System.Data.SqlTypes.SqlGuid.get_Value()
   at System.Data.SqlClient.SqlDataReader.GetGuid(Int32 i)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<BufferlessMoveNext>d__12.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.<ExecuteAsync>d__7`2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.<MoveNext>d__11.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.AsyncSelectEnumerable`2.AsyncSelectEnumerator.<MoveNext>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at System.Linq.AsyncEnumerable.<SingleOrDefault_>d__381`1.MoveNext()

The line that throws the error is:

var user = await this.DbContext.As
    .Include(a => a.B.C)
    .ThenInclude(c => c.D)
    .SingleOrDefaultAsync<A>(a => a.Property1 == Constant && a.Property2 == Parameter1 && a.B.Property1 == Parameter2);

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 24 (6 by maintainers)

Most upvoted comments

@ajcvickers Yes. So I incorrectly added the IsRequired() to the property.

joshmouch on Aug 31, 2018

FYI EnableRichDataErrorHandling has been updated to EnableDetailedErrors

https://github.com/aspnet/EntityFrameworkCore/blob/release/2.2/src/EFCore/DbContextOptionsBuilder`.cs#L110

nickdevereaux on Mar 19, 2019

@joshmouch Use EnableRichDataErrorHandling: https://github.com/aspnet/EntityFrameworkCore/blob/d59be61006d78d507dea07a9779c3c4103821ca3/src/EFCore/DbContextOptionsBuilder`.cs#L110

I also filed: https://github.com/aspnet/EntityFramework.Docs/issues/955

ajcvickers on Aug 31, 2018

@wgutierrezr Likely there is a database null in one of the columns marked as [Required]. Try using EnableRichDataErrorHandling as described in the comment above yours. If you’re still hitting issues, then please file a new issue and include a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

ajcvickers on Jan 12, 2019

@ajcvickers I am also getting this error after upgrading to 2.2.0. After enabling rich errors, I get a field name that is supposedly causing the exception, but that field is a string, so it should be nullable by default.

System.InvalidOperationException: An exception occurred while reading a database value for property 'TableName.Loc'. The expected type was 'System.String' but the actual value was null. ---> System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

When I specifically add IsRequired(false) to model builder for field ‘LOC’, it says I cannot make a DIFFERENT property nullable because it’s part of the key. I get an error saying ‘PROJ’ cannot be nullable. But I am not touching ‘PROJ’. This is very confusing. I had to revert back to 2.1.4, and I was able to do my migration and my code works as normal. When I have some time I’ll try to extract the table and create a project to demonstrate the problem, but my Database is so complex, I am not sure if it’ll work. When, I make a migration using 2.2.0, it forces the ‘LOC’ field to be Required, even though I haven’t changed anything that would make the migration do that.

bobekhj on Jan 11, 2019

The [Required] data annotation suddenly triggering this SqlNullValueException had us stuck for the last few months too. This update to EF Core 2.2.1 was part of a recent Azure Functions update so while our other services were working normally with the exact same queries, our Azure Functions started throwing these errors. Took us a fairly long time to follow the breadcrumbs down to the fact that someone had mistakenly added the [Required] attribute to a few of our entities a while back. This went unnoticed because previous versions of EF Core ignored that data annotation. Removing the unwanted annotations fixed our issue, but a clearer error message letting us know what field caused the error would have been great.

reff134-projects on Apr 9, 2019

I had a similar issue. To fix, I had to grab the generated query from the Output tab in Visual Studio and execute that query directly against the database to find the column that was null. It ended up being a primary key column of a completely different table/entity that was included in my result set because, and I’m speculating here, in FluentAPI I have a .HasForeignKey reference to that table. I can provide more information if needed, but I would suggest grabbing the generated query from the output and executing that by itself if you haven’t already.

dst3p on Feb 7, 2019