SQL Select where values in List<string>
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/602544/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Select where values in List<string>
提问by JoelHess
Is there a way that I can create query against a data source (could be sql, oracle or access) that has a where clause that points to an ArrayList or List?
有没有一种方法可以针对具有指向 ArrayList 或 List 的 where 子句的数据源（可以是 sql、oracle 或 access）创建查询？
example:
例子：
Select * from Table where RecordID in (RecordIDList)
I've seen some ways to do it with Linq, but I'd rather not resort to it if it's avoidable.
我已经看到一些使用 Linq 的方法，但如果可以避免，我宁愿不诉诸它。
回答by Andrew Hare
You could use String.Join. Try something like this:
你可以使用String.Join. 尝试这样的事情：
String query = "select * from table where RecordId in ({0});";
String formatted = String.Format(query, String.Join(",", list.ToArray()));
As a side note this will not protect you against SQL injection - hopefully this example will point you in the right direction.
附带说明一下，这不会保护您免受 SQL 注入的影响 - 希望这个示例会为您指明正确的方向。
回答by Amy B
Linq to Sql. RecordList should be a List<T>, not an ArrayListor an IList<T>
LINQ 到 Sql。RecordList 应该是 a List<T>，而不是 anArrayList或 anIList<T>
IEnumerable<TableRow> query =
from t in db.Table
where RecordList.Any(r => t.RecordId == r)
select t;
This will generate sql with parameters:
这将生成带有参数的sql：
SELECT *
FROM Table
WHERE RecordId in (@p0, @p1, @p2, @p3, @p4)
Linq will generate as many parameters as are needed. Some database implementations are limited in the number of parameters that can be accepted. SqlServer2005's limit is a little over 2000 parameters... so don't use a list with more than 2000 elements.
Linq 将根据需要生成尽可能多的参数。某些数据库实现在可以接受的参数数量方面受到限制。SqlServer2005 的限制是 2000 多个参数...所以不要使用超过 2000 个元素的列表。
回答by GordonB
I've only done what your trying to do with a comma separated list
我只做了你想用逗号分隔的列表做的事情
Select * from Table where RecordID in (1,2,34,45,76,34,457,34)
or where the results come from a separate select
或者结果来自单独的选择
Select * from Table where RecordID in (select recordId from otherTable where afieldtype=1)
I'm pretty sure you can't achieve what you're after....
我很确定你无法实现你所追求的......
回答by cjk
You can iterate over your array and add a parameter to your SQL for each. This gets you around SQL injection, but make sure you use a StringBuilder rather than strign concatenation as you build up your SQL statement.
您可以遍历数组并为每个数组添加一个参数到您的 SQL。这可以让您绕过 SQL 注入，但请确保在构建 SQL 语句时使用 StringBuilder 而不是严格连接。
e.g.
例如
StringBuilder sql = new StrignBuilder("select * from Table where ");
for (int i = 0; i < RecordIDLis.Length; i++)
{
if (i > 0) sql.Append (" OR ");
sql.Append(" RecordID = @param" + i.ToString() + " ");
IDbDataParameter param = new Param();
param.value etc.
}
回答by erikkallen
You can write a table-valued user-defined function that takes a list of IDs and creates a table, then join agains the result of this function. This article by Erland Sommarskogdescribes how to do it.
您可以编写一个表值用户定义函数，该函数接受一个 ID 列表并创建一个表，然后再次连接该函数的结果。Erland Sommarskog 的这篇文章描述了如何做到这一点。
Or, you could use table parameters, new in SQL server 2008 (I think).
或者，您可以使用 SQL Server 2008 中的新表参数（我认为）。
Or, as Manu said, you can use XML.
或者，正如 Manu 所说，您可以使用 XML。
However, I advice against using the IN String.Join approach in the accepted answer since it is like asking for SQL injection.
但是，我建议不要在接受的答案中使用 IN String.Join 方法，因为它就像要求 SQL 注入。
回答by andleer
Using Linq to SQL and I assume the Entity Framework you can do the following:
使用 Linq to SQL 和我假设实体框架您可以执行以下操作：
dataContext.Table.Where(t => RecordIDList.Contains(t.RecordID));
Will work with both List<> and ArrayList as they both implement IEnumerable.
将与 List<> 和 ArrayList 一起使用，因为它们都实现了 IEnumerable。
Linq and Lambdas require that you reverse the Contains method but it does work and generates a SQL "IN ()" clause.
Linq 和 Lambdas 要求您反转 Contains 方法，但它确实有效并生成 SQL“IN()”子句。
回答by Manu
If you use dynamic SQL, you can send the content of the parentheses as a literal comma-separated list. Otherwise you can use an XML variable for sending multiple values. See http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
如果使用动态 SQL，则可以将括号的内容作为文字逗号分隔列表发送。否则，您可以使用 XML 变量发送多个值。见http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
回答by Roel Snetselaar
Please note Linq to SQL = dead, Source: http://blogs.msdn.com/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx
请注意 Linq to SQL = dead，来源：http: //blogs.msdn.com/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap。 aspx
Entity framework is what you should use currently if you want to implement such architecture.
如果你想实现这样的架构，目前应该使用实体框架。
Furthermore if you are using another select query (like GordonB suggests) for your "in" clause it would be better to use "exists" instead of "in" for example:
此外，如果您对“in”子句使用另一个选择查询（如 GordonB 建议的），最好使用“exists”而不是“in”，例如：
select * from tablename where exists (select id from othertablename where fieldtype=1)
