LINQ to SQL - 如何有效地对多个条件进行 AND 或 OR 搜索

问题描述

我有一个 ASP.NET MVC 站点(它使用 Linq To Sql 作为 ORM)和一个客户想要一个针对定制数据库的搜索工具的情况，他们可以选择执行AND"搜索(所有条件匹配)或或"搜索(任何条件匹配).该查询非常复杂且冗长，我想知道是否有一种简单的方法可以让我同时执行这两项操作，而无需创建和维护两个不同版本的查询.

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.

例如，当前的AND"搜索看起来像这样(但这是一个简化版):

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;

}

我可以复制这个并替换 &&与 ||操作员获得OR"版本，但觉得必须有更好的方法来实现这一点.有没有人对如何以一种易于维护的高效灵活的方式实现这一点有任何建议?谢谢.

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.

推荐答案

如果你有这些扩展方法:

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 },
    ...
};

并将您的查询写为:

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

或者对于 || 版本:

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

只需将这些语句保存在一处.

and just maintain the statements in one place.