How do I solve SqlNullValueException?

Question

I'm trying to find an entity but I get a SqlNullValueException when entity framework is trying to get the value of a field that's Null.

I checked in the database and all the not nullable fields are filled. The only fields that have a NULL value are the ones that are allowed to have it.

In other answers I found that I should remove required attribute from the class properties or remove the IsRequired method from modelbuilder definition, however I autogenerated these and they never had a required definition.

My app runs on .NET Core 3.1 and my EntityFrameworkCore is version 2.2.4. Even though it builds succesfully could this cause the issue? Update All versions are 3.1 now.

If not, what else can cause it?

To find the entity I'm using the following method:

public static CodeLists FindCodeListById(Guid id)
{
    using (var context = new classificatielocalContext())
    {
        return context.CodeLists.Find(id);
    }
}

However, I get the following exception:

System.Data.SqlTypes.SqlNullValueException

HResult=0x80131931

Message=Data is Null. This method or property cannot be called on Null values.

Source=System.Data.Common

StackTrace:

at System.Data.SqlTypes.SqlGuid.get_Value()

at System.Data.SqlClient.SqlDataReader.GetGuid(Int32 i)

at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)

at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)`

at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded)

at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext()`

at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable1 source, Boolean& found)`

at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable1.GetEnumerator()`

at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__172.MoveNext()`

at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext()`

at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable1 source, Boolean& found)`

at System.Linq.Enumerable.First[TSource](IEnumerable1 source)`

at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_11.b__0(QueryContext qc)`

at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)

at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)

at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source, Expression1 predicate)

at Microsoft.EntityFrameworkCore.Internal.EntityFinder1.Find(Object[] keyValues)`

at Microsoft.EntityFrameworkCore.Internal.InternalDbSet1.Find(Object[] keyValues)`

at dal.Views.FindCodeListById(Guid id) in ...

at services.CodeListService.GetCodeList(Guid id) in ...

at api.Controllers.CodeListController.Get(Guid id) in ...

at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)

at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)

at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<<InvokeActionMethodAsync>g__Logged|12_1>d.MoveNext()

I am using OnModelCreating(ModelBuilder modelBuilder) to define my entities and no where have I defined that my columns are required.

Here is my modelbuilder definition:

modelBuilder.Entity<CodeLists>(entity =>
{
    entity.HasKey(e => e.Id)
        .ForSqlServerIsClustered(false);

    entity.ToTable("code_lists");

    entity.Property(e => e.Id)
        .HasColumnName("id")
        .HasDefaultValueSql("(newsequentialid())");

    entity.Property(e => e.ClassificationId).HasColumnName("classification_id");

    entity.Property(e => e.DescriptionId).HasColumnName("description_id");

    entity.Property(e => e.NameId).HasColumnName("name_id");

    entity.Property(e => e.OwnerId).HasColumnName("owner_id");

    entity.HasOne(d => d.Classification)
        .WithMany(p => p.CodeLists)
        .HasForeignKey(d => d.ClassificationId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK__code_list__class__5FB337D6");

    entity.HasOne(d => d.Description)
        .WithMany(p => p.CodeListsDescription)
        .HasForeignKey(d => d.DescriptionId)
        .HasConstraintName("FK__code_list__descr__60A75C0F");

    entity.HasOne(d => d.Name)
        .WithMany(p => p.CodeListsName)
        .HasForeignKey(d => d.NameId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK__code_list__name___619B8048");

    entity.HasOne(d => d.Owner)
        .WithMany(p => p.CodeLists)
        .HasForeignKey(d => d.OwnerId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK__code_list__owner__628FA481");
});

And here is my sql definition

CREATE TABLE [dbo].[code_lists]
(
    [id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED DEFAULT NEWSEQUENTIALID(),
    [classification_id] UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES classifications(id), -- TODO: Should be set to NOT NULL
    [description_id] UNIQUEIDENTIFIER FOREIGN KEY REFERENCES translations(id),
    [name_id] UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES translations(id),
    [owner_id] UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES users(id), -- TODO: Should be set to NOT NULL
)

Update After updating the versions and adding nuget package System.Data.SqlClient the stack trace changed somewhat to:

System.Data.SqlTypes.SqlNullValueException

HResult=0x80131931

Message=Data is Null. This method or property cannot be called on Null values.

Source=Microsoft.Data.SqlClient

StackTrace:

at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()

at Microsoft.Data.SqlClient.SqlBuffer.get_Guid()

at Microsoft.Data.SqlClient.SqlDataReader.GetGuid(Int32 i)

at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext()`

at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable1 source)`

at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)

at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)

at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source)`

at cbs.classifications.dal.Views.FindCodeListById(Guid id) in ...

at cbs.classifications.services.CodeListService.GetCodeList(Guid id) in ...

at cbs.classifications.api.Controllers.CodeListController.Get(Guid id) in

at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)

at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)

at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<<InvokeActionMethodAsync>g__Logged|12_1>d.MoveNext()

Updated CodeLists class

public partial class CodeLists
{
    public CodeLists()
    {
        Levels = new HashSet<Levels>();
        Nodes = new HashSet<Nodes>();
    }

    public Guid Id { get; set; }
    public Guid ClassificationId { get; set; }
    public Guid? DescriptionId { get; set; }
    public Guid NameId { get; set; }
    public Guid OwnerId { get; set; }

    public Classifications Classification { get; set; }
    public Translations Description { get; set; }
    public Translations Name { get; set; }
    public Users Owner { get; set; }
    public ICollection<Levels> Levels { get; set; }
    public ICollection<Nodes> Nodes { get; set; }
}

I know you said you checked this, but are you absolutely sure every row has an I'd? — Crowcoder
– Crowcoder, Commented Jan 27, 2020 at 10:21
@Crowcoder All description id's and name id's are filled, classification and owner id are NULL, but they are allow to be null — Friso
– Friso, Commented Jan 27, 2020 at 10:22
Does this answer your question? Entity Framework Core: `SqlNullValueException: Data is Null.` How to troubleshoot? — Michael Freidgeim
– Michael Freidgeim, Commented Sep 6, 2023 at 11:31

Duck Ling · Accepted Answer · 2022-04-28 21:25:24Z

11

My problem was that I had the Nullable feature enabled in my Class library project. Removing the Nullable node fixed it. EFCore was no longer throwing an error and the warnings disappeared from my models.

  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

answered Apr 28, 2022 at 21:25

Duck Ling

2,23017 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Duck Ling Over a year ago

No worries Raju. I think .NET 6 class libraries have this enabled by default!

Raju Parmar Over a year ago

No, I have enabled it.

Diego Over a year ago

this fixed my problem as well!

oleksa · Accepted Answer · 2021-10-21 16:50:20Z

8

+50

you have to mark all nullable CodeLists properties with Nullable data types like below:

public class CodeLists
{
    public Guid Id { get; set; }
    public Guid? ClassificationId { get; set; }
    public Guid? DescriptionId { get; set; }

    public Guid NameId { get; set; }
    public Guid? OwnerId { get; set; }

}

ClassificationId and OwnerId are not nullable in your CodeLists class implementation but is nullable in the Db

edited Oct 21, 2021 at 16:50

answered Jan 27, 2020 at 10:27

oleksa

4,0761 gold badge37 silver badges59 bronze badges

1 Comment

Macros185 Over a year ago

This is especially important to remember when you're adding a new primitive property to an existing entity. We recently added a foreign key on an existing entity to a new table and forgot to assign values to this new column on old data. Querying this data where the foreign key was still null triggered this error. Took us a while to figure out this int was not nullable and was causing this error.

Yehor Androsov · Accepted Answer · 2023-12-19 11:35:41Z

1

Instead of removing Nullable feature, you can also change it to warnings only. This will stop errors from EF Core and allow you use nullability in your project.

<PropertyGroup>
    <Nullable>warnings</Nullable>
</PropertyGroup>

answered Dec 19, 2023 at 11:35

Yehor Androsov

6,1862 gold badges30 silver badges56 bronze badges

Collectives™ on Stack Overflow

How do I solve SqlNullValueException?

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related