Multiple JOIN in Linq-to-SQL

Question

I'm trying to do the following query in linq-to-sql (joining 3 different tables):

select * from tbl_round r 
inner join tbl_election e on r.fk_election_id = e.election_id
inner join tbl_meeting m on m.meeting_id = e.fk_meeting_id

Here is what I have so far but not correct:

from round in db.tbl_rounds
join meeting in db.tbl_meetings on election.fk_meeting_id equals meeting.meeting_id 
join election in db.tbl_elections on round.fk_election_id equals election.election_id 
select round;

The error I'm getting is that the name 'election' does not exist in the current context.

Answer 1

Because you have "election" used before it is declared.

from round in db.tbl_rounds
join meeting in db.tbl_meetings on -->election<--.fk_meeting_id equals meeting.meeting_id 
join -->election<-- in db.tbl_elections on round.fk_election_id equals election.election_id 
select round;

In this case, you will need to change order in your query.

Query should look like this:

from round in db.tbl_rounds
join election in db.tbl_elections on round.fk_election_id equals election.election_id
join meeting in db.tbl_meetings on election.fk_meeting_id equals meeting.meeting_id 
select round;

Answer 2

You will have to re-order the join statement probably like

from round in db.tbl_rounds
join election in db.tbl_elections on round.fk_election_id equals election.election_id
join meeting in db.tbl_meetings on election.fk_meeting_id equals meeting.meeting_id  
select round;