For example, Oracle 10g has got a limitation for 1000 items in the IN clause and here is how you can get around it:
public interface ISqlInClauseGenerator { string Generate(string[] items, string columnName, int inClauseLimit = 1000); } public class SqlInClauseGenerator : ISqlInClauseGenerator { const string EndClause = ")"; const string StartClausePattern = "{0} IN ("; const string ConnectorClausePattern = ") OR " + StartClausePattern; public string Generate(string[] items, string columnName, int inClauseLimit = 1000) { if (items.IsNullOrEmpty()) { return string.Empty; } string startClause = string.Format(StartClausePattern, columnName); string connectorClause = string.Format(ConnectorClausePattern, columnName); var sb = new StringBuilder(startClause); for (int i = 0; i < items.Length; i++) { if (i == 0) { // start sb.Append(items[i]); } else if (i % inClauseLimit == 0) { // start a new IN clause sb.Append(connectorClause); sb.Append(items[i]); } else { // continue sb.Append("," + items[i]); } if (i == items.Length - 1) { // it's the last item sb.Append(EndClause); } } return sb.ToString(); } }
For example, if you have a list of 2000 productIds, this will generate something like this:
"ProductId IN (1,2,3...1000) OR ProductId IN (1001...2000)"