2

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")

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
Gerald Torres
  • 403
  • 6
  • 18

3 Answers3

2

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.

Sergey Mirvoda
  • 3,209
  • 2
  • 26
  • 30
  • So that's how... Put simply, this concatenates distinct values in the 'Country' Column, and the string made from that will be used by the Select(), right? But wouldn't this be time consuming if the table has about 100 rows? – Gerald Torres Aug 20 '11 at 16:56
  • in my example there is only one loop (Rows.Select) other example uses multiple loops. For 100 row it will be _very_ fast. For very large tables I'll recommend you NQuery. – Sergey Mirvoda Aug 20 '11 at 19:46
1

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;
Richard Szalay
  • 83,269
  • 19
  • 178
  • 237
1

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)
Chris Taylor
  • 52,623
  • 10
  • 78
  • 89
  • I used the same table :) I have no idea how this works... Can you please explain more of your first code? – Gerald Torres Aug 20 '11 at 16:37
  • @Gerald, then the first example should do the trick for you. I just thought it safe to show both options. – Chris Taylor Aug 20 '11 at 16:38
  • @Chris I think you should use join instead of IN. – Sergey Mirvoda Aug 20 '11 at 16:55
  • @Sergey, I stuck to the sample as provided by the OP rather than confuse things, my answer is the LINQ stuff, the SQL is from the OPs question. – Chris Taylor Aug 20 '11 at 17:48
  • @Gerald, no, using the LINQ in the first example will give you the same as the SQL you have shown ie. return all the rows that have a value in column1 that also appears in column2. Have you tried it? – Chris Taylor Aug 20 '11 at 17:50