LINQ filter combining exact matches like SQL IN and StartsWith matches

Question

I'm having product entity:

public class Product : DomainBase
{
    public virtual string Name { get; set; }
}

And there should be option to select products by filter, which contains an array of names, like:

public static IEnumerable<Product> SearchArrayQueryLinq(IEnumerable<string> names)
    {
        using (var session = Database.OpenSession())
        {
            var products = session.Query<Product>();

            var result = products.Where(product => names.Any(name =>  product.Name.Contains(name)));

            return result.ToList();
        }
    }

but it throws

System.NotSupportedException: Specified method is not supported.

What is right approach, to accomplish such filtering?

Answer 1

Without knowing more about what database you're connecting to or what library (is it RavenDB.. having done a quick Google?) then it's hard to be completely sure what the problem is.

However, what I think is happening is that you are giving an expression to the IQueryable "Where" extension method and the library is trying to turn that into search criteria to run against the db.. and failing because "Any" is not supported in nested criteria like that (again, I'm guessing).

The LINQ expressions that may or may not be translated into the database language (eg. SQL) vary by the library that performs the translation and vary by the database being talked to.

For example, the following (which is basically what you want to do) works fine with Entity Framework:

    private static void Test(IEnumerable<string> names)
    {
        using (var context = new NORTHWNDEntities())
        {
            foreach (var product in context.Products.Where(product => names.Any(name => product.ProductName.Contains(name))))
            {
                Console.WriteLine(product.ProductName);
            }
        }
        Console.ReadLine();
    }

One easy option for you is to change your code to

public static IEnumerable<Product> SearchArrayQueryLinq(IEnumerable<string> names)
{
    using (var session = Database.OpenSession())
    {
        var products = session.Query<Product>();
        return result = products.ToList().Where(product => names.Any(name =>  product.Name.Contains(name)));
    }
}

This should work.. however, it will get all Products from the database and perform the filtering in-memory. This is less efficient than getting the database to perform the search.

An alternative would be to generate an "Expression<Func<Product, bool>>" filter yourself that is easier for the library that you're using to translate. If, instead, of a nested "Any" criteria, you could generate a simple set of "OR" name checks then there is a better change of it working. The following will achieve that - but it's quite a lot of code. If this is something that you need to do in several places then some of the code could be made more general and reused.

    private static IEnumerable<Product> SearchArrayQueryLinq(IEnumerable<string> names)
    {
        using (var context = new NORTHWNDEntities())
        {
            return context.Products.Where(GetCombinedOrFilter(names)).ToList();
        }
    }

    private static Expression<Func<Product, bool>> GetCombinedOrFilter(IEnumerable<string> names)
    {
        var filter = GetNameFilter(names.First());
        foreach (var name in names.Skip(1))
            filter = CombineFiltersAsOr(filter, GetNameFilter(name));
        return filter;
    }

    private static Expression<Func<Product, bool>> GetNameFilter(string name)
    {
        return product => product.ProductName.Contains(name);
    }

    private static Expression<Func<Product, bool>> CombineFiltersAsOr(Expression<Func<Product, bool>> x, Expression<Func<Product, bool>> y)
    {
        // Combine two separate expressions into one by combining as "Or". In order for this to work, instead of there being a parameter
        // for each expression, the parameter from the first expression must be shared between them both (otherwise things will go awry
        // when this is translated into a database query) - this is why ParameterRebinder.ReplaceParameters is required.
        var expressionParameter = x.Parameters.Single();
        return Expression.Lambda<Func<Product, bool>>(
            Expression.Or(x.Body, ParameterRebinder.ReplaceParameters(y.Body, toReplace: y.Parameters.Single(), replaceWith: expressionParameter)),
            expressionParameter
        );
    }

    // Borrowed and tweaked from https://blogs.msdn.microsoft.com/meek/2008/05/02/linq-to-entities-combining-predicates/
    public sealed class ParameterRebinder : ExpressionVisitor
    {
        public static Expression ReplaceParameters(Expression expression, ParameterExpression toReplace, ParameterExpression replaceWith)
        {
            return new ParameterRebinder(toReplace, replaceWith).Visit(expression);
        }

        private readonly ParameterExpression _toReplace, _replaceWith;
        private ParameterRebinder(ParameterExpression toReplace, ParameterExpression replaceWith)
        {
            _toReplace = toReplace;
            _replaceWith = replaceWith;
        }

        protected override Expression VisitParameter(ParameterExpression p)
        {
            if (p == _toReplace)
                p = _replaceWith;
            return base.VisitParameter(p);
        }
    }

Update: I didn't notice your nhibernate tag - whoops! Using the criteria combining methods that nhibernate has is probably easier than all this.. :) I would have commented on your answer rather than updating my own but I haven't got the requisite 50 rep yet..

Answer 2

As I did a dive into NHibenrate documentation, it contains CriteriaAPI, so I came up to this

            using (var session = Database.OpenSession())
            {
                var products = session.CreateCriteria<Product>();

                if (names == null)
                {
                    return products.List<Product>();
                }

                var orClause = Expression.Disjunction();

                foreach (var name in names)
                {
                    orClause.Add(Expression.Like(nameof(Product.Name), name, MatchMode.Start));
                }

                products.Add(orClause);

                return products.List<Product>();
            }

Answer 3

You are trying to mix both kinds of conditions and applying IEnumerable methods on string properties.

Your query should look like this:

var result = products.Where(product => names.Contains(product.Name));

to find exact matches.

For a combination of exact matches and StartsWith it should look like this:

var results = products.Where(product => (names.Contains(product.Name) || names.Any(name => name.StartsWith(product.Name))));