Linq query to filter data table

I have a data table with 4 columns which may or may not contain duplicate values.
A duplicate value is determined by the first column and third column, if the value in column 1 and value in column 3 are the same it is considered duplicate. If there is a duplicate I want to keep the row with the max value in the second column, and the corresponding value in the 4th column, or null if it is empty.

Here is the query I’m using,

(
From row In dtIn.AsEnumerable
Group row By a1 = row(0).tostring.trim, a2 = row(2).tostring.trim Into grp = Group
Let UOM= grp.Max(Function (y) CDbl(y(1).toString.Trim))
Let COL4= grp.Where(Function (x) x(1).tostring.trim.equals(UOM.tostring)).first
Select dtIn.rows.add(a1,UOM,a2,col4(3))
).CopyToDataTable

I’m getting an error "Sequence contains no elements. "
I tried changing the .first to .firstordefault at the end of the row begining with Col4 and now I am getting "Object reference not set to an instance of an object "

Here is a sample of some of the data rows I’m using for the test.

[From UOM,Conversion,To UOM,Structure
BN,“50,000”,EA,1
PP,144.44,EA,
EA,.600,LB,
]

Hi @amkitche,

You may encounter this error if the row in a column you have filtered is empty.

You should make sure that any column value from your rows is not empty.

Even if there are empty rows, you should not consider them with another linq.

Regards.
Ömer

I know more often than not the 4th column is empty (as you can see by the data)

How would i get around that issue?

Hi @amkitche ,

Could you try changing the Expression to the below and check :

(From row In dtIn.AsEnumerable
Group row By a1 = row(0).tostring.trim, a2 = row(2).tostring.trim Into grp = Group
Let UOM= grp.Max(Function (y) CDbl(y(1).toString.Trim))
Let COL4= grp.Where(Function (x) x(1).tostring.trim.equals(UOM.tostring)).FirstOrDefault
Select dtIn.rows.add(a1,UOM,a2,if(COL4 is Nothing,Nothing,COL4(3))
).CopyToDataTable

We also would ask you to Create a Clone of the Input DT and assign it to a new DT and use it for the Addition of rows in the query instead of the Input DT itself.

Let us know if you are still facing issues.

in general, we do have some doubts when comparing the description and the LINQ

we don’t see such a check implemented

can maybe rewritten to

let mr = grp.OrderBy(Function (g) CDbl(g(1).toString.Trim)).Last()
Let ra = new Object(){a1, mr(1), a2, mr(3)}
Select r = .....

we order the group on UOM an taking from the row with max UOM later the values

Instead of CopyTo DataTable have a look here for a more defensive handling of empty results:

In case you need more help then we would ask to provide more cases / samples especially where the

if the value in column 1 and value in column 3 are the same it is considered duplicate.

case is reflected

Thanks for the response guys, I’ve tried outputting it to a cloned data table and a few other options. I just did some testing and what i am doing, is breaking it into two separate queries, First Sort Descending

(From row In dtIN.Select
Order By CDbl(row(1)) Descending Select row).CopyToDatatable()

Then I take the first record in the group by function, since the first record in the group will now be the largest value.
(
From row In dtIN
Group row By a1 = row(0).tostring.trim, a2 = row(2).tostring.trim Into grp =Group
Select grp.first
).CopyToDataTable

Not as elegant, but does the trick

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