DataTable.Select using the IN operator

Question

I've never found an elegant way to do this, so I'm wondering what methods other developers prefer (for performance, readability, etc).

Is there a way to use the LIKE operator in the DataTable.Select() function, based on the results of a query to another DataTable. For example, in SQL Server, the syntax would be:

Select SomeValue 
From Table1 
WHERE MyField IN 
   (Select SomeField From Table2 Where SomeColumn = SomeFilterVariable)

I know that from a coding standpoint, this is a very simple query to do against the DB, but this is being done in an app where doing it this way would result in hundreds of thousands of calls to a server due to the number of calculations involved. I've timed it, and it's much better to go get all the data at once and use DataTable.Select() or DataTable.Compute() to get the results I need.

I'm open to anything including LINQ to datasets, etc. What I really need to avoid is a lot of trips to the server.

Answer 1

With the example above, I think you could use RIGHT JOIN to get the data you want. Here:

SELECT A.SomeValue 
FROM Table1 A
RIGHT JOIN Table2 B
ON A.MyField = B.SomeField
WHERE B.SomeColumn = SomeFilterVariable

Answer 2

from t1 in db.Table1
where (from t2 in db.Table2 where t2.SomeColumn = SomeFilterVariable select t2.SomeField).Contains(t1.MyField)
select t1.SomeValue;

If that's too messy for you:

var subQ = from t2 in db.Table2 
           where t2.SomeColumn = SomeFilterVariable 
           select t2.SomeField;

var qury =  from t1 in db.Table1
            where subQ.Contains(t1.MyField)
            select t1.SomeValue;

The cool thing here is the way LINQ works, that will build & execute only one SQL statement, basically the same as the SQL you posted.