Help with Linq query - multiple row conditions

Hi all,

I’m trying to figure out how I can query a datatable like so…

CODE | DESCRIPTION
9091 | Apple
9092 | Banana
9091 | Banana

The query should only return 9091 | Apple

This condition is based on the fact that the code “9091” exists but “9092” does NOT exist against that same Description… so basically it needs to check if both “9091” and “9092” exist against each Description, and if so remove from the datatable, in which case this cancels out Banana. I hope this makes sense

How can I do this?

Hi,

I may not understand your requirement 100%, but how about the following?
This expression filters out 2 or more rows which has same description.

dt = dt.AsEnumerable.GroupBy(Function(r) r("DESCRIPTION").ToString()).Where(Function(g) g.Count=1).Select(Function(g) g.First()).CopyToDataTable

Regards,

1 Like

This is close… basically in my system a “9091” code means Added, and “9092” means Removed

I need to be able to see which items have been Added, but not Removed (based on what these codes mean in our system). So I’m hoping to filter the datatable based on Description where there is only “9091” value and there’s no “9092” value against it. For example in my above post, Apple only has “9091”.

Hi,

I got your situation. How about the following?

dt = dt.AsEnumerable.GroupBy(Function(r) r("DESCRIPTION").ToString()).Where(Function(g) g.Any(Function(r)r("CODE").ToString="9091")  AndAlso (not g.Any(Function(r)r("CODE").ToString="9092"))).Select(Function(g) g.First()).CopyToDataTable

Regards,

Use Filter Datatable with conditions CODE = 9091 AND DESCRIPTION = Apple

Thanks so much for your help! This is exactly what I was trying to do. Appreciate your input.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.