LINQ to SQL - How to efficiently do either an AND or an OR search for multiple criteria
- by Dan Diplo
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.
