c# - LINQ to SQL - How to efficiently do either an AND or an OR search for multiple criteria

Question

I have an ASP.NET MVC site (which uses Linq To Sql for the ORM) and a situation where a client wants a search facility against a bespoke database whereby they can choose to either do an 'AND' search (all criteria match) or an 'OR' search (any criteria match). The query is quite complex and long and I want to know if there is a simple way I can make it do both without having to have create and maintain two different versions of the query.

For instance, the current 'AND' search looks something like this (but this is a much simplified version):

private IQueryable<SampleListDto> GetSampleSearchQuery(SamplesCriteria criteria)
{
   var results = from r in Table where
            (r.Id == criteria.SampleId) &&
            (r.Status.SampleStatusId == criteria.SampleStatusId) &&
            (r.Job.JobNumber.StartsWith(criteria.JobNumber)) &&
            (r.Description.Contains(criteria.Description))
        select r;

}

I could copy this and replace the && with || operators to get the 'OR' version, but feel there must be a better way of achieving this. Does anybody have any suggestions how this can be achieved in an efficient and flexible way that is easy to maintain? Thanks.

Answer 1

You could create an extension method along the lines of

public static IQueryable<T> BoolWhere<T>(this IQueryable<T> source, Expression<Func<T, TValue>> selector, bool isOr) {
  //use isOr value to determine what expression to build and add to the source
}

where 'isOr' will determine whether to use an 'and' expression or an 'or' expression. Then you can construct your query along the lines of

bool isOr = true; //or false
var results = Data.BoolWhere(r => r.Id == criteria.SampleId, isOr)
  .BoolWhere(r => r.Status.SampleStatusId == criteria.SampleStatusId, isOr)
  .BoolWhere(r => r.Job.JobNumber.StartsWith(criteria.JobNumber), isOr)
  .BoolWhere(r => r.Description.Contains(criteria.Description), isOr)