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-22 13:13:54Z

3

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

edited Aug 22, 2012 at 13:13

answered Aug 22, 2012 at 12:07

James

82.4k19 gold badges172 silver badges245 bronze badges

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

2 Comments

Jeppe Stig Nielsen Over a year ago

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

James Over a year ago

@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 :)

Jeppe Stig Nielsen · Accepted Answer · 2012-08-22 11:59:30Z

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, 2012 at 11:59

Jeppe Stig Nielsen

62.5k12 gold badges121 silver badges197 bronze badges

2 Comments

James Over a year ago

?? is called the null-coalescing operator

Jeppe Stig Nielsen Over a year ago

See also Stack Overflow: Shortcut for “null if object is null, or object.member if object is not null”.

Maarten · Accepted Answer · 2012-08-22 12:11:59Z

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, 2012 at 12:11

answered Aug 22, 2012 at 12:02

Maarten

23k3 gold badges51 silver badges69 bronze badges

9 Comments

James Over a year ago

You are still going to get the same problem as wsdetails can still be null so wsdetails.First().location will throw an exception.

Maarten Over a year ago

Since wsdetails is an IQueryable it should be translated to SQL.

Maarten Over a year ago

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 Over a year ago

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 Over a year ago

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.

|

Collectives™ on Stack Overflow

Entity Framework - Handle null value in Linq

3 Answers 3

2 Comments

2 Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related