CODEWITHSUNDEEP
vb.netlinq
StephenT
StephenT

Reputation: 495

LINQ query not grouping properly

I'm trying to pull a group of records that appear more than once in a grouped set of data. My problem here is that my query isn't taking into account the grouping from the initial query and is basically pulling all records. I have an initial query that pulls data from a datatable as follows:

Dim eDTKBase = From eDTKData In dteDTK _
Select New With _
{ _
.eDTK_PDP_Code = eDTKData.Field(Of String)("PDP_CODE"), _`
.eDTK_PDP_Description = eDTKData.Field(Of String)("PDP_DESCRIPTION"), _
.eDTK_WB_Slave = eDTKData.Field(Of String)("WALLBOARD_OR_SLAVE"), _
.eDTK_Print_On_DAD = eDTKData.Field(Of String)("PRINT_ON_DAD"), _
.eDTK_PLI = eDTKData.Field(Of String)("COMPASS_PLI"), _
.eDTK_Dead = eDTKData.Field(Of String)("DEAD"), _
.eDTK_Instance = eDTKData.Field(Of String)("COUNTRY_INSTANCE") _
}

I then try to filter for only the records where the count of the eDTK_PDP_Code field is > 1 only for the records grouped by the eDTK_PLI, eDTK_PDP_Code fields using the following LINQ query:

Dim EDTKMultPDPtoPLI = From EDTK In eDTKBase _
Group By EDTK.eDTK_PLI, _
EDTK.eDTK_PDP_Code _
Into g = Group _
Where eDTK_PDP_Code.Count > 1 _
Select eDTK_PLI, _
eDTK_PDP_Code, _
eDTK_PDP_Code.Count

The problem is it's evaluating the entire set of data from the datatable instead of just the grouped records. What am I doing wrong here? Thanks.

Upvotes: 0

Views: 159

Answers (1)

Wade73
Wade73

Reputation: 4509

I did some experimenting and I think I found a solution. Here is the code using AdventureWorks, but I think it should make sense.

from r in (
     from s in SalesOrderHeaders
     select s.SalesPersonID,s.CustomerID
     where SalesPersonID isnot nothing)
group r by key = new with {r.SalesPersonID,r.CustomerID} into group
where group.count > 1
select key.SalesPersonID, key.CustomerID, group.count()

With this result, showing the number of times a customer has an order with the given sales person.

RepID   CustID  Count
280     1       4
283     2       8
275     3       6
277     3       6
275     4       6
277     4       2
281     5       8

clipped remainder

Hope this helps

Wade

Upvotes: 1

Related Questions