Solved

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

Posted on 2010-08-24
9
718 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
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VS2013 publish C# application Created folder -- where is it??? 10 28
abstract class C# 1 31
VS2010 Build fails to install 14 40
Visual Studio npm 1 12
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.

820 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