[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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