Solved

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

Posted on 2010-08-24
9
704 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
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 …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 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

19 Experts available now in Live!

Get 1:1 Help Now