SQL Select where values in List<string>

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?

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.

From stackoverflow

• You could use String.Join. Try something like this:

  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.

  mquander : The signature for string.Join is actually (string, string[]) so the call would more correctly look like string.Join(",", list.ToArray()).

  Andrew Hare : Good call - I fixed it!

  erikkallen : I would go further than your statement "will not protect you against SQL injection" and say that these things are the mother of all SQL injections. The better approach is far more work, though.

  Helen Toomik : Sure, in the general case building an SQL query by string concatenation is a bad idea, but if the list of RecordIds is a strongly-typed list of integers, this approach has zero risk of SQL injection.

• 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

• 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....

• Linq to Entities. RecordList should be a List<T>, not an ArrayList or an IList<T>

  IEnumerable<TableRow> query =
    from t in db.Table
    where RecordList.Any(r => t.RecordId == r)
    select t;
  

  This will generate sql with parameters:

  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.

• Using Linq to SQL and I assume the Entity Framework you can do the following:

  dataContext.Table.Where(t => RecordIDList.Contains(t.RecordID));
  

  Will work with both List<> and ArrayList as they both implement IEnumerable.

  Linq and Lambdas require that you reverse the Contains method but it does work and generates a SQL "IN ()" clause.

• 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

  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:

  select * from tablename where exists (select id from othertablename where fieldtype=1)
  

  David B : JoelHess didn't ask about LinqToSql... the question includes Oracle, so LinqToSql should have been ruled out by that requirement.

• 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 Sommarskog describes how to do it.

  Or, you could use table parameters, new in SQL server 2008 (I think).

  Or, as Manu said, you can use XML.

  However, I advice against using the IN String.Join approach in the accepted answer since it is like asking for SQL injection.

• 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.

  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.
  }