Solved

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

Posted on 2010-08-24
9
694 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 62

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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 62

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 62

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 62

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Is IHttpActionResult a promise pattern? 2 33
Duplicate a row 2 29
comparing string in C# 3 33
Form design in vb.net 7 18
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now