Building Predicates Dynamically

I have not had much experience with Object-relational mapping (ORM) until recently and for me it took some getting use to. I came from the school of thought that insisted that the database administrators “…are guarding all the doors, they are holding all the keys”. In previous jobs either the DBAs were designing all the tables and stored procedures and monitoring for the optimal use, or we were going to them to approve our suggested designs.

I am currently using NHibernate for my ORM solution along with LINQ (Language Integrated Query) which provides query capabilities to the managed .NET environment. This combination of technologies along with our own design idiosyncrasies can lead to some very interesting SQL statements getting executed against our Oracle database. One such example was was producing the following Oracle error:

ORA-01795 maximum number of expressions in a list is 1000

This was Oracle’s way of telling us that we had too many expression in a list like the following:

select * from Table1 where Col in (id1, id2, id3, id4, … id998, id999, id1000, id1001)

Unfortunately this is a hard limit in Oracle and I am unsure why it exists, Microsoft SQL Server, which I am more familiar with, has similar limits but it is more closely coupled to the Batch Size Limit (i.e. 65,536 * Network Packet Size). The LINQ pattern that produces a SQL IN statement usually has a Contains pattern like this:

// For this to fail with the ORA-01795 we need over 1000 userIds in this list...
public IEnumerable<string> userIds =
     new List<string> { "0","1","2","3","4","5","6","7","8","9","10","11","12"};
	 
IQueryable<userentity> UserSearch(params string[] keywords)
{
    IQueryable<userentity> query = dataContext.Users;
    query = query.Where(p => userIds.Contains(p.SSN));

    return query;
}

There is a well documented way around this limit, essentially you break each list into groups of less than a 1000 and create a logical OR and combine the IN statements, so it would look something like this:

select * from Table1 where Col in (id1, id2, id3, id4, … id998, id999) OR Col in (id1000, id10001, id1002, id1003, … id1998, id1999)

Of course for our particular case we could have any number of expressions so we would needed a way to dynamically compose expression predicates in groups of 1000 or less.

Creating Dynamic Predicates

The following code represents a PredicateBuilder, it is initiated by calling the second expression with the first expression’s parameters. An Invoke expression calls another lambda expression using the given expressions as arguments. We can create the conditional expression from the body of the first expression and the invoked version of the second, finally the last step is to wrap this in a new lambda expression.

public static class PredicateBuilder
{
    public static Expression<func<t, bool>> True<t>() { return f => true; }
    public static Expression<func<t, bool>> False<t>() { return f => false; }
 
    public static Expression<func<t, bool>> Or<t>(this Expression<func<t, bool>> expr1, Expression<func<t, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<expression>());
        
		return Expression.Lambda<func<t, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }
}

So now our new LINQ query can be expressed easily to avoid the ORA-01795 expression limit:

IQueryable<userentity> UserSearch(params string[] keywords)
{
	const int ORACLE_LIMIT = 999;    
 
    var predicate = PredicateBuilder.False<personentity>();
    for (int i = 0; i < personIds.Count(); i=i+ORACLE_LIMIT)
    {
        IEnumerable<string> subsetPersonIds = userIds.Skip(i).Take(ORACLE_LIMIT);
        predicate = predicate.Or(p => subsetPersonIds.Contains(p.SSN));
    }
	
    return dataContext.Users.Where (predicate);
}