CODEWITHSUNDEEP
.netvb.netlinqdatatable
Carlos Landeras
Carlos Landeras

Reputation: 11063

Linq - Filter datatable based on other datatable column values

I have two datatables with different datarow structure but one common column. I would like to get the ROWS of the first datatable, that are not present on the second datatable based on a column id. I can get it done. I have tried several things:

Tables: DatasetGlobal and DatasetInserted:

 Dim queryw = From r In DatasetGlobal.Tables(0). _
                     AsEnumerable().ToList() _
                     .Where(Function(r)
                                Return DatasetInserted.Tables(0).AsEnumerable().ToList().Any _
                                    (Function(r2)
                                         Return Not r2.Field(Of Decimal)(Constants.IDINDICAD) = _
                                             r.Field(Of Decimal)(Constants.cteIDINDICAD)
                                     End Function)
                            End Function)

Upvotes: 0

Views: 2463

Answers (1)

sloth
sloth

Reputation: 101052

The easiest way to filter a DataTable is to use the RowFilter property.

Example:

Dim dt1 = New DataTable()
dt1.Columns.Add("id", GetType(Integer))
dt1.Rows.Add(New Object() {1})
dt1.Rows.Add(New Object() {3})
dt1.Rows.Add(New Object() {5})

Dim dt2 = New DataTable()
dt2.Columns.Add("id", GetType(Integer))
dt2.Columns.Add("value", GetType(String))
dt2.Rows.Add(New Object() {1, "Foo"})
dt2.Rows.Add(New Object() {2, "Bar"})
dt2.Rows.Add(New Object() {3, "Foo"})
dt2.Rows.Add(New Object() {4, "Bar"})
dt2.Rows.Add(New Object() {5, "Foo"})


Dim ids = dt1.AsEnumerable().Select(Function(r) CInt(r(0)))
dt2.DefaultView.RowFilter = String.Format("id not in ({0})", String.Join(",", ids))

dt2.DefaultView now looks like:

enter image description here

Upvotes: 1

Related Questions