asked on

How do you use LINQ to select foreign key fields that reside in the same foreign table?

I am building an application that lists football matches for a tournament. I have attachd the database diagram for further explanation.

I am trying to list all the matches in a grid but I am having difficulty retrieving the names of TeamA and TeamB from dw_Team. TeamA and TeamB's ID's are both foreign keys in the dw_Match table. You'll have to look at the database diagram for a cleared understanding.

The code I am currently using is only returning TeamA's name, and not team B. Can someone help me out with the LINQ/Lamba query that I need?

Thanks in advance.

var matches = from m in db.Matches
                      orderby m.Date
                      select new
                      {
                          ID = m.ID,
                          StageName = m.Stage.Name,
                          MatchDayTitle = m.MatchDay.Title,
                          Date = m.Date,
                          Time = m.Time,
                          TeamAName = m.Team.Name,
                          TeamBName = m.Team.Name
                      };

Open in new window

Database-Diagram.PNG

ASKER CERTIFIED SOLUTION

joriszwaenepoel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

markerasmus

ASKER

Apologies, that was just an oversight on my part. Both relationships are setup, they were just sitting on top of each other and I hadn't dragged them apart.

But you have answered my question. There was a m.dw_team object that I hadn't spotted that was holding a reference to TeamA. m.Team holds the value for TeamB.

Is there a way to control those object names e.g renaming them to TeamA and TeamB, instead of the default with having to edit the datacontext.cs?

joriszwaenepoel

Are you using Linq to SQL or Entity Framework (and which version)?

I know that with EF you can change the names of the generated properties, but you might need to modify that again if you regenerate the model later. I am not sure about LINQ to SQL.