Left Join in Linq gives an error [Resolved]

Posted by Sharpcnet under LINQ on 12/17/2013 | Points: 10 | Views : 942 | Status : [Member] | Replies : 4
Table : tblEmp 

EmpId EmpName ManagerId
101 john Null
102 jack 101
The foll. query gives an error that the materialized value is null. Where am i going wrong. Please note that the EmpId & ManagerId are actually GUIDs in table.

Using c#, linq to entities
public DataTable GetData(guid id)
{
var qry = from e in MYDB.tblEmps.where(e=>e.EmpId==id)
join m in MYDB.tblEmps on e.ManagerId equals m.EmpId into emp_mgr
from m in emp_mgr.DefaultIfEmpty()
select new
{
name = m.empname ?? string.empty,
id = e.managerid //also tried e.managerid ?? guid.empty
}
DataTable dt = qry.ToDataTable();
return dt;
}





Responses

Posted by: Sharpcnet on: 12/18/2013 [Member] Starter | Points: 25

Up
0
Down

Resolved
That doesnt compile because empid is guid and i cant pass an int. But thank you anyways.

Someone suggested this and it worked...

name = m!=null && m.empname!=null ? m.empname : string.empty,






Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 12/17/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi,
Why are you passing Guid as a parameter in function, you can pass integer or string instead of Guid as below:-

public DataTable GetData(int id)

{

var qry = from e in MYDB.tblEmps.where(e=>e.EmpId==id)

join m in MYDB.tblEmps on e.ManagerId = m.EmpId into emp_mgr

from m in emp_mgr.DefaultIfEmpty()

select new

{

name = m.empname ?? string.empty,

id = e.managerid //also tried e.managerid ?? guid.empty

}

DataTable dt = qry.ToDataTable();

return dt;

}


Now check.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 12/18/2013 [Member] Starter | Points: 25

Up
0
Down
"Please note that the EmpId & ManagerId are actually GUIDs in table. "

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 12/18/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Yes that is fine, but do you know, GUID value is changing again and again.
Did you try above code which i modified?

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response