如何解决SqlNullValueException? [英] How do I solve SqlNullValueException?

查看：207
本文介绍了如何解决SqlNullValueException?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我试图找到一个实体，但是当实体框架试图获取Null字段的值时，我得到了SqlNullValueException.

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.

我检查了数据库，并填充了所有不可为空的字段.唯一具有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.

在其他答案中，我发现我应该从类属性中删除必需的属性，或者从modelbuilder定义中删除IsRequired方法，但是我自动生成了这些属性，并且它们从来没有必需的定义.

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.

我的应用程序在.NET Core 3.1上运行，而EntityFrameworkCore是2.2.4版.即使它成功构建，也可能导致此问题吗? 更新，所有版本均为3.1.

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

但是，出现以下异常:

System.Data.SqlTypes.SqlNullValueException

System.Data.SqlTypes.SqlNullValueException

HResult = 0x80131931

HResult=0x80131931

消息=数据为空.不能在Null值上调用此方法或属性.

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

Source = System.Data.Common

Source=System.Data.Common

StackTrace:

StackTrace:

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

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

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

在Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable 1.Enumerator.BufferlessMoveNext(DbContext _，Boolean buffer)`

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

在Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func 3操作下，功能3 verifySucceeded)

在Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable 1.Enumerator.MoveNext()`

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

，布尔值&找到)`

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

在Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable 1.GetEnumerator()`

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

在Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17 2.MoveNext()`

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

在Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor 1.EnumeratorExceptionInterceptor.MoveNext()`

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

，布尔值&找到)`

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

来自System.Linq.Enumerable.First[TSource](IEnumerable 1个源)"

在Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_1 1.b__0(QueryContext qc)`

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

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

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

在System.Linq.Queryable.FirstOrDefault[TSource](IQueryable 1源中，表达式1 predicate)

在Microsoft.EntityFrameworkCore.Internal.EntityFinder 1.Find(Object [] keyValues)`

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

在Microsoft.EntityFrameworkCore.Internal.InternalDbSet 1.Find(Object [] keyValues)`

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

在dal.Views.FindCodeListById(Guid id)在...

在services.CodeListService.GetCodeList(Guid id)在...

在api.Controllers.CodeListController.Get(Guid id)在...

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

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

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

我正在使用OnModelCreating(ModelBuilder modelBuilder)定义我的实体，没有在哪里定义我的列是必需的.

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

这是我的sql定义

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
)

更新 更新版本并添加nuget软件包System.Data.SqlClient后，堆栈跟踪已更改为:

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

System.Data.SqlTypes.SqlNullValueException

System.Data.SqlTypes.SqlNullValueException

HResult = 0x80131931

HResult=0x80131931

消息=数据为空.不能在Null值上调用此方法或属性.

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

Source = Microsoft.Data.SqlClient

Source=Microsoft.Data.SqlClient

StackTrace:

StackTrace:

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

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

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

在Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable 1.Enumerator.MoveNext()`

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

在System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable 1个源处)

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

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

在System.Linq.Queryable.FirstOrDefault[TSource](IQueryable 1个源中)`

在cbs.classifications.dal.Views.FindCodeListById(Guid id)在...

在cbs.classifications.services.CodeListService.GetCodeList(Guid id)在...

位于

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

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

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

已更新 CodeLists类

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

推荐答案

您必须将所有可为null的CodeLists属性标记为可为null的数据类型，如下所示:

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和OwnerId在CodeLists类实现中不可为空，但在Db中可为空

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

这篇关于如何解决SqlNullValueException?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆