CODEWITHSUNDEEP
linq
Darren
Darren

Reputation: 67

Select distinct on one returned field

I am trying to return a distinct list - but instead of projecting the Distinct() operator over all the returned fields I only want the Suburb to have the operator applied to it like so lga.Suburb.Distinct() but this does not work - how do I go about this?

Thanks!

Public Function GetLGAForRegion(ByVal region As Integer)

    Dim dc As New DataContextDataContext
    Dim query = (From lga In dc.tblLGA_Suburbs
    Join suburb In dc.tblSuburbs On lga.Suburb Equals suburb.SuburbName
    Join regions In dc.tblRegions On suburb.RegionID Equals regions.RegionID
    Where (regions.RegionID = region)
    Select lga.Suburb.Distinct(), suburb.SuburbID).ToList()
    Return query

End Function

Ok - this works :) Thanks for your help!

 Dim query = (From lga In dc.tblLGA_Suburbs
        Join suburb In dc.tblSuburbs On lga.Suburb Equals suburb.SuburbName
        Join regions In dc.tblRegions On suburb.RegionID Equals regions.RegionID
        Where (regions.RegionID = region)
        Group By lga = lga.Suburb Into g = Group
        Order By lga Ascending
        Select g.First().lga.Suburb, g.First().suburb.SuburbID).ToList()

Upvotes: 0

Views: 1069

Answers (1)

boca
boca

Reputation: 2352

You can use GroupBy like this

GroupBy(r -> r.Suburb).Select(g => g.First())

in query style

Dim query = (From lga In dc.tblLGA_Suburbs
Join suburb In dc.tblSuburbs On lga.Suburb Equals suburb.SuburbName
Join regions In dc.tblRegions On suburb.RegionID Equals regions.RegionID
Where (regions.RegionID = region)
Group lga by lga.Suburb into suburbGroup
Select suburbGroup.First();

Upvotes: 2

Related Questions