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
markerasmusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

joriszwaenepoelCommented:
It looks like you have only defined 1 of the 2 relationships between Match and Team.
If you modify your database to include the 2nd relationship, then regenerate your LINQ model, then your "Match" objects should have 2 "Team" properties, one for TeamA and one for TeamB.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
markerasmusAuthor Commented:
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?  
0
joriszwaenepoelCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.