How to debug and fix 'Nullable object must have a value' within Entity Framework Core?

Question

I'm doing a projection in this method:

public async Task<TradeDetail> Get(int tradeId)
{
    var firstOrDefaultAsync = await context.EvgTGTrade
        .Where(x => x.IdTrade == tradeId)
        .Select(trade => new TradeDetail
        {
            Id = trade.IdTrade,
            Code = trade.CdTrade,
            Description = trade.DeTrade,
            Comments = trade.DeComentarios,
            TypeId = trade.IdTipoTrade,
            BrokerId = trade.EvgTGBrokerTrade.FirstOrDefault().IdBroker,
            BillingCycleId = trade.IdCicloFacturacion,
            CounterpartId = trade.IdSujeto,
            StartDate = trade.FhIni,
            EndDate = trade.FhFin,
            SignatureDate = trade.FhFirma,
            Positions = trade.EvgTGPosicion.Select(pos => new Position
            {
                Amount = pos.Cantidad,
                Code = pos.CdPosicion,
                Description = pos.DePosicion,
                LogisticElement = pos.IdElemLogNavigation.DeElemLog,
                StartDate = pos.FhIni,
                EndDate = pos.FhFin,
                FormulaId = pos.IdFormula,
                Operations = pos.EvgTGOperacionCv.Select(op =>
                    new Operation()
                    {
                        TypeId = op.IdTipoOperacion,
                        Fee = op.Fee,
                        Date = op.FhOperacionCv,
                        Price = op.NmPrecio ?? 0,
                        Quantity = op.NmCantidadKwh ?? 0,
                    }),
            })
        })
        .FirstOrDefaultAsync();
    return firstOrDefaultAsync;
}

I'm getting an exception at the first line saying

Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type 'SampleApp.EF.MyDbContext'. System.InvalidOperationException: Nullable object must have a value.

I've inspected the exception and the stack trace and it doesn't say anything about which is the problematic object. No clue of what's wrong.

So, is there a method to know which property generates the conflict? I'm sure it's a property-column mismatch, but what if you have a a lot of properties? It's quite painful!

Update

I've already fixed the problem. It was due to an attempt of assigning null to a property in my model that was non-nullable.

That said, this questions isn't about this concrete fix, but about finding a general way to debug this kind of situation (this exception) that are quite common. IMHO, the exception message is too vague. It doesn't provide any useful information of the conflicting property.

Gentille

have you tried another perspective? I mean, if you tried to debug the code unsuccessfully, why don't you try to see what is going on in the database checking the logs or profiler? – Eldin

Do you have EF Core 3.1 ? – Familiar

Please have a look at issue. If you cast the object properties to nullable types, then this issue should get fixed as per this github thread. – Familiar

@ManojChoudhari Yes, it's EF Core 3.1 – Gentille

Did you already try typecasting to nullable objects for all properties? e.g. (int?)op.Id – Familiar

I've discovered the conflicting property. The model property is non-nullable and the query can return null. But I have found it commenting line by line until it worked. It's so time consuming! – Gentille

The problem is not fixing it (I've fixed it already), but HOW to find the problem quickly, since the exception doesn't provide any useful information about it. – Gentille

I agree, I am not sure currently if there is any quicker way to find which property is causing the issue. For now, I have added answer as per our discussion. At least it should help a bit to people who are facing similar issue. – Familiar

Ridiculously bad error message. Literally on the order of "water is wet." Worse, actually, because that should be non-nullable objects. – Whole

Answer 1

There can be two answers.

Firstly, this may be probably because your query is returning NULL and the model is not accepting null.

Second fix may be, if you are using anonymous types, then try typecasting the query result into nullable type.

e.g.

Id = (int?) op.Id,

how to find which property is returning NULL?

You can enable SQL Profiler and check the SQL query which is getting executed. Copy the query into SSMS and see which values are NULL.
This may be helpful for you to decide out if you really need nullable typecast OR you want to change the query itself.

There is an issue on GitHub on this page.

Update: 05-July-2021: How to find which property is returning NULL ?

You can use simple logging feature (introduced in EF core 5.0) by calling EnableDetailedErrors method. Below code example shows how to configure Simple Logging for showing logs on Console and it also enables detailed errors. If you do not want to show logs on console, then you can pass an action delegate in LogTo call, which accepts a string as parameter. This delegate can then write logs to any destination of your choice.

Refer documentation for more details.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .LogTo(Console.WriteLine)
        .EnableDetailedErrors();

Familiar

An easier way to get the SQL is to split the assignment of the query results into two steps. The first is the Task that describes the query - assign that to a temporary variable. Then as a second step, assign the final results (the result of awaiting a call to FirstOrDefaultAsync on your temporary) to the variable you will be returning. Debug your program, stepping through all this. Examine your temporary variable after the call to FirstOrDefaultAsync, you should see the SQL in one of the properties – Eared

Answer 2

I want to add to @Manoj-Choudhari's answer which helped me track down an issue which started after I upgraded from EF Core 3.x to EF Core 5.x.

I had a query like this: (Item[1] -> Categories[0..n] -> Parent[1], keys are ints)

from item in ctx.Items
from ixc in item.Categories.DefaultIfEmpty() /* Left join */
let parent = ixc.Parent
select new {
    itemId = item.Id,
    parentId = parent.Id
}

This fails with the same "Nullable object must have a value" error if the navigation to "parent" ends up null. What I observed different in behavior between EFC3 and EFC5 is the parent.Id part of the projection used to be automatically inferred as int? because it seemed to understand parent could be null. Seems you have to be more explicit with EF Core 5 and actually cast the mapped types to the nullable type manually.

from item in ctx.Items
from ixc in item.Categories.DefaultIfEmpty() /* Left join */
let parent = ixc.Parent
select new {
    itemId = item.Id,
    parentId = (int?)parent.Id
}

The old way was probably more of an exploit, and its better to be specific now.

Edit: The EF Core team is aware of this behavior: https://github.com/dotnet/efcore/issues/22517

Unrig

Answer 3

Usually, as far as I know, it doesn't have a quicker way to find what is the exactly field that is making the issue.

What you can do looking at your case, is:

1 - Look the query that you are doing, and verify if it is returning the data needed. If that it is happening well, so probably the query could return null, and your model doesn't accept that (and vice-versa).

2 - If the first item is working properly, try to look at the other iterations that you are doing on the model. For example: you are doing Positions.Select(), so verify if the positions, and the other things coming from the positions model are all fine.

3 - If you are not a hundred per cent sure about what is the field. Just put a nullable type for every field you are doing a linq. Example: BrokerId = trade.EvgTGBrokerTrade.FirstOrDefault().IdBroker, you can add a question mark to make sure that the field is not going to crash your application.

4 - Look at the tables that you create on the database, probably it will have the information of the fields if they are nullable or not. Then, you can alter on your model of the code.

These are the things that I suggest for you

Posology

has this improved in any way since then? still only getting the message Nullable object must have a value, and don't have a clue of what field it is when trying to debug code with a long query. – Applicator

Answer 4

For that and some other ef exceptions I often use https://www.linqpad.net/

1. Setup database connection in linqpad
2. Copy trouble query
3. Execute
4. If see errors, remove some part of the query and repeat step 3

Once I have found the part of the query which leads to troubles it usually rather easy to find the fix.

Ioannina

Answer 5

In my case, I had to change LINQ statements for properties based on enums

private readonly Enum _value;

from

x => x.EnumProperty == _value;

to

x => x.EnumProperty.Equals(_value);

Dont really get why, seeing as my logic was working correct in the application with the old Linq, but the test failed until I used .Equals. Only difference in environment is MSSqlDb vs InMemoryDb

Idiom