find all records in datatable from another datatable

Question

I have two asp.net datatables, each with a UserID field. I need to return a count of those from table1 that are in table2. Example:

Table1: Userid 123, 456, 789

Table2: UserID 789, 456

The result should be a count of 2 (789 and 456).

Table1 and Table2 could have thousands of records. I am looking for the most efficient way to return the result, asp.net, vb.net. I have looked at Linq but have never used it, not sure if this helps...

How to detect that a datatable is a part of another datatable

This almost works. It gives me 123. Does anyone know how I can get the 456 and 789?

Dim dt3 As DataTable = dt_users.[Select]().Where(Function(x) 
Not dt.[Select](String.Format("UserID = '{0}'",   x("UserID"))).Any()).CopyToDataTable()

'get returned UserID
For Each row As DataRow In dt3.Rows
    Dim user As String = row("UserID")
Next

you could check out data relations. https://msdn.microsoft.com/en-us/library/ay82azad(v=vs.110).aspx — Jeremy, May 17 '15 at 04:07
@Jeremy I am looking for a filtering solution. creating a relationship does not help me. — Rob, May 17 '15 at 04:17
From the doc "After you have created a DataRelation, you can use it to navigate between tables and to retrieve values." Creating a DataRelation object not only allows you to define the relationship, but also select related records from the other table. — Jeremy, May 17 '15 at 04:30
https://msdn.microsoft.com/en-us/library/system.data.datatable.childrelations(v=vs.110).aspx is a more complete example of what i'm talking about. Either that, or use a dataview, and loop through filtering on each value getting the count along the way. — Jeremy, May 17 '15 at 04:33

score 0 · Answer 1 · answered May 17 '15 at 04:20

You could just do a sql statement if that is easiest (don't see a requirement that it be a linq solutions). You can use something such as this:

select table1.* from table1 inner join table2 on table1.UserID = table2.UserID

You could also user the exists statement:

select * from table1 where exists (select 1 from table2 where table2.UserId = table1.UserId)

I am no expert on which would be faster, but I believe the first example would be the fastest, assuming both columns are indexed.

HTH

Wade

These objects are created on the fly in memory (datatables) so I can't use a stored procedure. — Rob, May 17 '15 at 04:26

score 0 · Answer 2 · answered May 17 '15 at 04:55

0

Check out LINQ-to-Objects, specifically the Join method.

answered May 17 '15 at 04:55

bmay2

382
2
4
17

score 0 · Answer 3 · answered May 17 '15 at 13:26

Did a little bit of research on Linq and was able to modify the query in my question to identify which users from Table1 are in Table2...

'get Users from Table1 (dt_users) that are in Table2 (dt)
 Dim dt3 As DataTable = dt_users.[Select().Where(Function(x) dt.[Select](String.Format("UserID = '{0}'", x("UserID"))).Any()).CopyToDataTable()

'output result from above dt3
 For Each row As DataRow In dt3.Rows
      Dim user As String = row("UserID")
 Next

'get Users from Table1 (dt_users) that are NOT in Table2 (dt)
Dim dt4 As DataTable = dt_users.[Select]().Where(Function(x) Not dt.[Select](String.Format("UserID = '{0}'", x("UserID"))).Any()).CopyToDataTable()

'output result from dt4
For Each row As DataRow In dt4.Rows
    Dim user As String = row("UserID")
Next

find all records in datatable from another datatable

3 Answers3