Match Against A Collection In Single Query (Extending SQL 'IN' Statement)

Introduction

SQL 'IN' statement that allows you to match one field against a list of values. What if you have to match more than one field? What if you have a collection of objects and want to find matches in database and you want to do it using just one query.

The following example demonstrates how it can be done using LINQ:

var investors = new List < TestData >
                {
                    new TestData { Investor = new Investor
                    { InvestorId = "253360" } , Country = "AUS" } ,
                    new TestData { Investor = new Investor
                    { InvestorId = "352410" } , Country = "NZL" } ,
                    new TestData { Investor = new Investor
                    { InvestorId = "552479" } , Country = "USA" }
                } ;

var filter = ExpressionExtensions.In<TestData, Investor>( investors,
                               ( data, db)=> data.Investor.InvestorId== db.Id&&
                                                data.Domicile== db.Country);

// the following is executed in one query
var loaded = Database.Investor
                 .Where(filter)
                 .ToList();

When executed against database, it will generate SQL that looks similar to the following:

SELECT * FROM Investor
WHERE (Id = '253360' AND Country = 'AUS') OR
      (Id = '352410' AND Country = 'NZL') OR
      (Id = '552479' AND Country = 'USA')

And the following is implementation of 'In' function.

 publicstaticclass ExpressionExtensions
{
    publicstatic Expression<Func<TDb, bool>>In<TData, 
    TDb>(IEnumerable<TData> data, Expression<Func<TData, TDb, bool>> projection)
    {
        var expressions = new List<Expression<Func<TDb, bool>>>();

        foreach(var d in data)
        {
            expressions.Add(SetOneParam(projection, d));
        }

        //Combine all individual expressions with Or
        var expr = expressions.Aggregate((current, next)=> current.Or(next));
       
        return expr;
    }

    privatestatic Expression<Func<T, bool>> 
    Or<T>(this Expression<Func<T, bool>> expr1, 
    Expression<Func<T, bool>> expr2)
    {
        var invExpr = Expression.Invoke(expr2, expr1.Parameters);

        return Expression.Lambda<Func<T, bool>>
            (Expression.OrElse(expr1.Body, invExpr), expr1.Parameters);
    }
    privatestatic Expression<Func<TDb, bool>> SetOneParam<TData, 
    TDb>(Expression<Func<TData, TDb, bool>> expr, TData data)
    {
        var dataParam = expr.Parameters.Single(x => x.Type== typeof (TData));
        var visitor = new ReplaceParamVisitor<TData, TDb>(data, dataParam);
        return visitor.Visit(expr)as Expression<Func<TDb, bool>>;
    }
}

class ReplaceParamVisitor<TData,TDb>: ExpressionVisitor
{
    privatereadonly TData _data;
    privatereadonly ParameterExpression _dataParam;

    public ReplaceParamVisitor(TData data, ParameterExpression dataParam)
    {
        _data = data;
        _dataParam = dataParam;
    }

    publicoverride Expression Visit(Expression node)
    {
        // replace parameter with constant

        if(node == _dataParam)
            return Expression.Constant(_data);

        returnbase.Visit(node);
    }

    protectedoverride Expression VisitLambda<T>(Expression<T> node)
    {
        var parameters = node.Parameters
                             .Where(p => p != _dataParam);

        // remove parameter from lambda expression
        return Expression.Lambda<Func<TDb, bool>>(Visit(node.Body), parameters);
    }
}

It works by modifying the lambda expression tree and replacing ParameterExpression with ConstantExpression. It does it for every item in collection and joins partial expressions using 'OR'. So the result is one big OR statement, one sub-statement per collection item.