LINQ to SQL的 - 如何有效地完成要么是AND或OR搜索多个标准 [英] LINQ to SQL - How to efficiently do either an AND or an OR search for multiple criteria

问题描述

我有一个ASP.NET MVC网站（使用LINQ到SQL的ORM）并在客户端需要对一个定制的数据库搜索工具，由此他们可以选择要么做一个和搜索的情况（所有标准比赛）或或搜索（任何条件相匹配）。查询是相当复杂和漫长，我想知道是否有一个简单的方法，我可以把它无需拥有创建和维护两个不同版本的查询一举两得。

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.

推荐答案

如果您有这些扩展方法：

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.

这篇关于LINQ to SQL的 - 如何有效地完成要么是AND或OR搜索多个标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！