CODEWITHSUNDEEP
linqcountgroup-by
derstauner
derstauner

Reputation: 1796

linq query with count

I would like to create a simple linq query, but I don't really know, how it should be. I have searched the net, but found nothing, what I can use or I don't know yet, that I could use it.

So, basically, I have a table with this fields: reference, vat_code, amount, vat_amount, supplier.

Now, I would like to query the records, where reference<>'' and the reference is more than once in the table. But I need all the occupians.

F.e. from

1;VF;100;27;345
2;VF;200;54;123
2;VF;-200;-54;123
2;VF;200;54;123
3;VF;300;81;888

to

2;VF;200;54;123
2;VF;-200,-54;123
2;VF;200;54;123

How would be look the linq query to this?

Thanks.

Upvotes: 1

Views: 49

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236218

If rows with same reference should go together in results, then you should filter out rows with reference equal to empty string, then group all rows by reference and select only those groups which have more than one row.

C# sample with DataTable:

var result = dt.AsEnumerable()
               .Where(r => r.Field<string>("reference") != "")
               .GroupBy(r => r.Field<string>("reference"))
               .Where(g => g.Count() > 1)
               .SelectMany(g => g); // flatten group to sequence of rows

Upvotes: 1

Related Questions