CODEWITHSUNDEEP
vb.netlinqgroup-bydistinct
Hallaghan
Hallaghan

Reputation: 1941

Linq - Group by and apply a distinct to a single field

I have a sql table where I store subscription based purchases made by clients, and due to an old bug on my application, I have rows with different usernames but the same transactionid present.

I am trying to build a query that will only retrieve these results that interest me and not groups of rows with the same username and transactionid (as that is possible and normal).

Here is my current query:

Dim pquery = From r In dc.PurchaseRecords
                             Where r.expired = False AndAlso r.status = 1
                             Group r By r.transactionId Into g = Group
                             Where g.Count() > 1

This query currently returns groups which share the same transactionid, as intended, but in these groups I have situations where the group contains only rows of the same username and not different usernames sharing the same transactionid.

How can I apply a distinct into this query, maintaining the same output structure I have now but returning only groups that are made of different usernames with the same transactionid?

Thank you for your help.

EDIT: I want to return a query which has groups that contain each full row of the table where these conditions I mentioned apply.

Upvotes: 0

Views: 554

Answers (1)

Ahmad Ibrahim
Ahmad Ibrahim

Reputation: 1925

This will return groups that have different usernames with the same transactionid

Dim pquery = From r In dc.PurchaseRecords
                             Where r.expired = False AndAlso r.status = 1
                             Group r By r.transactionId Into g = Group
                             Where g.GroupBy(Function(x) x.UserName).Count() > 1

Upvotes: 1

Related Questions