如何解决SqlNullValueException? [英] How do I solve 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.QueryingEnumerable
1.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.QueryingEnumerable
1.Enumerator.MoveNext()`
,布尔值&找到)`
at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable
1 source, Boolean& found)`
在Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable
1.GetEnumerator()`
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable
1.GetEnumerator()`
在Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17
2.MoveNext()`
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17
2.MoveNext()`
在Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor
1.EnumeratorExceptionInterceptor.MoveNext()`
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor
1.EnumeratorExceptionInterceptor.MoveNext()`
,布尔值&找到)`
at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable
1 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_1
1.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.EntityFinder
1.Find(Object[] keyValues)`
在Microsoft.EntityFrameworkCore.Internal.InternalDbSet
1.Find(Object [] keyValues)`
at Microsoft.EntityFrameworkCore.Internal.InternalDbSet
1.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.QueryingEnumerable
1.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屋!