[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2010-08-24
9
Medium Priority
?
783 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 64

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 64

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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…
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 …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 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