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

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.

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)

If you have these extension methods:

public static class BoolExtensions
{
    public static bool And<TR, TC>(this IEnumerable<Func<TR, TC, bool>> statements, TR value, TC criteria)
    {
        foreach (var statement in statements)
        {
            if (!statement.Invoke(value, criteria))
            {
                return false;
            }
        }

        return true;
    }

    public static bool Or<TR, TC>(this IEnumerable<Func<TR, TC, bool>> statements, TR value, TC criteria)
    {
        foreach (var statement in statements)
        {
            if (statement.Invoke(value, criteria))
            {
                return true;
            }
        }

        return false;
    }
}

Then you could declare your statements as a list:

List<Func<TypeOfR, TypeOfC, bool>> statements = new List<Func<TypeOfR, TypeOfC, bool>>()
{
    { (r, c) => r.Id == c.SampleId },
    { (r, c) => r.Status.SampleStatusId == c.SampleStatusId },
    ...
};

And write your query as either:

var results = from r in Table where
        statements.And(r, criteria)
    select r;

or for the || version:

var results = from r in Table where
        statements.Or(r, criteria)
    select r;

and just maintain the statements in one place.

Dynamic LINQ

Perhaps simpler than Jens' idea to visualize, if you're only looking for combined Or and combined And (and not some mix) you could always express your equality as a list of tests, and then apply the Any or All operators to it. For example:

var queries = new List<Func<Table,SampleListDto,bool>>{
      ((a,b) => a.Id == b.SampleId),
      ((a,b) => a.Status.SampleStatusId == b.SampleStatusId),
      ((a,b) => a.Job.JobNumber.StartsWith(b.JobNumber)),
      ((a,b) => a.Description.Contains(b.Description))
};

var results = Table.Where(t=> queries.All(q => q(t, criteria)); // returns the && case
// or:  var results = Table.Where(t=>queries.Any(q=>q(t,criteria));

As w/ Jens', no idea how efficiently this translates into SQL, but if that becomes an issue then you're probably better off converting it to native SQL anyway.

Here is information on PredicateBuilder

This should be compatible with LINQ to SQL.

A new function could be created to use the PredicateBuilder functions And or Or:

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

}

The And and Or functions would look like:

private IQueryable<SampleListDto> GetOrSampleSearchQuery(
    SamplesCriteria criteria) 
{ 
    return GetSampleSearchQuery(criteria, PredicateBuilder.Or<SampleListDto>);
}
private IQueryable<SampleListDto> GetAndSampleSearchQuery(
    SamplesCriteria criteria)
{
    return GetSampleSearchQuery(criteria, PredicateBuilder.And<SampleListDto>);
}

You could use a T4 template to generate each method. That way you will still have strongly-typed expressions. Right click your project and select add->New Item->Text Template

The template would loop like this:

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ output extension=".cs" #>

namespace YourNamespaceName
{
    public partial class YourClassName
    {
    <# Generate(true); #>
    <# Generate(false); #>
    }
}
<#+
    private void Generate(bool isOr)
    {
        string op = isOr ? "||" : "&&";
        string methodName = "GetSampleSearchQuery" + (isOr ? "Or" : "And");
#>
    private IQueryable<SampleListDto> <#= methodName #>(SamplesCriteria criteria)
    {
        var results = from r in Table where
                (r.Id == criteria.SampleId) <#= op #>
                (r.Status.SampleStatusId == criteria.SampleStatusId) <#= op #>
                (r.Job.JobNumber.StartsWith(criteria.JobNumber)) <#= op #>
                (r.Description.Contains(criteria.Description))
            select r;
        return results;
    }
<#+
    }
#>

Just change your class to be partial and add any other needed filters to the T4 template and use <#= op #> instead of the operator. (Right click the T4 template in the solution explorer and select Run Custom Tool to force it to update) You'll end up with two methods: GetSampleSearchQueryOr and GetSampleSearchQueryAnd for an Or and an And filter repetitively, both maintained in just one place.

You can use the technique explained in this answer. The Compose method can be called thus:- first.Compose(second, Expression.And); or first.Compose(second, Expression.Or);

So the type of operation becomes just a parameter.

By repeatedly applying this to all of your clauses you can build the required expression, and it's all strongly-typed and pure Expression code.