MySQL Entity Framework With LinQ Correlated Query Not Executing

Question

I am using Visual Studio 2012 with MySQL 5.7 Community Edition. I am getting object reference not set to an instance of object on the below query.

var oList = (
    from dm in dbContext.document_master
    join um in dbContext.user_master on dm.Alocated_CAA equals um.UserId
    where (dm.DocumentHandle != null)
    select new TaskLogReport
    {
        documentDate = dm.Document_Date,
        documentHandle = dm.DocumentHandle,
        fileNumber = dm.FileNumber,
        statusRemarks = dbContext.statushistories
            .Where(x => x.DocumentHandle == 12345678).FirstOrDefault().Remarks
    }).ToList();

In the above query If I get change 12345678 to dm.DocumentHandle, then getting object reference not set to an instance object.

Try with MS-SQL server , working fine.

Answer 1

You get the error because FirstOrDefault() returns null, because there are no objects who's DocumentHandle equals dm.DocumentHandle. Changing dm.DocumentHandle to 12345678 works because there is one matching element.

This line is the problem:

.Where(x => x.DocumentHandle == 12345678).FirstOrDefault().Remarks

C#'s Enumerable.FirstOrDefault() method returns either the first matching element of an enumerable, or, if there aren't any, the default value of the type. In the case of nullable types, FirstOrDefault() returns null if no elements are found.

The reason you get no error when you use 12345678 is because there is at least one matching value with that DocumentHandle. However, when you change that to dm.DocumentHandle, no matching elements are found, causing FirstOrDefault to return null. Then you essentially do null.Remarks, which causes the error.

You should change your code to this:

.FirstOrDefault(x => x.DocumentHandle == dm.DocumentHandle)?.Remarks

There are two differences here:

1. Got rid of the where and moved the predicate to the FirstOrDefault call. This is just a cleaner way of doing what you were doing before.

2. Added the ? at the end of the FirstOrDefault call. That is the null propagation operator.

What it does is it turns this:

int foo;
if (bar != null)
{
    foo = bar.foo;
}

Into this:

int foo = bar?.foo;

Now, if there are matching elements, statusRemarks will be equal to the first one's Remarks. Otherwise, statusRemarks will be null.

EDIT: The null propagation operator was implemented in C# 6.0, which is .Net 4.6 onwards, so it won't work in .Net 4.0.

You'll have to modify your query and implement a check, like so:

var oList = (
    from dm in dbContext.document_master
    join um in dbContext.user_master on dm.Alocated_CAA equals um.UserId
    where (dm.DocumentHandle != null && dbContext.statushistories.Count(x => x.DocumentHandle == dm.DocumentHandle) > 0)
    select new TaskLogReport
    {
        documentDate = dm.Document_Date,
        documentHandle = dm.DocumentHandle,
        fileNumber = dm.FileNumber,
        statusRemarks = dbContext.statushistories.First(x.DocumentHandle == dm.DocumentHandle).Remarks
    }).ToList();