How to Use 'IN' on a DataTable

Question

I have this code in MS SQL:

select * from table where column1 in (select column2 from table)

How can I translate this using a DataTable?

Something along the lines of: table.select("column1 in column2")

Answer 1

you can't. but you can do it via linq + table.select:

table.Select(string.Format("CITY in '{0}'",string.Join("','",table.Rows.OfType<DataRow>().Select(r=>r["COUNTRY"].ToString()).Distinct())))

EXPLANATION: suppose you have a very simple table

ID CITY COUNTRY
1 NY USA
2 Moscow Russia
3 LA USA
4 St Peterburg Russia

1. Using LINQ to Objects we select all unique values from Country column and concatenate values (via string.Join) to the IN filter statement string. For our example it will be USA','Russia
2. surround IN filter statement with quotes via string.Format: 'USA','Russia'
3. Pass IN filter in dataTable.Select("CITY IN ('USA','Russia')")

FYI:

If you need to execute really cool SQL queries against DataTables\DataSets you can use NQuery it is very fast and standards compliant.

Answer 2

Assuming the tables are in the same DataSet, you can add a DataRelation to the DataSet and then access the child rows using GetChildRows()

var relation = new DataRelation("RelationName", 
    dataSet.Tables["Parent"].Columns["Column2"], 
    dataSet.Tables["Child"].Columns["Column1"]);

dataSet.Relations.Add(relation);

var childRows = from row in dataSet.Tables["Child"].Rows
                where row.GetParentRows("RelationName").Length > 0;

Answer 3

You can use the following LINQ to DataSets query to get the same result as the query you have in SQL.

  var rows = from r1 in table.AsEnumerable()
             from r2 in table.AsEnumerable()
             where r1.Field<string>("Column1") == r2.Field<string>("Column2")
             select r1;

I assume from your example that the columns are coming from the same table. If not then you just need to change the table in the above as follows.

  var rows = from r1 in table1.AsEnumerable()
             from r2 in table2.AsEnumerable()
             where r1.Field<string>("Column1") == r2.Field<string>("Column2")
             select r1;

This is similar to

select * from table1 where column1 in (select column2 from table2)