实体框架核心: “ SqlNullValueException: Data is Null.”如何排除故障？

我使用实体框架核心在 ASP.NET 核心应用程序和控制器的行动，我没有改变一些工作代码或数据库，但我不能告诉什么是查询执行的实体框架核心。

控制器操作:

[HttpGet]
// GET: Administration/Companies
public async Task<ActionResult> Index()
{
var users = await UserManager.Users.ToListAsync();


var companyEditVMs = await DB.Companies
.OrderBy(company => company.CompanyId == 1
? "_" + company.CompanyName
: company.CompanyName
)
Select(a => new CompanyEditVM(HttpContext, a, users.Where(b => b.CompanyId == a.CompanyId)))
.ToListAsync();


return View(companyEditVMs);
}

追踪

SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlClient.SqlBuffer.get_String()
System.Data.SqlClient.SqlDataReader.GetString(int i)
lambda_method(Closure , DbDataReader )
Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable<T>+AsyncEnumerator.BufferlessMoveNext(DbContext _, bool buffer, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync<TState, TResult>(TState state, Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable<T>+AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider+ExceptionInterceptor<T>+EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
System.Linq.AsyncEnumerable.Aggregate_<TSource, TAccumulate, TResult>(IAsyncEnumerable<TSource> source, TAccumulate seed, Func<TAccumulate, TSource, TAccumulate> accumulator, Func<TAccumulate, TResult> resultSelector, CancellationToken cancellationToken) in Aggregate.cs
KYC.Core.Areas.Commercial.Controllers.CompaniesController.Index() in CompaniesController.cs
-
[HttpGet]
// GET: Administration/Companies
public async Task<ActionResult> Index()
{
var users = await UserManager.Users.ToListAsync();


var companyEditVMs = await DB.Companies
.OrderBy(company => company.CompanyId == 1
? "_" + company.CompanyName
: company.CompanyName
)
.Select(a => new CompanyEditVM(HttpContext, a, users.Where(b => b.CompanyId == a.CompanyId)))
.ToListAsync();
lambda_method(Closure , object )
Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable+Awaiter.GetResult()
Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor+TaskOfActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
System.Threading.Tasks.ValueTask<TResult>.get_Result()

我甚至尝试只做 var companies = await DB.Companies.ToListAsync()。我有完全相同的例外。

也许我希望能够获取由 EF Core 执行的查询来手动完成这项工作，这样我就可以尝试找出查询出了什么问题。

我想知道到底发生了什么。特别是因为其他实体，如用户或国家仍然可以从数据库中提取。

知道如何对实际的潜在问题进行故障排除吗？

[编辑]

唯一实际上改变了“代码”的是 nuget 引用，我基本上升级了几乎所有的东西，虽然它没有破坏代码中的引用，它似乎改变了 EF Core 是如何解释数据库的(大胆猜测)。

我确实重新设置了我的 git 存储库，就在 nget 包更新发生之前，一切正常。然后我决定把 EntityFrameworkCore 从2.0.2升级到2.2.4(也试过2.2.0，结果是一样的) ，问题又发生了... ... 不知道2.0.2和2.2.0之间发生了什么变化来触发这个异常(但模型是一样的，只是 EF Core 版本发生了变化) ... ..。

下面是实体定义，它似乎是通过数据库表/模式中的一个工具自动生成的:


[Table("T_Companies")]
public partial class Company : INotifyPropertyChanging, INotifyPropertyChanged
{
public override string ToString()
{
return CompanyId + " " + CompanyName;
}


private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(string.Empty);


private int _CompanyId;


private string _CompanyName;


private int _CompanyTypeId;


private int _CountryId;


private string _CompanyVatNumber;


private string _CompanyStreetAddress;


private string _CompanyZipCode;


private string _CompanyCity;


private string _ContactLastName;


private string _ContactFirstName;


private bool? _Active;


private int? _AccountId;


private string _CallbackSalt;


private int? _UserSpaceId;


private string _Login;


private string _Pwd;


private bool _IsTechnicalAccount;


private DateTime? _StatusDate;


private int _BankStatusCode;


private string _PivotalAccount;


private CompanyType _CompanyType;


private Country _Country;


private bool _IsKycIdSent;


#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnCreated();
partial void OnCompanyIdChanging(int value);
partial void OnCompanyIdChanged();
partial void OnCompanyNameChanging(string value);
partial void OnCompanyNameChanged();


partial void OnCompanyCityChanging(string value);
partial void OnCompanyCityChanged();
partial void OnCompanyZipCodeChanging(string value);
partial void OnCompanyZipCodeChanged();
partial void OnContactLastNameChanging(string value);
partial void OnContactLastNameChanged();
partial void OnActiveChanging(bool? value);
partial void OnActiveChanged();
partial void OnCompanyTypeIdChanging(int value);
partial void OnCompanyTypeIdChanged();
partial void OnCountryIdChanging(int value);
partial void OnCountryIdChanged();
partial void OnContactFirstNameChanging(string value);
partial void OnContactFirstNameChanged();
partial void OnCompanyVatNumberChanging(string value);
partial void OnCompanyVatNumberChanged();
partial void OnCompanyStreetAddressChanged();
partial void OnCompanyStreetAddressChanging(string value);
partial void OnAccountIdChanging(int? value);
partial void OnAccountIdChanged();
partial void OnCallbackSaltChanging(string value);
partial void OnCallbackSaltChanged();
partial void OnUserSpaceIdChanging(int? value);
partial void OnUserSpaceIdChanged();
partial void OnLoginChanging(string value);
partial void OnLoginChanged();
partial void OnPwdChanging(string value);
partial void OnPwdChanged();
partial void OnIsTechnicalAccountChanging(bool value);
partial void OnIsTechnicalAccountChanged();
partial void OnStatusDateChanging(DateTime? value);
partial void OnStatusDateChanged();
partial void OnBankStatusCodeChanging(int value);
partial void OnBankStatusCodeChanged();
partial void OnPivotalAccountChanging(string value);
partial void OnPivotalAccountChanged();
partial void OnIsKycIdSentChanging(bool value);
partial void OnIsKycIdSentChanged();


#endregion


public Company()
{
OnCreated();
}


[Key, Column("CompanyId"), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int CompanyId
{
get => _CompanyId;
set
{
if (_CompanyId != value)
{
OnCompanyIdChanging(value);
SendPropertyChanging();
_CompanyId = value;
SendPropertyChanged("CompanyId");
OnCompanyIdChanged();
}
}
}


[DataType(DataType.Text), StringLength(1024), Column("CompanyName"), Required]
public string CompanyName
{
get => _CompanyName;
set
{
if (_CompanyName != value)
{
OnCompanyNameChanging(value);
SendPropertyChanging();
_CompanyName = value;
SendPropertyChanged("CompanyName");
OnCompanyNameChanged();
}
}
}


[Column("CompanyTypeId"), Required]
public int CompanyTypeId
{
get => _CompanyTypeId;
set
{
if (_CompanyTypeId != value)
{
OnCompanyTypeIdChanging(value);
SendPropertyChanging();
_CompanyTypeId = value;
SendPropertyChanged("CompanyTypeId");
OnCompanyTypeIdChanged();
}
}
}




[Column("CountryId"), Required]
public int CountryId
{
get => _CountryId;
set
{
if (CountryId != value)
{
OnCountryIdChanging(value);
SendPropertyChanging();
_CountryId = value;
SendPropertyChanged("CountryId");
OnCountryIdChanged();
}
}
}




[DataType(DataType.Text), StringLength(100), Column("CompanyCity"), Required]
public string CompanyCity
{
get => _CompanyCity;
set
{
if (_CompanyCity != value)
{
OnCompanyCityChanging(value);
SendPropertyChanging();
_CompanyCity = value;
SendPropertyChanged("CompanyCity");
OnCompanyCityChanged();
}
}
}


[DataType(DataType.Text), StringLength(100), Column("CompanyStreetAddress"), Required]
public string CompanyStreetAddress
{
get => _CompanyStreetAddress;
set
{
if (_CompanyStreetAddress != value)
{
OnCompanyStreetAddressChanging(value);
SendPropertyChanging();
_CompanyStreetAddress = value;
SendPropertyChanged("CompanyStreetAddress");
OnCompanyStreetAddressChanged();
}
}
}


[DataType(DataType.Text), StringLength(30), Column("CompanyVatNumber"), Required]
public string CompanyVatNumber
{
get => _CompanyVatNumber;
set
{
if (_CompanyVatNumber != value)
{
OnCompanyVatNumberChanging(value);
SendPropertyChanging();
_CompanyVatNumber = value;
SendPropertyChanged("CompanyVatNumber");
OnCompanyVatNumberChanged();
}
}
}




[DataType(DataType.Text), StringLength(10), Column("CompanyZipCode"), Required]
public string CompanyZipCode
{
get => _CompanyZipCode;
set
{
if (_CompanyZipCode != value)
{
OnCompanyZipCodeChanging(value);
SendPropertyChanging();
_CompanyZipCode = value;
SendPropertyChanged("CompanyZipCode");
OnCompanyZipCodeChanged();
}
}
}




[DataType(DataType.Text), StringLength(1024), Column("ContactLastName"), Required]
public string ContactLastName
{
get => _ContactLastName;
set
{
if (_ContactLastName != value)
{
OnContactLastNameChanging(value);
SendPropertyChanging();
_ContactLastName = value;
SendPropertyChanged("ContactLastName");
OnContactLastNameChanged();
}
}
}


[DataType(DataType.Text), StringLength(1024), Column("ContactFirstName"), Required]
public string ContactFirstName
{
get => _ContactFirstName;
set
{
if (_ContactFirstName != value)
{
OnContactFirstNameChanging(value);
SendPropertyChanging();
_ContactFirstName = value;
SendPropertyChanged("ContactFirstName");
OnContactFirstNameChanged();
}
}
}


[Column("Active"), Required]
public bool? Active
{
get => _Active;
set
{
if (_Active != value)
{
OnActiveChanging(value);
SendPropertyChanging();
_Active = value;
SendPropertyChanged("Active");
OnActiveChanged();
}
}
}


[Column("AccountId")]
public int? AccountId
{
get => _AccountId;
set
{
if (_AccountId != value)
{
OnAccountIdChanging(value);
SendPropertyChanging();
_AccountId = value;
SendPropertyChanged("AccountId");
OnAccountIdChanged();
}
}
}


[DataType(DataType.Text), StringLength(1024), Column("CallbackSalt")]
public string CallbackSalt
{
get => _CallbackSalt;
set
{
if (_CallbackSalt != value)
{
OnCallbackSaltChanging(value);
SendPropertyChanging();
_CallbackSalt = value;
SendPropertyChanged("CallbackSalt");
OnCallbackSaltChanged();
}
}
}


[Column("UserSpaceId")]
public int? UserSpaceId
{
get => _UserSpaceId;
set
{
if (_UserSpaceId != value)
{
OnUserSpaceIdChanging(value);
SendPropertyChanging();
_UserSpaceId = value;
SendPropertyChanged("UserSpaceId");
OnUserSpaceIdChanged();
}
}
}


[DataType(DataType.Text), StringLength(1024), Column("Login")]
public string Login
{
get => _Login;
set
{
if (_Login != value)
{
OnLoginChanging(value);
SendPropertyChanging();
_Login = value;
SendPropertyChanged("Login");
OnLoginChanged();
}
}
}


[DataType(DataType.Text), StringLength(1024), Column("Pwd")]
public string Pwd
{
get => _Pwd;
set
{
if (_Pwd != value)
{
OnPwdChanging(value);
SendPropertyChanging();
_Pwd = value;
SendPropertyChanged("Pwd");
OnPwdChanged();
}
}
}


[Column("IsTechnicalAccount"), Required]
public bool IsTechnicalAccount
{
get => _IsTechnicalAccount;
set
{
if (_IsTechnicalAccount != value)
{
OnIsTechnicalAccountChanging(value);
SendPropertyChanging();
_IsTechnicalAccount = value;
SendPropertyChanged("IsTechnicalAccount");
OnIsTechnicalAccountChanged();
}
}
}


[DataType(DataType.DateTime), Column("StatusDate")]
public DateTime? StatusDate
{
get => _StatusDate;
set
{
if (_StatusDate != value)
{
OnStatusDateChanging(value);
SendPropertyChanging();
_StatusDate = value;
SendPropertyChanged("StatusDate");
OnStatusDateChanged();
}
}
}


[Column("BankStatusCode")]
public int BankStatusCode
{
get => _BankStatusCode;
set
{
if (_BankStatusCode != value)
{
OnBankStatusCodeChanging(value);
SendPropertyChanging();
_BankStatusCode = value;
SendPropertyChanged("BankStatusCode");
OnBankStatusCodeChanged();
}
}
}


[DataType(DataType.Text), StringLength(255), Column("PivotalAccount")]
public string PivotalAccount
{
get => _PivotalAccount;
set
{
if (_PivotalAccount != value)
{
OnPivotalAccountChanging(value);
SendPropertyChanging();
_PivotalAccount = value;
SendPropertyChanged("PivotalAccount");
OnPivotalAccountChanged();
}
}
}


public List<Resultat> Resultats { get; set; }


public CompanyType CompanyType
{
get => _CompanyType;
set
{
var previousValue = _CompanyType;
if (previousValue != value)
{
SendPropertyChanging();
_CompanyType = value;
if (value != null)
{
CompanyTypeId = value.CompanyTypeId;
}
else
{
_CompanyTypeId = default;
}
SendPropertyChanged("CompanyType");
}
}
}


public Country Country
{
get => _Country;
set
{
var previousValue = _Country;
if (previousValue != value)
{
SendPropertyChanging();
_Country = value;
_CountryId = value?.CountryId ?? default;
SendPropertyChanged("Country");
}
}
}


[Column("IsKycIdSent"), Required]
public bool IsKycIdSent
{
get => _IsKycIdSent;
set
{
if (_IsKycIdSent != value)
{
OnIsKycIdSentChanging(value);
SendPropertyChanging();
_IsKycIdSent = value;
SendPropertyChanged("IsKycIdSent");
OnIsKycIdSentChanged();
}
}
}


public event PropertyChangingEventHandler PropertyChanging;


public event PropertyChangedEventHandler PropertyChanged;


protected virtual void SendPropertyChanging()
{
PropertyChanging?.Invoke(this, emptyChangingEventArgs);
}


protected virtual void SendPropertyChanged(string propertyName)
{
PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
}


private void Attach_Resultats(Resultat entity)
{
SendPropertyChanging();
entity.Company = this;
}


private void Detach_Resultats(Resultat entity)
{
SendPropertyChanging();
entity.Company = null;
}
}

以及相关的表创建脚本:

create table T_Companies
(
CompanyId            int identity (10) identity
primary key nonclustered,
CompanyName          varchar(1024) not null,
CompanyTypeId        int           not null
constraint FK_Company_CompanyType
references T_CompanyTypes,
CountryId            int           not null
constraint FK_Company_Country
references T_Countries,
Active               bit           not null,
AccountId            int,
CallbackSalt         varchar(1024),
UserSpaceId          int,
Login                varchar(1024),
Pwd                  varchar(1024),
StatusDate           datetime(23, 3),
BankStatusCode       int           not null,
PivotalAccount       varchar(255),
IsTechnicalAccount   bit           not null,
CompanyStreetAddress varchar(256),
CompanyCity          varchar(256),
CompanyZipCode       varchar(10),
CompanyVatNumber     varchar(30),
ContactFirstName     varchar(20),
ContactLastName      varchar(20),
IsKycIdSent          bit           not null
)
go

[编辑2]

对于非常相同的模型，这(在项目文件中的 nuget 引用)工作

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.1.8" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.8" />

而那些人，没有:

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.2.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.2.0" />

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.2.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.8" />
135587 次浏览
小开
最佳答案

The error message indicates that EF Core is trying to read string value for a required property, i.e. a property which should never have null value in the database, but instead the underlying data reader reports null value for that property in some record(s).

Looking at your entity model and corresponding database table, you can see the obvious discrepancy for many string properties -> varchar columns. CompanyStreetAddress, CompanyCity, CompanyZipCode, CompanyVatNumber, ContactFirstName, ContactLastName - all these are marked as [Required] in the model, but have no corresponding not null constraint in the table.

So the problem is caused by one or more of these columns.

You need to fix that discrepancy - probably by removing [Required] attribute because the constraint is already broken in the existing data.

The fact that it "works" in some older EF Core version doesn't matter - that's incorrect mapping and as such should be fixed. Technically it shouldn't work from the beginning. But remember that EF Core is still in active development and has many bugs which are fixed in the next release(s). Most likely some code change was made between "working" and "non working" EF Core version which fixes the previous incorrect behavior.

小开

If you are trying to read some nullable data from the database, but your type is not nullable you can get this error.

If MyInt is nullable in the database and you have this entity:

public class MyEntity
{
public int Id { get; set; }
public int MyInt { get; set; }
}

You will get the exception: System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.'

To fix this, just change the type of your MyInt property to Nullable<int> or int?:

public class MyEntity
{
public int Id { get; set; }
public int? MyInt { get; set; }
}

Note: This is not an answer to the original question, but is an answer to the question in the title.

小开 
   public bool Isgstvailable { get; set; }

check the bit column in sql view or table. if it returns null , c# readers can't able to read and throws null ref exception.

case when gst.Isgstvailable is null then cast(0 as bit) else gst.Isgstvailable end as Isgstvailable

小开

This kind of exception can also appear if you enable the latest Nullable feature from C# 8.

EF Core, at least currently, it's not fully compatible with C# 8 nullable types. So for example, given that you enable the Nullable feature for the project if you have a type like this:

public class MyEntity
{
public string MyProperty { get; set; }
}

even if the property is not marked with the [Required] attribute, EF core raises this kind of exception because it requires that value in database isn't null (i.e. it doesn't test the column value with IsDbNull).

For further info on how to handle nullable reference types in EF core take a look at: https://learn.microsoft.com/en-us/ef/core/miscellaneous/nullable-reference-types

小开

To fix similar issue with Data is Null exception, I had to explicitly put IsRequired(false) on my column mapping. In my case I was mapping a database view.

builder.Property(x => x.MyProperty).IsRequired(false);
小开

SOLUTION: Yes, the error "SqlNullValueException: Data is Null." is caused when a column (of a table) contains NULL when the model has marked the field causing the problem as [Required]... the problem is resolved easily with numeric fields but is really bad when the field is of type string...

Think about the following 2 classes that use the example of a distribution route, each route has a driver, and of course, each driver then has 1 or more routes.

public class Route
{
public int id { get; set; }
public string RouteName { get; set; }


[Required] \\==> FIELD CAUSING THE PROBLEM
public string UsuarioId { get; set; }


public virtual Usuario Driver { get; set; }
}


public class Usuario
{
public string Id {get;set;
public string Name { get; set; }
public virtual List<Route> Routes { get; set; } = new List<Route>();


}

As you may guess, a Route can have a single driver (or Usuario as I have called it), but a Driver can have multiple routes, this makes a one-to-many relationship as follow:

protected override void OnModelCreating(ModelBuilder modelBuilder){


base.OnModelCreating(modelBuilder);


modelBuilder.Entity<Usuario>()
.HasMany<Route>(usuario => usuario.Routes)
.WithOne(route => route.Driver)
.HasForeignKey(route => route.UsuarioId)
.OnDelete(DeleteBehavior.SetNull);
                

}

According to the FluentAPI, when a driver is deleted the behavior is to set the UsuarioId field (in Routes model) to NULL, but this violates the [Required] attribute. So we will not be able to delete a record in the Users (or Drivers) table. Also, if we have data already in the database, in this case, Routes with NULL UsuarioId, it will present the error immediatly.

Remember, the point is: We want the [Required] to be used only for validation purposes, as we could have Routes that have not been assigned to a Driver (UserId) yet, so in the database, it should be allowed to be NULL, not in our mvc forms.

To resolve the issue, mark the field as [required], then go to the DbContext class where you did define the relationships using fluent API, and do specify that the column is not required like this:

modelBuilder.Entity<Route>()
.Property(p => p.UsuarioId).IsRequired(required: false);

so the field is now required under the annotation schema but not required by the fluent API which will be ultimately used to build the database. This will resolve the problem!

小开

Not the OP case, but this can also be related to inverted Principal Key <> Foreign Key configuration in a relationship.

For instance:

entity
.HasOne(e => e.Principal)
.WithOne(e => e.Dependent)
.HasForeignKey<Principal>(e => e.Key) <- This should be the dependent entity
.HasPrincipalKey<Dependent>(e => e.Key); <- This should be the principal entity

instead of

entity
.HasOne(e => e.Principal)
.WithOne(e => e.Dependent)
.HasForeignKey<Dependent>(e => e.Key)
.HasPrincipalKey<Principal>(e => e.Key);
小开

Just wanted to post this here;

The solutions on here are great however it did not work in my case. I was using a SQL view instead of a table and non of my fields had the [Required] tagged on them in the model.

After a bit of troubleshooting, I found that the issue came from my integer columns being null. I simply changed my int column in my model to a string (I needed this info for display purposes only) and it did the trick.

From

public int BatchId { get; set; }

To

public string BatchId { get; set; }

I hope this helps someone. Thanks for all the answers above!

小开

[using isRequired()][1] I had fixed my problem by defining all properties of the table in db context (genering by scafford) which is required or not.

小开

remove [Required] from the model.. and use : < input data-val="true" data-val-required="write a message for validation" > in cshtml

小开

If you want to ignore those values, adding DefaultIfEmpty() works.

_context.<YourDbSet>.DefaultIfEmpty().ToListAsync();

In this case:

var companyEditVMs = await DB.Companies
.OrderBy(company => company.CompanyId == 1
? "_" + company.CompanyName
: company.CompanyName
)
Select(a => new CompanyEditVM(..--..)).DefaultIfEmpty()
.ToListAsync();
小开

For my case it was due to me altering the column in database from not nullable to nullable, and then forgetting that I need to rerun Scaffold-DbContext again so that the entity classes gets updated. I am using database-first Entity Framework Core.


提交答案