How to reuse where clauses in Linq To Sql queries

Question

I have users searching records of type Record. They type a search term in a textbox and then I search records by matching several fields with the search term.

My query looks like:

var results = from record in DataContext.Records
              where
                   record.Field1.ToLower().Contains(term) ||
                   record.Field2.ToLower().Contains(term) ||
                   record.Field3.ToLower().Contains(term)
              select record;

I have a number of queries that all use the same filter and thus I would like to extract the filtering so it can be reused. Something like:

var filter = new Func<Record, string, bool>(
                (record, term) =>
                    record.Field1.ToLower().Contains(term) ||
                    record.Field2.ToLower().Contains(term) ||
                    record.Field3.ToLower().Contains(term)
             );

var results = from record in DataContext.Records
              where filter(record, term)
              select record;

However, it does not work because:

Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

How can I reuse my where condition across queries?

Answer 1

You need to build an expression instead of a function:

Expression<Func<Record, bool>> filter = 
  record => record.Field1.ToLower().Contains(term);  // rest omitted

The lambda expression remains the same, but you need to return it into a variable of type Expression<Func<Record, bool>> -- that will make the C# compiler compile it as an expression instead of a delegate, allowing it to be passed to LINQ to SQL.

However, you won't be able to use an expression variable with a C#-syntax where clause: you'll need to use the Where extension method:

var results = DataContext.Records.Where(filter);

Edited to add: If you want to be able to create filters on different terms, you just need a method to produce an expression from a term:

private static Expression<Func<Record, bool>> Filter(string term)
{
  return r => r.Field1.ToLower().Contains(term);
}

var results = DataContext.Records.Where(Filter(term));

If you prefer to keep filter as a lambda as you have at the moment, you can do so, but the generics get a bit nested:

Func<string, Expression<Func<Record, bool>>> filter =
  term => (r => r.Field1.ToLower().Contains(term));

var results = DataContext.Records.Where(filter(term));

Regardless, the important thing is that what goes in the Where clause must be an Expression<Func<Record, bool>> -- but as shown above you can make the expression depend on term by building a suitable expression on the fly. Which is exactly what LINQ to SQL would be doing if you spelled out the filter longhand in the Where clause.

Answer 2

Use a CompiledQuery!

var filter = CompiledQuery.Compile(
    (DatabaseDataContext dc, Record record, string term) =>
        record.Field1.ToLower().Contains(term) ||
        record.Field2.ToLower().Contains(term) ||
        record.Field3.ToLower().Contains(term)
);

var results = from record in DataContext.Records
              where filter(DataContext, record, term)
              select record;

For more information, see How to: Store and Reuse Queries.