Entity Framework - Handle null value in Linq

Question

I'm writing two LINQ queries where I use my first query's result set in my second query.

But in some cases when there is no data in the database table my first query returns null, and because of this my second query fails since wsdetails.location and wsdetails.worklocation are null causing an exception.

Exception:

Object reference not set to an instance of an object

My code is this:

        var wsdetails = (from assetTable in Repository.Asset
                         join userAsset in Repository.UserAsset on
                         assetTable.Asset_Id equals userAsset.Asset_Id
                         join subLocationTable in Repository.SubLocation on
                         assetTable.Sub_Location_Id equals subLocationTable.Sub_Location_ID
                         where userAsset.User_Id == userCode
                         && assetTable.Asset_TypeId == 1 && assetTable.Asset_SubType_Id == 1
                         select new { workstation = subLocationTable.Sub_Location_Name, location = assetTable.Location_Id }).FirstOrDefault();


            result = (from emp in this.Repository.Employee
                      join designation in this.Repository.Designation on
                      emp.DesignationId equals designation.Id
                      where emp.Code == userCode
                      select new EmployeeDetails
                      {                             
                          firstname = emp.FirstName,
                          lastname = emp.LastName,                              
                          designation = designation.Title,
                          LocationId = wsdetails.location,
                          WorkStationName = wsdetails.workstation
                      }).SingleOrDefault();

As a workaround I can check

if wsdetails == null

and change my second LINQ logic, but I believe there are some ways to handle null values in LINQ itself like the ?? operator.

But I tried this and it didn't work for me.

Any help?

James · Accepted Answer · 2012-08-22T13:13:54.513

The problem is EF can't translate the null-coalescing operator to SQL. Personally I don't see what's wrong with checking the result with an if statement before executing the next query. However, if you don't want to do that, then because your result is always going to be a single query why not do something like:

var wsdetails = (from assetTable in Repository.Asset 
                 join userAsset in Repository.UserAsset on 
                 assetTable.Asset_Id equals userAsset.Asset_Id 
                 join subLocationTable in Repository.SubLocation on 
                 assetTable.Sub_Location_Id equals subLocationTable.Sub_Location_ID 
                 where userAsset.User_Id == userCode 
                 && assetTable.Asset_TypeId == 1 && assetTable.Asset_SubType_Id == 1 
                 select new { workstation = subLocationTable.Sub_Location_Name, location = assetTable.Location_Id }).FirstOrDefault();  

result = (from emp in this.Repository.Employee 
          join designation in this.Repository.Designation on 
          emp.DesignationId equals designation.Id 
          where emp.Code == userCode 
          select new EmployeeDetails 
          {                              
              firstname = emp.FirstName, 
              lastname = emp.LastName,                               
              designation = designation.Title
           }).SingleOrDefault();

result.LocationId = wsdetails != null ? wsdetails.location : "someDefaultValue";
result.WorkStationName = wsdetails != null ? wsdetails.workstation ?? "someDefaultValue";

But if it's **`wsdetails`** that's `null`, then `wsdetails.location ?? "someDefaultValue"` will still explode. What we need here is not the `??` operator but the (until now non-existing) operator `.?` which "dots" cautiously (checking for `null` before member access). — Jeppe Stig Nielsen, Aug 22 '12 at 12:16
@JeppeStigNielsen oops that was a typo thanks, I did mean to use the standard ternary operator there. `.?` is not a valid C# operator. If you read the link you posted it was "*contemplated*" it was never implemented :) — James, Aug 22 '12 at 12:19

score 2 · Answer 2 · answered Aug 22 '12 at 11:59

2

Instead of the "binary" operator ?? maybe you should use the good old ternary one, ? :, like in

wsdetails != null ? wsdetails.location : null

answered Aug 22 '12 at 11:59

Jeppe Stig Nielsen

60,409
11
110
181

`??` is called the [null-coalescing operator](http://msdn.microsoft.com/en-us/library/ms173224) – James Aug 22 '12 at 12:02
See also [Stack Overflow: Shortcut for “null if object is null, or object.member if object is not null”](http://stackoverflow.com/questions/3817930/). – Jeppe Stig Nielsen Aug 22 '12 at 12:07

Maarten · Answer 3 · 2012-08-22T12:11:59.213

1

Try not evaluating the first query, and use it in the second query. This should result in a single SQL statement.

var wsdetails = (from assetTable in Repository.Asset
                         join userAsset in Repository.UserAsset on
                         assetTable.Asset_Id equals userAsset.Asset_Id
                         join subLocationTable in Repository.SubLocation on
                         assetTable.Sub_Location_Id equals subLocationTable.Sub_Location_ID
                         where userAsset.User_Id == userCode
                         && assetTable.Asset_TypeId == 1 && assetTable.Asset_SubType_Id == 1
                         select new { workstation = subLocationTable.Sub_Location_Name, location = assetTable.Location_Id });
// wsdetails is still an IEnumerable/IQueryable


        result = (from emp in this.Repository.Employee
                  join designation in this.Repository.Designation on
                  emp.DesignationId equals designation.Id
                  where emp.Code == userCode
                  select new EmployeeDetails
                  {                             
                      firstname = emp.FirstName,
                      lastname = emp.LastName,                              
                      designation = designation.Title,
                      LocationId = wsdetails.First().location,
                      WorkStationName = wsdetails.First().workstation
                  }).SingleOrDefault();

edited Aug 22 '12 at 12:11

answered Aug 22 '12 at 12:02

Maarten

22,527
3
47
68

You are still going to get the same problem as `wsdetails` can still be null so `wsdetails.First().location` will throw an exception. – James Aug 22 '12 at 12:04
Since wsdetails is an IQueryable it should be translated to SQL. – Maarten Aug 22 '12 at 12:09
What I mean is `wsdetails.First()` will return null, therefore, your code would still throw an NRE. From the OP's question "*But in some cases when there is no data in SQL table my first query returns `null`*" – James Aug 22 '12 at 12:10
2

No, the executed .First() is not IEnumerable.First() but IQueryable.First() so it should be translated to SQL, and be executed on the database in SQL. Which will be a JOIN with a SELECT TOP 1 or something. – Maarten Aug 22 '12 at 12:11
So what do you expect it to return if there is nothing there? – James Aug 22 '12 at 12:15
1

Then SQL will return a NULL value as the value for LocationId and WorkStationName (the properties in the anonymous type in the second linq query). – Maarten Aug 22 '12 at 12:16
Ah ok gotcha +1 wasn't aware of that. – James Aug 22 '12 at 12:17
@Maarten Thanks!! I just tried this and am getting another excep - The specified LINQ expression contains references to queries that are associated with different contexts. Repository.Designation is another db may be that resulted in this new excep – ssilas777 Aug 22 '12 at 12:45
1

Okay, yep, if it is another database, then EF cannot do it in one query. So my solution is not applicable in this case. One of the other answers should suffice. – Maarten Aug 22 '12 at 12:47

Entity Framework - Handle null value in Linq

3 Answers3