Solved

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

Posted on 2010-08-16
3
478 Views
Last Modified: 2013-12-17
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
Comment
Question by:markerasmus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
joriszwaenepoel earned 500 total points
ID: 33445515
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
 

Author Comment

by:markerasmus
ID: 33445724
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
 
LVL 10

Expert Comment

by:joriszwaenepoel
ID: 33445866
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question