If you have ben working with Linq, one of the barriers you may have encountered is the "2100 parameter limit" when attempting to build an "IN" statement through Linq.  A common scenario is that you are given a large list of ID's that you have need to select from.  A common way of implementing this could be reviewed here:

http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql/

However, if you implement this method and have greater than 2100 parameters, your going to need a different option.  Critics will probably argue that "you shouldn't be doing that in the first place" and suggest you examine your design.  But sure enough, the scenarios exist and in some cases, your just going to need to get creative.

Recently I went through this myself and came up with a pretty neat extension for IQueryable objects.

public static List<T> In<T>(this IQueryable<T> q, Database Context, String ColumnName, ArrayList Values)
{
String SqlCmd = Context.GetCommand(q).CommandText;
StringBuilder SQL = new StringBuilder();

// First make sure there are no joins - which would mean multiple "where's"
if (SqlCmd.ToLower().Contains(" join "))
throw new NotSupportedException("\"In\" does not support join clauses.");
// Next make sure there are not more than 1 "select's" - which could mean multiple "where's"
if (SqlCmd.ToLower().IndexOf("select ") != SqlCmd.ToLower().LastIndexOf("select "))
throw new NotSupportedException("\"In\" does not support sub queries.");

// Append everything up until the "where" part of the query.
// If there is no where part, just add everything.
if (SqlCmd.ToLower().Contains("where"))
throw new NotSupportedException("\"In\" does not support additional where clauses.");
else
SQL.AppendFormat(SqlCmd.ToString());

SQL.AppendFormat(" where [{0}] IN (", ColumnName);
for (int i = 0; i < Values.Count; i++)
{
if (i < Values.Count - 1)
SQL.AppendFormat("'{0}',", Values[i]);
else
SQL.AppendFormat("'{0}')", Values[i]);
}

return Context.ExecuteQuery<T>(SQL.ToString()).ToList<T>();
}

The limitations are:

  • The "IN" statement must be your first statement in the clause.
  • You cannot use joins (although you could use a view to get around this)
  • You cannot use subqueries (again, you could use a view to get around this)

Everything else is fair game and it makes it easy to share accross your other object.

A sample query then looks like:

List<MyTable> returned = Context.MyTable.In(Context, MyTable.COLUMNS.SomeColumnName, MyItemsAsArrayList);