Linq Multiple Joins - Name Not In Scope

Posted by Sharpcnet under LINQ on 12/8/2013 | Points: 10 | Views : 6567 | Status : [Member] | Replies : 4
Here are the tables:
tblEmp: EMPID  EMPCODE GRADEID
-------------------------
1 c1 g1
2 c2 g2

tblGrade: GRADEID LEAVEID DAYS
-------------------------
g1 l1 10
g1 l2 20
g2 l1 30

tblLeave: LEAVEID LEAVECODE
------------------------
l1 al
l2 cl
l3 ml
I would like to get the leavecodes based on empcode. So the sql would be
select g.leaveid,l.leavecode,g.days
from tblemp e
join tblgrade g on e.gradeid = g.gradeid
join tblleave l on g.leaveid = l.leaveid
where e.empcode='c1'

Desired result:

LEAVEID LEAVETYPE DAYS
-------------------------
l1 al 10
l2 cl 20

This code gives an error - 'The name 'g' is not in scope on the left side of equals.Consider swapping the expressions on either side of equals.
public DataTable GetData()
{
var qry = from e in DB.tblemp.where(e=>e.empcode=='c1')
join g in DB.tblgrade on e.gradeid equals g.gradeid
join l in DB.tblleave on g.leaveid equals l.leaveid //error here at g.leaveid
select new
{
leaveid = g.leaveid,
leavecode = l.leavecode ?? string.empty,
days = g.days ?? 0.0
};
DataTable dt = new DataTable();
dt = qry.ToDataTable();
return dt;
}





Responses

Posted by: Bandi on: 12/8/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
just place l.leaveid equals g.leaveid
publicDataTableGetData(){ 

var qry =from e in DB.tblemp.where(e=>e.empcode=='c1') join g in DB.tblgrade on e.gradeid equals g.gradeid
join l in DB.tblleave on l.leaveid equals g.leaveid //error here at g.leaveid


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Bandi on: 12/8/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
public DataTable GetData() 

{

var qry = from e in DB.tblemp.where(e=>e.empcode=='c1')

join g in DB.tblgrade on e.gradeid equals g.gradeid

join l in DB.tblleave on l.leaveid equals g.leaveid //error here at g.leaveid

select new

{

leaveid = g.leaveid,

leavecode = l.leavecode ?? string.empty,

days = g.days ?? 0.0

};

DataTable dt = new DataTable();

dt = qry.ToDataTable();

return dt;

}


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

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

Up
0
Down
Tried that swap but no luck. So , using a stored procedure for now instead of linq....

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

Posted by: Bandi on: 12/11/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
what's the problem now?

I just provided error resolution.. Doesn't it resolve the error?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Login to post response