System.Linq.Dynamic and Sql IN operator

Question

I am using the System.Linq.Dynamic library to power a GUI base query engine however I can't seem to find support for the equivalent of the Sql IN operator. Has anyone used this?

I have searched and found a couple of possible duplicates here however none have been exactly what I am asking (or have been solved).

Clarification:

I am using the dynamic query API to build queries in Linq, e.g:

var customers = dbDataContext
                 .Clients
                 .Where("Lastname = @0", "Smith");

Which works fine, the one operator I am struggling with is an equivalent to the Sql IN operator. I would like to do this:

var customers = dbDataContext
                 .Clients
                 .Where("ProductIDsPurchased IN (1,6,8,90)");

However I am not sure how to write this using dynamic queries (the above does not work).

Answer 1

Hi,

Probably this Stackoverflow thread can help you...

Excerpt from the thread:

you could implement your own WhereIn method :

public static IQueryable<TEntity> WhereIn<TEntity, TValue>
 (
     this ObjectQuery<TEntity> query, 
     Expression<Func<TEntity, TValue>> selector, 
     IEnumerable<TValue> collection
 )
{
    if (selector == null) throw new ArgumentNullException("selector");
    if (collection == null) throw new ArgumentNullException("collection");
    ParameterExpression p = selector.Parameters.Single();

    if (!collection.Any()) return query;

    IEnumerable<Expression> equals = collection.Select(value => 
      (Expression)Expression.Equal(selector.Body, 
       Expression.Constant(value, typeof(TValue))));

    Expression body = equals.Aggregate((accumulate, equal) => 
    Expression.Or(accumulate, equal));

    return query.Where(Expression.Lambda<Func<TEntity, bool>>(body, p));
}

Usage:

public static void Main(string[] args)
{
    using (Context context = new Context())
    {
        //args contains arg.Arg
        var arguments = context.Arguments.WhereIn(arg => arg.Arg, args);   
    }
}

In your case:

var customers = dbDataContext
             .Clients
             .WhereIn(c=> c.LastName, new List<string>{"Smith","Wesson"});

HTH

Answer 2

When I use the Contains method of a collection, I find that LINQ-to-SQL generates an "IN" expression for me.