CODEWITHSUNDEEP
asp.netvb.netlinq
StephenT
StephenT

Reputation: 495

LINQ grouping with count issue

I'm having trouble trying to group a LINQ query with a count correctly. As an example I'm trying to query a datatable with the following data:

PC EC CC

ABC XXX US

ABC XXX CA

ABC XXX UK

DEF YYY US

DEF YYY CA

DEF YYY UK

DEF ZZZ US

DEF ZZZ CA

DEF ZZZ UK

HIJ AAA US

HIJ AAA CA

HIJ AAA UK

I want to use the count function to show the number of different values of EC for each value of PC, removing the values in CC. In other words the result for the above dataset should be:

PC Count EC

ABC 1

DEF 2

HIJ 1

I've tried several different ways of getting to this result but I keep hitting a wall. My last attempt looked like this:

 Dim test = From r In ( _
                        From s In Base _
                        Group By s.Base_PC, _
                                 s.Base_EC _
                        Into g = Group) _
                        Group r By key = New With {r.Base_PC} _
                        Into Group _
                        Select key.Base_PC, Group.Count

Any ideas where I'm going wrong? Thanks!

Upvotes: 1

Views: 118

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460208

Here the method syntax since it's too much for query syntax:

Dim counts = base.AsEnumerable().
    GroupBy(Function(r) r.Field(Of String)("PC")).
    Select(Function(g) New With {
               .PC = g.Key,
               .CountEC = g.Select(Function(r) r.Field(Of String)("EC")).
                            Distinct().
                            Count()
           })

For Each x In counts
    Console.WriteLine("PC={0} Count of distinct EC's={1}", x.PC, x.CountEC)
Next

First you need to group by the "PC" column, then you select an anonymous type with the group's key and the count of distinct values of the "EC" column.

Upvotes: 2

Related Questions