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

[joshmouch]

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);

[joshmouch]

I should also note that the value of Parameter2 is null when the error occurs.

[joshmouch]

Also, if I comment out the Include and ThenInclude lines then the line of code works. In other words, this works as expected:

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

[smitpatel]

@joshmouch - Can you submit a full repro code which demonstrate issue you are hitting? In the absence of model classes and configuration, it is really difficult for us to investigate this issue.

[joshmouch]

@smitpatel No I can't. I can gather any other info you need, though.

[joshmouch]

After removing the .Include method, above, I tried loading the related entity after the query results were returned, and I got the same exception. So it's probably not the Include, but rather something with the relationship between the two entities:

// This throws the same exception:
await context.Entry(a).Reference<B>(a => a.B).LoadAsync();

[joshmouch]

On a hunch, I tried changing a line in the DbContext OnModelCreating from:

a.Property(e => e.BId).IsRequired();

to:

a.Property(e => e.BId);

And that fixed the problem. Is there any way to get a better error message in this case?

[ajcvickers]

@joshmouch Does the BId property map to a column that contains nulls?

[joshmouch]

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

[joshmouch]

@ajcvickers Is it possible to add a better error message for this situation?

[ajcvickers]

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

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

[wgutierrezr]

Hi
My project ASP.Net Core 2.2 MVC, have a Vendors class with DataAnnotations:
```C#
public partial class Vendors : BaseClass
{
public Vendors()
{
Contacts = new HashSet();
VendorContracts = new HashSet();
VendorIngredients = new HashSet();
VendorNotes = new HashSet();
}

    [Key]
    public int VendorId { get; set; }

    [Required(ErrorMessage = "Code is required")]
    [StringLength(maximumLength: 4, MinimumLength = 4, ErrorMessage = "Code must have 4 characters")]
    [DisplayFormat(NullDisplayText = "Enter Code...")]
    public string Code { get; set; }

    [Required]
    [Display(Name = "Company Name")]
    [StringLength(int.MaxValue, MinimumLength = 4, ErrorMessage = "Company Name must at least 4 characters")]
    public string CompanyName { get; set; }

    [Display(Name = "Website")]
    [Url]
    public string CompanyWebsite { get; set; }

    [Required]
    public string Country { get; set; }

    [Required]
    [Display(Name = "Province/State")]
    public string ProvinceState { get; set; }

    [Display(Name = "City")]
    public string City { get; set; }

    [Display(Name = "Number")]
    public string StreetNumber { get; set; }

    [Display(Name = "Street Name")]
    public string StreetName { get; set; }

    [Display(Name = "Unit/Suite")]
    public string UnitSuite { get; set; }

    [Display(Name = "Postal/Zip Code")]
    [DataType(DataType.PostalCode)]
    public string PostalZipCode { get; set; }

    [Display(Name = "eMail Address")]
    [Required(ErrorMessage = "eMail Address is required")]
    [DataType(DataType.EmailAddress)]
    [RegularExpression(@"^[\d\w\._\-]+@([\d\w\._\-]+\.)+[\w]+$", ErrorMessage = "Email is invalid")]
    public string NotificationsEmail { get; set; }

    [Display(Name = "Company Partner")]
    public bool? CompanyPartner { get; set; }

    public ICollection<Contacts> Contacts { get; set; }
    public ICollection<VendorContracts> VendorContracts { get; set; }
    public ICollection<VendorIngredients> VendorIngredients { get; set; }
    public ICollection<VendorNotes> VendorNotes { get; set; }
}

And a service that calls my unit of work:
```C#
// This is a service method
        public IEnumerable<Vendors> GetAllVendors()
        {        
            return uow.Vendors.GetAll();
        }

// This is a UOW method
        public IEnumerable<TEntity> GetAll()
        {
            return dbSet.ToList();
        }

The error I got:

{System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
   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[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at EMS.DAL.Persistance.Base.Repository`1.GetAll() in C:\Projects\EMS2\EMS\EMS.DAL\Persistance\Base\Repository.cs:line 43
   at EMS.Service.DataService.Common.MasterDataServices.GetAllVendors() in C:\Projects\EMS2\EMS\EMS.Service\DataService\Common\MasterDataServices.cs:line 78}`

When I remove all DataAnnotations from my Vendors class, everthing works perfectly.

I don't know is this issue is related but I have the same error message.

My Startup.cs uses AddMVC, and my controller use ": Controller".

What am I doing wrong?

Regards.

[bobekhj]

@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.

[ajcvickers]

@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]

@bobekhj Looks like you could be hitting one of the issues here. If not, then then please file a new issue and include a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[Slacquer]

NOTE, let me first say that I am sorry. I was wrong. It seems i DID have an IsRequired on one of my properties. With that being said, i believe there is a bug PRIOR to 2.2, where this was being ignored and everything worked as expected!

Now i am on 2.2 and everything is fine.

One more thing "Data is Null. This method or property cannot be called on Null values." has got to be one of the most useless error messages I have encountered. It would be great if it simply would have just given the name!

I also receive this error and do NOT use annotations at all, only fluentapi. I have triple checked the IsRequired matching my schema, this is NOT the answer, something very bad has happened from 2.1 to 2.2

My assumption is that "GetFieldValueFromSqlBufferInternal" is trying to read @__query_MarketId_0 ? and failing, but why? what changed that caused this? My code in question is EXACTLY the same from 6 months ago.

Should mention this
Sql:
SELECT [p].[Id], [p].[BlendedContribution], [p].[BlendedRate], [p].[CreatedOn], [p].[Description], [p].[ExpiringContribution], [p].[ExpiringRate], [p].[ManualContribution], [p].[ManualRate], [p].[MarketId], [p].[ModifiedOn], [p].[Status], [p].[Title], [p].[TotalPayroll]
FROM [FederalActScenarios] AS [p]
WHERE [p].[MarketId] = @__query_MarketId_0
ORDER BY CASE
WHEN [p].[Title] = N'Base Scenario'
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END DESC, [p].[CreatedOn], [p].[Id]

trace:
at System.Data.SqlClient.SqlDataReader.GetFieldValueFromSqlBufferInternalT
at System.Data.SqlClient.SqlDataReader.GetFieldValueT
at lambda_method(Closure , DbDataReader )

[ajcvickers]

@Slacquer Please file a new issue and include a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[dst3p]

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.

[Slacquer]

Hi Derek, thanks for replying my apologies I am just now reading this.

Your suggestion seems quite valid, but what bothers me is that the code runs perfectly fine when in 2.1, without any changes. Are you suggesting that 2.1 actually is NOT erroring and should be?

From: Derek Stepan [e-mail]
Sent: Thursday, February 7, 2019 11:11 AM
To: aspnet/EntityFrameworkCore [e-mail]
Cc: Slacquer [e-mail]; Mention [e-mail]
Subject: Re: [aspnet/EntityFrameworkCore] 2.2.0-preview1: System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.' (#13169)

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.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub https://github.com/aspnet/EntityFrameworkCore/issues/13169#issuecomment-461514845 , or mute the thread https://github.com/notifications/unsubscribe-auth/ATx75aZoBYq5v7P4A1yIEUlwEgL-XtSSks5vLF4igaJpZM4WUA0u . https://github.com/notifications/beacon/ATx75ehmbA6tDoq3M6nz0ZWcbdGi7PTcks5vLF4igaJpZM4WUA0u.gif

[dst3p]

@Slacquer no worries. I hadn't intended for my reply to be applicable to your comment, rather, to help others who might have made the same mistake that I did.

[nickdevereaux]

FYI EnableRichDataErrorHandling has been updated to EnableDetailedErrors

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

[tadrian]

I am using the [Required] annotation in my model and noticed that after upgrading to 2.2.1/2.2.2 I was getting the error "Data is Null. This method or property cannot be called on Null values"

When I looked in SQL I noticed that some of the data in inf2 was null (and not string), once I added a value to the column it was working again.

```c#
MODEL
[Required]
[Display(Name = "Friendly Name")]
public string Inf2 { get; set; }

ERROR

fail: Microsoft.EntityFrameworkCore.Query[10100]
An exception occurred while iterating over the results of a query for context type '...Context'.
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
at System.Data.SqlClient.SqlBuffer.get_Int32()
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at lambda_method(Closure , DbDataReader )

[reff134-projects]

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.

[GitHubSlob]

This is kinda relevant but I got the error with Sqlite.
InvalidOperationException: The data is NULL at ordinal 0. This method can't be called on NULL values. Check using IsDBNull before calling.
Microsoft.Data.Sqlite.SqliteValueReader.GetInt64(int ordinal)

It turns out I tried adding a record to an Sqlite table using "Create New" form that scaffolding created. I clicked the Save button just to see what would show up in the Identity column (autonumber) which I guess didn't exist because what happened was the row was added to the table with all Null values from the Primary Key on through. So I found this "all Nulls" row when I clicked on "Retrieve Data" in Visual Studio's Server Explorer. So I deleted the row and everything started working again.
Might not apply to this thread but might. . .

[Curtis-]

Posting this here hoping that it may help someone and save them form several days of debugging and troubleshooting it had caused me. For me, the solution to the 'Data is null' error was that I had an integer property in my entity that that was receiving a null value from the query against the database. The fix was to declare the integer property as being nullable "int?".