Entity Framework Core: `SqlNullValueException: Data is Null.` How to troubleshoot?

Question

I am using Entity Framework Core in an ASP.NET Core application and Controller action and I haven't changed something to the working code nor to the database but I can't tell what is the query performed by Entity Framework Core.

The controller action:

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

The trace

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()

I even tried to do just var companies = await DB.Companies.ToListAsync(). and I have exactly the same exception.

Maybe I would like to be able to fetch the query performed by EF Core to do that manually so that I can try to figure out what's wrong with the query.

I am wondering what possibly could have happened. Especially since other entities like user or countries can still be fetched from the database.

Any idea how can I troubleshoot the actual underlying issue?

[EDIT]

The only that actually changed in the "code" are the nuget references, I basically upgraded pretty much everything although it didn't break a reference in the code it seems it change how EF Core is somehow (wild guess) interpreting the database.

I did reset my git repository to the point just before the nuget packages update happened and things work.,. I then decided to just update EntityFrameworkCore from 2.0.2 to 2.2.4 (also tried with 2.2.0 and ended up with the same result) and the issue happens again... Not sure what have changed between 2.0.2 and 2.2.0 to trigger this exception (but the model is the same just the EF Core version changed)...

Here is the entity definition, it seems to have been auto-generated with a tool from the database table / schema:

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

and the related table creation script:

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

[EDIT 2]

For the very same model, this (in the project file the nuget references) works

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

while those ones, no:

<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" />

Fireplug

Your EF model likely doesn't match what is in the database, unfortunately we can't help you fix it as we can't see both of those things. It's probably something in your model that is marked as not null, but the database is allowing nulls. – Fundament

Also, if you get the error from calling await DB.Companies.ToListAsync() then the entire rest of the code you posted is completely redundant :) – Fundament

@Fundament was for debugging purposes – Fireplug

@Fundament and there is no way to get a better trace or something more accurate? :s – Fireplug

What? You have an exception message, a trace won't help you. Give us the entity and table definition for the companies table. – Fundament

From the exception message we know that the property type is string. So check you Company class string properties marked as required. Then check the database table for enabling/having NULL in that column. – Thalassa

@Fundament see my update – Fireplug

@IvanStoev to me it looks more an issue with EF Core version... – Fireplug

If you want to know what EF is executing against your db, just log it: learn.microsoft.com/de-de/ef/core/miscellaneous/logging (or use SQL profiler) – Linebreeding

@Linebreeding I checked the query seems to be fine, but the mapping does no longer work after EF Core upgrade – Fireplug

Have you tried updating the model from your database? – Linebreeding

@Linebreeding not yet, the legacy codebase I inherited is kinda brittle, minor changes are sort of explosive. – Fireplug

That is what version control is for. Create a branch and if it blows up, switch back – Linebreeding

@Linebreeding I know and this is precisly how I found out the issue with the EF Core version – Fireplug

Answer 1

Update:

All I said previously still applies, with just additional ways of getting the things wrong due to the later EF Core nullability rules changes (most noticeable are the string properties inside NRT enabled projects which now are considered required by default, which is totally opposite of what they are considered in non NRT enable context).

What I would like to add here is the mechanism of detecting which column/property is causing the issue.

I kind of lose track of which exact EF Core version added such functionality, but in recent EF Core versions, you can use the EnableDetailedErrors method to turn detail error logging on, and then instead of the OP exception (it's still there, but as an inner of the new) you'll get InvalidOperationException with message similar to this

An error occurred while reading a database value for property '{EntityName}.{PropertyName}'. The expected type was 'System.String' but the actual value was null.

where {EntityName}.{PropertyName} is the offending property.

---

Original:

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.
If you are using Database First model, you should remove in generated OnModelCreating builder.Property(x => x.MyProperty).IsRequired(); or just regenerate the modal using EF Core Tools.

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.

Thalassa

Thanks this was actually the underlying issue ~~ didn't know that EF Core used to be more "tolerant" buggyish in early stage to the point of letting go of some column constraints. Thanks for pointing this out. – Fireplug

In my case, this error started to appear once I activated nullable reference types at project level, without making the necessary changes to my entity classes. Disabling the feature did the trick. – Publicist

THANK YOU for coming back and updating this by adding the mechanism -- super helpful. – Steinway

Thanks !!! And btw, using compiler, we can add it only in debug. (If you properly foemat code) : #if DEBUG .EnableDetailedErrors() #endif – Parcel

So helpful! I started getting this error this morning on a table that I didn't create and the EnabledDetailsErrors pointed me in the right direction. Cheers! – Hereditary

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.EnableDetailedErrors(); } – Suppurative

Answer 2

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.

Ribaldry

Related question/answer: https://mcmap.net/q/160465/-entity-framework-core-throws-sqlnullvalueexception-when-loading-entities-from-dbcontext/7108481 – Ribaldry

I like your "Note:" - it's the difference between what the customer asked for and what they actually want. – Clary

"Note: This is not the answer you wanted/needed, but is an answer to the req spec you gave us. Love, IT" – Freeborn

Sometimes you don't get what you want, but you get what you need. :) – Satinet

Answer 3

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

Chiropractor

In short words: With C# 8, MyProperty needs to be string? if it is a nullable value in the database. – Hersh

I spent 2 hours trying to figure out why EF was failing. Thanks, – Serpent

Answer 4

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

Toms

Doing this revealed that because the column in question for me was part of a composite key, it was automatically marked required by EF, which led to the problem. This didn't solve the problem but it revealed it.TVM – Poteen

I had to change the type string into string? – Circumstance

Answer 5

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!

Antihero

Thanks for your answer, this is absolutely fantastic! – Fireplug

Answer 6

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

Gratia

Yes this was the issue with my Self join with all such tables. Thank you @Gratia for saving my time. – Hewet

this answer should be the correct answer – Halo

Answer 7

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.

Merlon

Answer 8

   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

Alloy

This doesn't seem to be related to the question at all. Also, if it does, how does this significantly add something to existing answers? – Anett

Thank you @GertArnold ,while loads the data in Entity framework, it do mapping with model. if payload data is not match with data type it throws Exception. I have one case i have bit field in DB ,unfortunately it inserted null data. while loading the bit field Entity framework throws null ref exception , due to miss match of datatype and loaded datatype. – Alloy

Yes, and that was sufficiently explained by Ivan. Your answer doesn't add anything new. Also, it's not a null reference exception that is thrown. – Anett

Answer 9

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

Dietetics

Answer 10

I know its an old question, but just in case any one is still looking. Either change from

public string RRN { get; set; }

TO

public string? RRN { get; set; }

Or in the cproj file disable the nullable

 <Nullable>disable</Nullable>

Invite

Answer 11

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

Honoria

Answer 12

[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.

Lundberg

Answer 13

I fixed the issue by adding

modelBuilder.Entity<EntityName>().HasNoKey();

to DbContext method

protected override void OnModelCreating(ModelBuilder modelBuilder)

because my stored procedure returned null for Id property.

Boult

Answer 14

I just had to put (int?) for the c# property on the model and worked fine, since the Database column allows null.

Simplex

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Yerkovich

Answer 15

I had this same issue with a view. Big issue with SQL/SSMS is that the views do not get refreshed, so EF scaffold does not pick up the changes.

This code will refresh your view metadata, which allows EF to see the small changes like NULL/NOT NULL property changes when scaffolding.

-- Loop and refresh all views.
DECLARE @ViewName VARCHAR(100), @SchemaName VARCHAR(20);

DECLARE [views_cursor] CURSOR FOR 
SELECT v.[name] AS ViewName, s.[name] AS SchemaName 
FROM [sys].[views] v INNER JOIN [sys].[schemas] s ON s.[schema_id] = v.[schema_id]
WHERE v.[type] = 'V'
ORDER BY SchemaName, ViewName;

OPEN [views_cursor];

FETCH NEXT FROM [views_cursor] 
INTO @ViewName, @SchemaName;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        DECLARE @FullName VARCHAR(120) = @SchemaName + '.' + @ViewName;
        PRINT 'Refreshing: ' + @FullName
        EXEC sp_refreshview @FullName
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + @FullName
    END CATCH
    FETCH NEXT FROM [views_cursor] 
    INTO @ViewName, @SchemaName;
END

CLOSE [views_cursor];
DEALLOCATE [views_cursor];
GO

Granoff

Answer 16

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!

Urus

Hello Asif, I had the same issue. In my situation it was better to change from 'int' to 'int?' to allow null. The corresponding column in the database allowed nulls as well. The reason it worked for you is because strings are always nullable. – Symposiac

I changed all my view int -> int? and set HasNoKey and all my strings to .IsRequired(false); and I still get the SqlNullValueException. Is there some global flag I could set? BTW, ALL the rows/columns have valid data. So I have no null values/ – Influx

Answer 17

In modern EF Core versions, this is triggered by Nullable property in your csproj. It also means that you have some kind of field that is null in database, but is not nullable in C#.

If you don't want to disable Nullable feature, it is also possible to switch it from enable to warnings

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

Lignify

Answer 18

In my context, my Database wasn't synchronized with my models, I added a new migration to synchronize the two and updated my DB, the issue is fixed.

Cabala