none
Need help with LINQ GroupBy RRS feed

  • Question

  • Hi, 

    Here is my table structure 

    table1 - PLAYER

    PlayerID

    FirstName

    LastName

    EmailAddress

    CellNo

    Table2 - Team

    TeamID

    TeamName

    Table3 - TEAMPLAYERS

    ID

    PlayerID

    TeamID

    A player can be part of more than one team. 

    Question:

    Now I want to get the player basic details along with the count of no. of teams he plays for. I use the following query to get the result in SQL:

    SELECT P.FirstName, P.LastName, P.EmailAddress, P.CellNo, COUNT(TP.TeamID) 

    FROM Player P JOIN TeamPlayer TP ON P.PlayerID = TP.PlayerID 

    GROUP BY P.PlayerID, P.FirstName, P.Lastname, P.EmailAddress, P.CellNo;

    This works perfectly. 

    However, im stuck when converting this to linq. Not much of help from online resources. Can someone help me with this?

    Sunday, May 27, 2018 5:49 PM

Answers

  • Try a query like this (in C#):

    var query = from p in PLAYER

                join t in TEAMPLAYERS on p.PlayerID equals t.PlayerID into pt

                select new { p.FirstName, p.LastName, p.EmailAddress, p.CellNo, TeamCount = pt.Count() };

     

    Use the corresponding variables instead of PLAYER and TEAMPLAYERS.

    This query also works for players that are not included into teams.




    Sunday, May 27, 2018 7:37 PM

All replies

  • Try a query like this (in C#):

    var query = from p in PLAYER

                join t in TEAMPLAYERS on p.PlayerID equals t.PlayerID into pt

                select new { p.FirstName, p.LastName, p.EmailAddress, p.CellNo, TeamCount = pt.Count() };

     

    Use the corresponding variables instead of PLAYER and TEAMPLAYERS.

    This query also works for players that are not included into teams.




    Sunday, May 27, 2018 7:37 PM
  • Hi Sandhya.Sandy,

    Please try the following code, which use group by and count.

    var result = from p in db.PLAYERs
                                 join tp in db.TEAMPLAYERS on p.PlayerID equals tp.PlayerID
                                 group p by new { p.FirstName, p.LastName, p.CellNo } into grp
                                 select new
                                 {
                                     FirstName = grp.Key.FirstName,
                                     LastName = grp.Key.LastName,
                                     CellNo = grp.Key.CellNo,
                                     Count = grp.Count()
                                 }; 

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 28, 2018 1:48 AM
    Moderator
  • You made my day. the code works like a champ! Thanks so much. 
    Monday, May 28, 2018 6:13 AM
  • Thanks Zhanglong for the response. I will try this and let you know. 
    Monday, May 28, 2018 6:14 AM