LINQ to Entities Group By Driving Me Crazy

I have a LINQ query written in VB.Net that uses Joins to return records from a relational database. I am trying to add a GROUP BY statement, but whichever way I cut it, I either get an error or Visual Studio Intellisense does not allow my format.

This is what my query looks like without the GroupBy:

Public Function GetBadges(ByVal servBranchID As Integer)

Using MREnt As New MREntities
Dim badges As ObjectQuery(Of MR_Badge) = MREntities.MR_Badge
Dim badgetypes As ObjectQuery(Of MR_BadgeType) = MREntities.MR_BadgeType
Dim servicebranches As ObjectQuery(Of MR_ServiceBranch) = MREntities.MR_ServiceBranch

Dim query = _
From badge In badges _
Join badgetype In badgetypes _
On badgetype.BadgeTypID Equals badge.MR_BadgeType.BadgeTypID _
Join servicebranch In servicebranches _
On badge.MR_Service.ServID Equals servicebranch.MR_Service.ServID _
Where servicebranch.ServBranchID = servBranchID _
Select New With { _
    .BadgeID = badge.BadgeID, _
    .Number = badge.Number, _
    .BadgeType = badge.MR_BadgeType.Type, _
    .Badge = badge.Badge _
}

'Here I do an OrderBy after calling Distinct
If query.Count > 0 Then
    Return query.Distinct.OrderBy(Function(b) b.Badge).ToList()
End If

End Function

If I try to add a method-based GroupBy after calling Distinct, like this:

Return query.Distinct.GroupBy(Function(g) g.Number).OrderBy(Function(s) s.Badge).ToList()

I get the following error: "Overload resolution failed because no accessible 'OrderBy' can be called with these arguments: Extension method 'Public Function OrderBy(Of TKey)(keySelector As System.Func(Of System.Linq.IGrouping(Of String, ), TKey)) As System.Linq.IOrderedEnumerable(Of System.Linq.IGrouping(Of String, ))' defined in 'System.Linq.Enumerable': 'Label' is not a member of 'System.Linq.IGrouping(Of String, )'. Extension method 'Public Function OrderBy(Of TKey)(keySelector As System.Func(Of System.Linq.IGrouping(Of String, ), TKey)) As System.Linq.IOrderedEnumerable(Of System.Linq.IGrouping(Of String, ))' defined in 'System.Linq.Enumerable': Data type(s) of the type parameter(s) cannot be inferred from these arguments. Specifying the data type(s) explicitly might correct this error. Extension method 'Public Function OrderBy(Of TKey)(keySelector As System.Linq.Expressions.Expression(Of System.Func(Of System.Linq.IGrouping(Of String, ), TKey))) As System.Linq.IOrderedQueryable(Of System.Linq.IGrouping(Of String, ))' defined in 'System.Linq.Queryable': 'Number' is not a member of 'System.Linq.IGrouping(Of String, )'. Extension method 'Public Function OrderBy(Of TKey)(keySelector As System.Linq.Expressions.Expression(Of System.Func(Of System.Linq.IGrouping(Of String, ), TKey))) As System.Linq.IOrderedQueryable(Of System.Linq.IGrouping(Of String, ))' defined in 'System.Linq.Queryable': Data type(s) of the type parameter(s) cannot be inferred from these arguments. Specifying the data type(s) explicitly might correct this error. "

If I add my Group By statement in the query definition itself, like this:

Dim query = _
    From badge In badges _
    Join badgetype In badgetypes _
    On badgetype.BadgeTypID Equals badge.MR_BadgeType.BadgeTypID _
    Join servicebranch In servicebranches _
    On badge.MR_Service.ServID Equals servicebranch.MR_Service.ServID _
    Where servicebranch.ServBranchID = servBranchID _

    Group badge By badge.Number Into g _

    Select New With { _
        .BadgeID = badge.BadgeID, _
        .Number = badge.Number, _
        .BadgeType = badge.MR_BadgeType.Type, _
        .Badge = badge.Badge _
    }

... I get the error "Definition of method 'g' is not accessible in this context" on the "g".

I have tried a few other ways suggested elsewhere on this site and also from other sites, but nothing works.

Any help will be greatly appreciated.

Thanks!

ansaurus

tags:

views:

answers:

LINQ to Entities Group By Driving Me Crazy

related questions