Linq to SQL Where clause based on field selected at runtime

Question

I'm trying to create a simple reusable search using LINQ to SQL.

I pass in a list of words entered in a search box. The results are then filtered based on this criteria.

  private IQueryable<User> BasicNameSearch(IQueryable<User> usersToSearch, ICollection<string> individualWordsFromSearch)
  {
        return usersToSearch
            .Where(user => 
            individualWordsFromSearch.Contains(user.Forename.ToLower()) 
            || individualWordsFromSearch.Contains(user.Surname.ToLower()));
  }

Now I want this same search functionality on a different datasource and want to dynamically select the fields to apply the search to. For instance instead of IQueryable of Users I may have an IQueryable of Cars and instead of firstname and surname the search goes off Make and Model. Basically the goal is to reuse the search logic by dynamically selecting what to search on at runtime.

Answer 1

You could create an extension method that will compile your string selectors together into one expression:

public static class CompileExpressions
{
    public static IQueryable<T> SearchTextFieldsOr<T>(this IQueryable<T> source,
        ICollection<string> wordsFromSearch, params Func<T, string>[] stringSelectors)
    {
        Expression<Func<T, bool>> compiledExpression = t => false;

        foreach (var filter in stringSelectors)
        {
            compiledExpression = compiledExpression.Or(t => wordsFromSearch.Contains(filter(t)));
        }

        var compiled = compiledExpression.Compile();

        return source.Where(t => compiled(t));
    }

    public static IQueryable<T> SearchTextFieldsAnd<T>(this IQueryable<T> source,
        ICollection<string> wordsFromSearch, params Func<T, string>[] stringSelectors)
    {
        foreach (var filter in stringSelectors)
        {
            source = source.Where(t => wordsFromSearch.Contains(filter(t)));
        }

        return source;
    }

    //Taken from http://www.albahari.com/nutshell/predicatebuilder.aspx
    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);
    }
}

An example of how this could be used:

public class Entity
{
    public string Name { get; set; }
    public string Type { get; set; }
    public string Model { get; set; }
    public string Colour { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        var source = new[]{
            new Entity { Colour = "Red", Model = "New", Name="Super", Type="Filter"},
            new Entity { Colour = "Green", Model = "New", Name="Super", Type="Filter"},
            new Entity { Colour = "Green", Model = "New", Name="Super", Type="Filter"},
            new Entity { Colour = "Green", Model = "New", Name="Super", Type="Filter"},
            new Entity { Colour = "Green", Model = "New", Name="Super", Type="Filter"},
            new Entity { Colour = "Green", Model = "New", Name="Super", Type="Amazing"},
        };

        var filters = new[] {"Red", "Amazing" };

        var filteredOr = source
               .AsQueryable()
               .SearchTextFieldsOr(filters, t => t.Colour, t => t.Type)
               .ToList();

        //2 records found because we're filtering on "Colour" OR "Type"

        var filteredAnd = source
               .AsQueryable()
               .SearchTextFieldsAnd(filters, t => t.Colour, t => t.Type)
               .ToList();

         //1 record found because we're filtering on "Colour" AND "Type"

    }
}

Because your string selector argument is of type params Func<T, string>[], you can add as many string selectors as you want to be included in your query.

Answer 2

Your question is similar to this thread's (not the same question though).

In a nutshell, when you write a linq-to-sql request, it only builds a System.Linq.Expression corresponding to the actual code you typed which is given to a "provider", which will translate it to sql (you can get this provider by casting your request to IQueryable, which has a Provider property).

In fact, the code forming you request will never be "executed", and is actually not even compiled to IL like delegates you would pass to Linq-to-objects functions. (which is using System.Linq.Enumerable extension methods, while linq-to-sql is using System.Linq.Queryable ones)

Thus, you may also create linq expressions "manually" (instead of letting c# compiler build it for you under the hood), and pass them to the provider, which will parse and execute them as if you created them using the regular way.

See examples in the thread given above.

edit: or you could just look at Oliver's answer, who gave you a copy-paste-run sample :)