Solved

How do you query a many-to-many junction using LINQ?

Posted on 2010-08-24
9
748 Views
Last Modified: 2013-11-11
I am trying to query a junction table using LINQ but I can't see the entities.  Please see database diagram for further details.

I originally had this setup with the Match table that had to extra fields TeamAID and TeamBID, each with a foreign key relationship to the Team table primary key.  To access the TeamA fields I could simply use m.Match.Team.Name, etc., or TeamB's fields by using m.Match.Team1.Name.

Then I decided to use a many-to-many relationship using a junction table, but now that I have a many-to-many relationship I can access the fields.

What am I am doing wrong?
//OLD CODE
            IQueryable matches = from m in db.Matches
                                 where (m.Date >= date && m.Date <= date.AddHours(24)) &&
                                 m.Stage.Name == stageName &&
                                 m.MatchDay.Title == matchDayTitle
                                 orderby m.Date
                                 select new
                                 {
                                     MatchDay = m.MatchDay.Title,
                                     Time = string.Format("{0:t}", m.Date),
                                     TeamAFlagIconUrl = m.Team.FlagIconURL,
                                     TeamAName = m.Team.Name,
                                     TeamBFlagIconUrl = m.Team1.FlagIconURL,
                                     TeamBName = m.Team1.Name
                                 };

//NEW CODE
//Get all matches
            IQueryable matches = from m in db.Matches
                                 where (m.Date >= date && m.Date <= date.AddHours(24)) &&
                                 m.Stage.Name == stageName &&
                                 m.MatchDay.Title == matchDayTitle
                                 orderby m.Date
                                 select new
                                 {
                                     MatchDay = m.MatchDay.Title,
                                     Time = string.Format("{0:t}", m.Date),
                                     TeamAFlagIconUrl = m.MatchTeams.????,
                                     TeamAName = m.MatchTeams.????,
                                     TeamBFlagIconUrl = m.MatchTeams.????,
                                     TeamBName = m.MatchTeams.????
                                 };
            return matches;

Open in new window

DataContext.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
  • 5
  • 4
9 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 33511542
Hi markerasmus;

Because MatchTeams points to a collection of Teams format the statements as follows.

TeamAFlagIconUrl = m.MatchTeams.Select( t => t ),
TeamBFlagIconUrl = m.MatchTeams.Select( t => t ),

Fernando
0
 

Author Comment

by:markerasmus
ID: 33511629
Fernando, how does LINQ know that which item belongs to TeamA, and which item belongs to TeamB?  i.e. how does it know where to assign the value for TeamAFlagIconUrl and TeamBFlagIconUrl?
0
 

Author Comment

by:markerasmus
ID: 33511685
And how do you retrieve the team name?

TeamAFlagIconUrl = m.MatchTeams.Select( t => t ),
TeamAName = m.MatchTeams.Select( t => t ),
TeamBFlagIconUrl = m.MatchTeams.Select( t => t ),
TeamBName = m.MatchTeams.Select( t => t )

How does LINQ know that the column/entity should be assigned to the TeamAName, or TeamBName?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:markerasmus
ID: 33511864
Consider the attached code (old code when I still had TeamAID and TeamBID) - how do I update the teams for a match now that they are in a junction table?
0
 

Author Comment

by:markerasmus
ID: 33511868

//Create a new instance of the TournamentDataContext object.
            using (TournamentDataContext db = new TournamentDataContext())
            {
                //Get the current match.
                Match match = (from m in db.Matches
                               where m.ID == matchID
                               select m).Single();

                //StageID.
                if (stageId != null)
                    match.StageID = stageId;
                //MatchDayID.
                if (matchDayID != null)
                    match.MatchDayID = matchDayID;
                //TeamAID.
                if (teamAID != null)
                    match.TeamAID = teamAID;
                //TeamBID.
                if (teamBID != null)
                    match.TeamBID = teamBID;
                //Date.
                if (date != null)
                    match.Date = date;

                //Submit the changes.
                db.SubmitChanges();
            }

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 33512872
Hi Hi markerasmus;

First let me say that Linq to SQL does not support the Many to Many relationship model.

To your question, "how does LINQ know that which item belongs to TeamA, and which item belongs to TeamB?, It does not know. You would need to query using what you want returned for example :

TeamAFlagIconUrl = m.MatchTeams.Where(r => t.TeamAFlagIconUrl  == "YourCriteria").Select( t => t ),

To your question, "And how do you retrieve the team name?", The m.MatchTeams returns an EntitySet and therefore is not a single value where you can select a single field. You would need to query the entity set for what you need and return a single value.


Fernando
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 33513041
Hi markerasmus;

Take a look at these two links to see if they will help you.

How to implement a many-to-many relationship using Linq to Sql ?
http://blogs.msdn.com/b/mitsu/archive/2007/06/21/how-to-implement-a-many-to-many-relationship-using-linq-to-sql.aspx

LINQ to SQL - Many to Many Relationships
http://www.codeproject.com/KB/linq/linq-to-sql-many-to-many.aspx

Fernando
0
 

Author Comment

by:markerasmus
ID: 33513071
Thanks Fernado.  In your opinion, should i have used a many-to-many relationship, or kept the TeamAID and TeamBID fields in the Match table as foreign keys to the Team table?
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 33513191
In my opinion I would have keep the original design, makes it simpler to work in Linq.
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

729 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