• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 493
  • Last Modified:

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
0
markerasmus
Asked:
markerasmus
  • 2
1 Solution
 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now