Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-08-16
3
Medium Priority
?
486 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

618 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