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

Answer 1

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