EF generate System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values

Question

I would like to ask you what is wrong in this query.

    public async Task<RecipeHeader> GetRecipeWithRecipeLineChild(int id)
        {
            try
            {
                return await dbSet.Where(x => x.RecipeHeaderId == id)
                    .Include(x => x.RecipeLines)
                    .ThenInclude(x => x.Unit)
                    .Include(x => x.CalculationMethod)
                    .ThenInclude(y => y.Calculation)
                    .FirstOrDefaultAsync();            
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "{Repo} GetRecipeWithRecipeLineChild function error", typeof(RecipeHeaderRepository));
                return new RecipeHeader();
            }
        }

when I use .QueryString() and copy to AzureDataStudio it works.

but in my app generate

2022-05-19 13:38:39.3178|10100|ERROR|Microsoft.EntityFrameworkCore.Query|An exception occurred while iterating over the results of a query for context type 'RecipesApi.Data.AppDbContext'. System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. at lambda_method334(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator ) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext() System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. at lambda_method334(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator ) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext()

Here is Db model

    [Index(nameof(RecipeId),IsUnique =true)]
    public class RecipeHeader
    {
        
        [Key]
        public int RecipeHeaderId { get; set; }

        [MaxLength(15)]
        public string RecipeId { get; set; }
        [MaxLength(50)]
        public string RecipeName { get; set; } = "";
        public int? CalculationMethodId { get; set; }
        [ForeignKey("CalculationMethodId")]
        public virtual CalculationMethod CalculationMethod  { get; set; }

        [MaxLength(80)]
        public string Comment { get; set; }
        public int? PrescriptionId { get; set; }

        [ForeignKey("PrescriptionId")]
        public virtual Prescription Prescription { get; set; }
        public bool ColorRecipe { get; set; }
        public byte? Salt { get; set; }
        public byte? Program { get; set; }
        public ushort Version { get; set; }
        public DateTime CreatedDate { get; set; }
        public DateTime? UpdatedDate { get; set; }
        public string UpdatedBy { get; set; } = "system";
        public bool Active { get; set; }
        public byte RecipeStatus { get; set; }= 1;
        public virtual ICollection<RecipeLine> RecipeLines { get; set; }
    }

Firstly I assume there is some error in my ViewModel and AutoMapper. So I skip viewModel Automapper etc and use DB model but have same result... Now I'm little bit lost I think it will be some small stupid mistake but I cannot see it... Also here printscreen of my dbTable schema

Something wrong with your model. EF Core expects not null field, but retrieved NULL. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented May 19, 2022 at 12:48
You aren't using AutoMapper anywhere in your query. The error complains about the results, not the query. In EF Core 5 and later, an error is thrown if a null value is returned for a non-nullable property, even if it's a reference type. For example, if RecipeName is null, you'll get an error because the type is string, not string? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 19, 2022 at 12:49
To ensure you don't get such errors the nullability of the properties should map the table fields. That's annoying, because you won't get any compilation or runtime errors until a null is encountered. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 19, 2022 at 12:51
BTW both the RecipeName and Comment fields are nullable but the properties are string instead of string? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 19, 2022 at 12:52

LukasVanek · Accepted Answer · 2022-05-20 06:34:58Z

18

I fix it when I comment out  from .csproj

answered May 20, 2022 at 6:34

LukasVanek

3691 gold badge3 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jensen Over a year ago

Had similar issue migrating from 5.0 to 6.0 and setting nullable to disable fixed it. Commenting out does the same thing like @LukasVanek commented.

user14181068 Over a year ago

Had this issue when migrate from 3.1 to 6.0 too! and this fixed the issue!!

Greatran Over a year ago

I had the same issue when taking out ApplicationDbContext from the Asp.net core project to a separate class library. This solution saves my day..!!.

KramFfud Over a year ago

Over here, there are zero mis-matches b/t the nullability ("[Required]") of EF POCO model members and their respective database fields. So the fact that this beautiful little fix quashed our "SqlNullValueException" worries me a little. Why did .NET 5/6/7 introduce this problem in the 1st place? What else did I lose (read: "break") by dropping "<Nullable>enable..."? Do I need to do the same in EVERY dependent .csproj? ...even if that Project doesn't reference EF or the POCOs?

Spongebob Comrade Over a year ago

why this works ? 🧐

Nicholas Petersen · Accepted Answer · 2023-05-22 21:38:01Z

2

In my case, the problem was not that the entity and sql column mismatched in terms of nullability, as they did match, both were nullable. The problem was that the given property had a [Required] attribute on it. Removing that attribute fixed the problem. While it's true that it didn't make sense to mark a nullable property like this Required, nonetheless this was in a migration from legacy Entity Framework, which didn't mind the issue.

Entity:

[Required] // <-- problem! EFCore needs this removed
public DateTime? GracePeriodExpires { get; set; }

SQL:

[GracePeriodExpires] DATETIME2 NULL

answered May 22, 2023 at 21:38

Nicholas Petersen

9,6087 gold badges62 silver badges74 bronze badges

Comments

Eswar Kusumanchi · Accepted Answer · 2022-10-31 06:19:03Z

0

i resolved this issue by making column as notnull

and updated all the columns which null in database

after that it worked for me

thanks

answered Oct 31, 2022 at 6:19

Eswar Kusumanchi

11 bronze badge

Comments

dading84 · Accepted Answer · 2022-12-08 12:19:55Z

0

For me, and I think the OP, the issue was what Panagiotis Kanavos mentions in the comments.

It was due to a nullable field I was returning was being mapped to a non nullable field.

When the data returns a null value then you get the runtime error. The error message doesn't specify which field is causing the error.

Changing the offending field in my code from DateTime type to DateTime? to match the nullable field in the db schema resolved it for me.

answered Dec 8, 2022 at 12:19

dading84

1,21612 silver badges20 bronze badges

Collectives™ on Stack Overflow

EF generate System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related