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?
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";
Instead of the "binary" operator ??
maybe you should use the good old ternary one, ? :
, like in
wsdetails != null ? wsdetails.location : null