simple LINQ query

Question

i am having trouble joinging tables on a LINQ query.

As you can see there are three tables there. On my webpage, i want to display the following data in a List View.

so let me explain this. I want 3 fields from the bets table. I want 2 fields from the match table (where the match.matchid = bet.matchid) I want 2 fields from the TEAM table (where match.teamone = team.teamid and match.teamtwo = team.teamid)

i hope this makes sense.

thankyou

score 3 · Accepted Answer · answered Jul 09 '09 at 01:41

It looks like you already have the relationships defined. If you are using the designer, you should have existing entities in the generated classes.

var result = bet.Select( b => new {
                 BetID = b.betid,
                 Title = b.title, 
                 Description = b.description,
                 BeginDate = b.match.begindate, 
                 EndDate = b.match.enddate, 
                 TeamOneName = b.match.team_1.teamname, 
                 TeamTwoName = b.match.team_2.teamname 
              } );

Note that I'm only guessing at the association names. You should be able to figure out the names given to them by the designer.

Rhys Jones · Answer 2 · 2009-07-09T01:28:59.937

The key to this one is to include a self join on the team table, for team one and team two, like this:

var result = from bet in db.bet  
  join match in db.match on bet.matchid equals match.matchid
  join team1 in db.team on match.teamone equals team1.teamid 
  join team2 in db.team on match.teamtwo equals team2.teamid
  select new {bet.betid, bet.title, bet.description, match.location, match.begindate,   match.enddate, team1.name, team2.name};

simple LINQ query

2 Answers2