使用DataTable.Select IN操作符 [英] DataTable.Select using the IN operator

问题描述

我从来没有发现一种优雅的方式来做到这一点，所以我不知道用什么方法其他开发商preFER（性能，可读性，等）。

有没有办法使用LIKE运算符的DataTable.Select（）函数的基础上，查询到另一个数据表中的结果。例如，在SQL Server中，语法是：

 选择someValue中
从表1
WHERE MyField的IN
   （选择SomeField从表2其中SomeColumn = SomeFilterVariable）
 

我知道，从编码的角度看，这是一个非常简单的查询做反对DB中，而这是在一个应用程序正在做的地方做将导致成千上万的呼叫到一个服务器，由于这样涉及的计算的数量。我定时它，它是更好的去获得所有的数据一次，并使用DataTable.Select（）或DataTable.Compute（）来得到我需要的结果。

我愿意接受任何东西，包括LINQ到数据集，等我真正需要避免的是很多前往服务器。

解决方案

 从T1在db.Table1
其中，（从T2在db.Table2，其中t2.SomeColumn = SomeFilterVariable选择t2.SomeField）。载（t1.MyField）
选择t1.SomeValue;
 

如果这是太乱了你：

  VAR SUBQ =从T2在db.Table2
           其中，t2.SomeColumn = SomeFilterVariable
           选择t2.SomeField;

VAR qury =从db.Table1 T1
            其中，subQ.Contains（t1.MyField）
            选择t1.SomeValue;
 

这里的很酷的事情是这样的LINQ的工作，将建立和放大器;只能执行一条SQL语句，基本上是一样的，你发布的SQL。

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.

解决方案

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.

这篇关于使用DataTable.Select IN操作符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！