none
LINQ Group By Count in VB RRS feed

  • Question

  • Hi, our group is using Entity Framework and LINQ and are all finding it a little hard to wrap our heads around the LINQ syntax when we're so used to SQL syntax.  Can someone help with this query - how can we write this in LINQ syntax (in VB preferably)?

    We have a table of Personnel, a table of Military (some personnel are in Military) and a table of Location.  We want a count by location of how many people are in Military.  The below works in SQL, but we're having trouble translating that into LINQ.

    Thanks!

    Select Count(*) as Mil_Count, Trp_bureau_name
    From Troop_LKP TBL INNER JOIN Personnel P
    on TBL.Trp_Id = SUBSTRING(p.Location_Code,1,2)
    INNER JOIN Military M on m.Personnel_ID = p.Personnel_ID
    Group by Trp_bureau_name, Trp_Id
    order by Trp_Id

    Friday, November 6, 2015 12:31 PM

Answers

  • Figured it out and thought I'd post it in case it can help anyone else.

     Dim r = (From mm In Context.Militaries
                    Join pp In Context.Personnels On mm.Personnel_ID Equals pp.Personnel_ID
                    Join ll In Context.Troop_LKP On ll.Trp_Id Equals pp.Location_Code.Substring(0, 2)
                    Group ll By ll.Trp_Id, ll.Trp_Bureau_name Into llGroup = Group Order By Trp_Id
                   Select New With {.Troop = llGroup.FirstOrDefault().Trp_Bureau_name, .mCount = llGroup.Count()}).ToList()

    • Marked as answer by KimFromPA Friday, November 6, 2015 4:09 PM
    Friday, November 6, 2015 4:09 PM

All replies

  • Figured it out and thought I'd post it in case it can help anyone else.

     Dim r = (From mm In Context.Militaries
                    Join pp In Context.Personnels On mm.Personnel_ID Equals pp.Personnel_ID
                    Join ll In Context.Troop_LKP On ll.Trp_Id Equals pp.Location_Code.Substring(0, 2)
                    Group ll By ll.Trp_Id, ll.Trp_Bureau_name Into llGroup = Group Order By Trp_Id
                   Select New With {.Troop = llGroup.FirstOrDefault().Trp_Bureau_name, .mCount = llGroup.Count()}).ToList()

    • Marked as answer by KimFromPA Friday, November 6, 2015 4:09 PM
    Friday, November 6, 2015 4:09 PM
  • It is glad to hear that you got it working and thank you for sharing your solutions & experience here. It will be very beneficial for other community members who have similar questions.

    Cheers.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 9, 2015 1:34 AM
    Moderator