C# 精心设计的查询命令和/或规范

Well designed query commands and/or specifications

提问by Erik Funkenbusch

I've been searching for quite some time for a good solution to the problems presented by the typical Repository pattern (growing list of methods for specialized queries, etc.. see: http://ayende.com/blog/3955/repository-is-the-new-singleton).

我一直在寻找一个很好的解决方案来解决典型的存储库模式（不断增长的专门查询方法列表等）所带来的问题。请参阅：http: //ayende.com/blog/3955/repository-是新的单身人士）。

I really like the idea of using Command queries, particularly through use of the Specification pattern. However, my problem with specification is that it only relates to the criteria of simple selections (basically, the where clause), and does not deal with the other issues of queries, such as joining, grouping, subset selection or projection, etc.. basically, all the extra hoops many queries must go through to get the correct set of data.

我真的很喜欢使用命令查询的想法，特别是通过使用规范模式。但是，我对规范的问题是它只涉及简单选择的标准（基本上是 where 子句），不处理查询的其他问题，例如连接、分组、子集选择或投影等。基本上，许多查询必须经过所有额外的环节才能获得正确的数据集。

(note: I use the term "command" as in the Command pattern, also known as query objects. I'm not talking about command as in command/query separation where there is a distinction made between queries and commands (update, delete, insert))

（注意：我在命令模式中使用术语“命令”，也称为查询对象。我不是在命令/查询分离中谈论命令，其中查询和命令（更新、删除、插入））

So I'm looking for alternatives that encapsulate the entire query, but still flexible enough that you're not just swapping spaghetti Repositories for an explosion of command classes.

所以我正在寻找封装整个查询的替代方案，但仍然足够灵活，您不仅仅是为了命令类的爆炸而交换意大利面条式存储库。

I've used, for instance Linqspecs, and while I find some value in being able to assign meaningful names to selection criteria, it's just not enough. Perhaps i'm seeking a blended solution that combines multiple approaches.

我已经使用过，例如 Linqspecs，虽然我发现能够为选择标准分配有意义的名称有一些价值，但这还不够。也许我正在寻找一种结合多种方法的混合解决方案。

I am looking for solutions that others may have developed to either address this problem, or address a different problem but still satisfies these requirements. In the linked article, Ayende suggests using the nHibernate context directly, but I feel that largely complicates your business layer because it now also has to contain query information.

我正在寻找其他人可能已经开发出来的解决方案来解决这个问题，或者解决一个不同的问题，但仍然满足这些要求。在链接的文章中，Ayende 建议直接使用 nHibernate 上下文，但我觉得这在很大程度上使您的业务层复杂化，因为它现在还必须包含查询信息。

I'll be offering a bounty on this, as soon as the waiting period elapses. So please make your solutions bounty worthy, with good explanations and I will select the best solution, and upvote the runners up.

等待期一过，我就会悬赏。因此，请让您的解决方案有价值，并提供良好的解释，我将选择最佳解决方案，并为亚军投票。

NOTE: I'm looking for something that is ORM based. Doesn't have to be EF or nHibernate explicitly, but those are the most common and would fit the best. If it can be easily adapted to other ORM's that would be a bonus. Linq compatible would also be nice.

注意：我正在寻找基于 ORM 的东西。不必明确是 EF 或 nHibernate，但它们是最常见的并且最适合。如果它可以很容易地适应其他 ORM，那将是一个奖励。Linq 兼容也很好。

UPDATE: I'm really surprised that there aren't many good suggestions here. It seems like people are either totally CQRS, or they're completely in the Repository camp. Most of my apps are not complex enough to warrant CQRS (something with most CQRS advocates readily say that you should not use it for).

更新：我真的很惊讶这里没有很多好的建议。人们似乎要么完全是 CQRS，要么完全属于 Repository 阵营。我的大多数应用程序都不够复杂，不足以保证 CQRS（大多数 CQRS 倡导者很容易说你不应该使用它）。

UPDATE: There seems to be a little confusion here. I'm not looking for a new data access technology, but rather a reasonably well designed interface between business and data.

更新：这里似乎有点混乱。我不是在寻找新的数据访问技术，而是在寻找业务和数据之间设计合理的接口。

Ideally, what i'm looking for is some kind of cross between Query objects, Specification pattern, and repository. As I said above, Specification pattern only deals with the where clause aspect, and not the other aspects of the query, such as joins, sub-selects, etc.. Repositories deal with the whole query, but get out of hand after a while. Query objects also deal with the whole query, but I don't want to simply replace repositories with explosions of query objects.

理想情况下，我正在寻找的是查询对象、规范模式和存储库之间的某种交叉。正如我上面所说，规范模式只处理 where 子句方面，而不处理查询的其他方面，例如连接、子选择等。存储库处理整个查询，但一段时间后会失控. 查询对象也处理整个查询，但我不想简单地用查询对象的爆炸式替换存储库。

采纳答案by david.s

Disclaimer:Since there aren't any great answers yet, I decided to post a part from a great blog post I read a while ago, copied almost verbatim. You can find the full blog post here. So here it is:

免责声明：由于还没有任何好的答案，我决定发布我前段时间阅读的一篇很棒的博客文章中的一部分，几乎是逐字复制的。您可以在此处找到完整的博客文章。所以这里是：

---

---

We can define the following two interfaces:

我们可以定义以下两个接口：

public interface IQuery<TResult>
{
}

public interface IQueryHandler<TQuery, TResult> where TQuery : IQuery<TResult>
{
    TResult Handle(TQuery query);
}

The IQuery<TResult>specifies a message that defines a specific query with the data it returns using the TResultgeneric type. With the previously defined interface we can define a query message like this:

的IQuery<TResult>指定定义与它返回使用的数据的特定的查询消息TResult的通用类型。使用之前定义的接口，我们可以定义这样的查询消息：

public class FindUsersBySearchTextQuery : IQuery<User[]>
{
    public string SearchText { get; set; }
    public bool IncludeInactiveUsers { get; set; }
}

This class defines a query operation with two parameters, which will result in an array of Userobjects. The class that handles this message can be defined as follows:

这个类定义了一个带有两个参数的查询操作，这将产生一个User对象数组。处理此消息的类可以定义如下：

public class FindUsersBySearchTextQueryHandler
    : IQueryHandler<FindUsersBySearchTextQuery, User[]>
{
    private readonly NorthwindUnitOfWork db;

    public FindUsersBySearchTextQueryHandler(NorthwindUnitOfWork db)
    {
        this.db = db;
    }

    public User[] Handle(FindUsersBySearchTextQuery query)
    {
        return db.Users.Where(x => x.Name.Contains(query.SearchText)).ToArray();
    }
}

We can now let consumers depend upon the generic IQueryHandlerinterface:

我们现在可以让消费者依赖于通用IQueryHandler接口：

public class UserController : Controller
{
    IQueryHandler<FindUsersBySearchTextQuery, User[]> findUsersBySearchTextHandler;

    public UserController(
        IQueryHandler<FindUsersBySearchTextQuery, User[]> findUsersBySearchTextHandler)
    {
        this.findUsersBySearchTextHandler = findUsersBySearchTextHandler;
    }

    public View SearchUsers(string searchString)
    {
        var query = new FindUsersBySearchTextQuery
        {
            SearchText = searchString,
            IncludeInactiveUsers = false
        };

        User[] users = this.findUsersBySearchTextHandler.Handle(query);    
        return View(users);
    }
}

Immediately this model gives us a lot of flexibility, because we can now decide what to inject into the UserController. We can inject a completely different implementation, or one that wraps the real implementation, without having to make changes to the UserController(and all other consumers of that interface).

该模型立即为我们提供了很大的灵活性，因为我们现在可以决定向UserController. 我们可以注入一个完全不同的实现，或者一个包装真实实现的实现，而不必对UserController（以及该接口的所有其他使用者）进行更改。

The IQuery<TResult>interface gives us compile-time support when specifying or injecting IQueryHandlersin our code. When we change the FindUsersBySearchTextQueryto return UserInfo[]instead (by implementing IQuery<UserInfo[]>), the UserControllerwill fail to compile, since the generic type constraint on IQueryHandler<TQuery, TResult>won't be able to map FindUsersBySearchTextQueryto User[].

在我们的代码中IQuery<TResult>指定或注入时，该接口为我们提供了编译时支持IQueryHandlers。当我们将FindUsersBySearchTextQuery改为 return 时UserInfo[]（通过实现IQuery<UserInfo[]>），UserController将无法编译，因为 上的泛型类型约束IQueryHandler<TQuery, TResult>将无法映射FindUsersBySearchTextQuery到User[]。

Injecting the IQueryHandlerinterface into a consumer however, has some less obvious problems that still need to be addressed. The number of dependencies of our consumers might get too big and can lead to constructor over-injection - when a constructor takes too many arguments. The number of queries a class executes can change frequently, which would require constant changes into the number of constructor arguments.

IQueryHandler然而，将接口注入消费者，还有一些不太明显的问题需要解决。我们的消费者的依赖项数量可能会变得太大，并可能导致构造函数过度注入 - 当构造函数接受太多参数时。一个类执行的查询数量可能会经常变化，这需要不断更改构造函数参数的数量。

We can fix the problem of having to inject too many IQueryHandlerswith an extra layer of abstraction. We create a mediator that sits between the consumers and the query handlers:

我们可以IQueryHandlers通过额外的抽象层来解决注入过多的问题。我们创建了一个位于消费者和查询处理程序之间的中介：

public interface IQueryProcessor
{
    TResult Process<TResult>(IQuery<TResult> query);
}

The IQueryProcessoris a non-generic interface with one generic method. As you can see in the interface definition, the IQueryProcessordepends on the IQuery<TResult>interface. This allows us to have compile time support in our consumers that depend on the IQueryProcessor. Let's rewrite the UserControllerto use the new IQueryProcessor:

的IQueryProcessor是一个非通用接口与一个通用的方法。正如您在接口定义中看到的，IQueryProcessor依赖于IQuery<TResult>接口。这使我们能够在依赖于IQueryProcessor. 让我们重写UserController以使用 new IQueryProcessor：

public class UserController : Controller
{
    private IQueryProcessor queryProcessor;

    public UserController(IQueryProcessor queryProcessor)
    {
        this.queryProcessor = queryProcessor;
    }

    public View SearchUsers(string searchString)
    {
        var query = new FindUsersBySearchTextQuery
        {
            SearchText = searchString,
            IncludeInactiveUsers = false
        };

        // Note how we omit the generic type argument,
        // but still have type safety.
        User[] users = this.queryProcessor.Process(query);

        return this.View(users);
    }
}

The UserControllernow depends on a IQueryProcessorthat can handle all of our queries. The UserController's SearchUsersmethod calls the IQueryProcessor.Processmethod passing in an initialized query object. Since the FindUsersBySearchTextQueryimplements the IQuery<User[]>interface, we can pass it to the generic Execute<TResult>(IQuery<TResult> query)method. Thanks to C# type inference, the compiler is able to determine the generic type and this saves us having to explicitly state the type. The return type of the Processmethod is also known.

在UserController现在依赖于IQueryProcessor能够处理所有的查询。所述UserController的SearchUsers方法调用IQueryProcessor.Process传递初始化的查询对象的方法。由于FindUsersBySearchTextQuery实现了IQuery<User[]>接口，我们可以将其传递给泛型Execute<TResult>(IQuery<TResult> query)方法。由于 C# 类型推断，编译器能够确定泛型类型，这使我们不必显式声明类型。该Process方法的返回类型也是已知的。

It is now the responsibility of the implementation of the IQueryProcessorto find the right IQueryHandler. This requires some dynamic typing, and optionally the use of a Dependency Injection framework, and can all be done with just a few lines of code:

现在是IQueryProcessor找到合适的执行的责任IQueryHandler。这需要一些动态类型，并可选择使用依赖注入框架，并且只需几行代码即可完成：

sealed class QueryProcessor : IQueryProcessor
{
    private readonly Container container;

    public QueryProcessor(Container container)
    {
        this.container = container;
    }

    [DebuggerStepThrough]
    public TResult Process<TResult>(IQuery<TResult> query)
    {
        var handlerType = typeof(IQueryHandler<,>)
            .MakeGenericType(query.GetType(), typeof(TResult));

        dynamic handler = container.GetInstance(handlerType);

        return handler.Handle((dynamic)query);
    }
}

The QueryProcessorclass constructs a specific IQueryHandler<TQuery, TResult>type based on the type of the supplied query instance. This type is used to ask the supplied container class to get an instance of that type. Unfortunately we need to call the Handlemethod using reflection (by using the C# 4.0 dymamic keyword in this case), because at this point it is impossible to cast the handler instance, since the generic TQueryargument is not available at compile time. However, unless the Handlemethod is renamed or gets other arguments, this call will never fail and if you want to, it is very easy to write a unit test for this class. Using reflection will give a slight drop, but is nothing to really worry about.

在QueryProcessor类构造一个特定的IQueryHandler<TQuery, TResult>基础上，提供的查询实例的类型类型。此类型用于要求提供的容器类获取该类型的实例。不幸的是，我们需要Handle使用反射调用该方法（在这种情况下使用 C# 4.0 dymamic 关键字），因为此时无法强制转换处理程序实例，因为泛型TQuery参数在编译时不可用。但是，除非该Handle方法被重命名或获取其他参数，否则此调用永远不会失败，如果您愿意，为此类编写单元测试非常容易。使用反射会略有下降，但没什么好担心的。

---

---

To answer one of your concerns:

回答您的问题之一：

So I'm looking for alternatives that encapsulate the entire query, but still flexible enough that you're not just swapping spaghetti Repositories for an explosion of command classes.

所以我正在寻找封装整个查询的替代方案，但仍然足够灵活，您不仅仅是为了命令类的爆炸而交换意大利面条式存储库。

A consequence of using this design is that there will be a lot of small classes in the system, but having a lot of small/focused classes (with clear names) is a good thing. This approach is clearly much better then having many overloads with different parameters for the same method in a repository, as you can group those in one query class. So you still get a lot less query classes than methods in a repository.

使用这种设计的结果是系统中会有很多小类，但是有很多小类/重点类（名称清晰）是一件好事。这种方法显然比在存储库中为同一方法使用许多具有不同参数的重载要好得多，因为您可以将它们分组到一个查询类中。因此，与存储库中的方法相比，您获得的查询类仍然少得多。

回答by Olivier Jacot-Descombes

You can use a fluent interface. The basic idea is that methods of a class return the current instance this very class after having performed some action. This allows you to chain method calls.

您可以使用流畅的界面。基本思想是类的方法在执行某些操作后返回该类的当前实例。这允许您链接方法调用。

By creating an appropriate class hierarchy, you can create a logical flow of accessible methods.

通过创建适当的类层次结构，您可以创建可访问方法的逻辑流。

public class FinalQuery
{
    protected string _table;
    protected string[] _selectFields;
    protected string _where;
    protected string[] _groupBy;
    protected string _having;
    protected string[] _orderByDescending;
    protected string[] _orderBy;

    protected FinalQuery()
    {
    }

    public override string ToString()
    {
        var sb = new StringBuilder("SELECT ");
        AppendFields(sb, _selectFields);
        sb.AppendLine();

        sb.Append("FROM ");
        sb.Append("[").Append(_table).AppendLine("]");

        if (_where != null) {
            sb.Append("WHERE").AppendLine(_where);
        }

        if (_groupBy != null) {
            sb.Append("GROUP BY ");
            AppendFields(sb, _groupBy);
            sb.AppendLine();
        }

        if (_having != null) {
            sb.Append("HAVING").AppendLine(_having);
        }

        if (_orderBy != null) {
            sb.Append("ORDER BY ");
            AppendFields(sb, _orderBy);
            sb.AppendLine();
        } else if (_orderByDescending != null) {
            sb.Append("ORDER BY ");
            AppendFields(sb, _orderByDescending);
            sb.Append(" DESC").AppendLine();
        }

        return sb.ToString();
    }

    private static void AppendFields(StringBuilder sb, string[] fields)
    {
        foreach (string field in fields) {
            sb.Append(field).Append(", ");
        }
        sb.Length -= 2;
    }
}

public class GroupedQuery : FinalQuery
{
    protected GroupedQuery()
    {
    }

    public GroupedQuery Having(string condition)
    {
        if (_groupBy == null) {
            throw new InvalidOperationException("HAVING clause without GROUP BY clause");
        }
        if (_having == null) {
            _having = " (" + condition + ")";
        } else {
            _having += " AND (" + condition + ")";
        }
        return this;
    }

    public FinalQuery OrderBy(params string[] fields)
    {
        _orderBy = fields;
        return this;
    }

    public FinalQuery OrderByDescending(params string[] fields)
    {
        _orderByDescending = fields;
        return this;
    }
}

public class Query : GroupedQuery
{
    public Query(string table, params string[] selectFields)
    {
        _table = table;
        _selectFields = selectFields;
    }

    public Query Where(string condition)
    {
        if (_where == null) {
            _where = " (" + condition + ")";
        } else {
            _where += " AND (" + condition + ")";
        }
        return this;
    }

    public GroupedQuery GroupBy(params string[] fields)
    {
        _groupBy = fields;
        return this;
    }
}

You would call it like this

你会这样称呼它

string query = new Query("myTable", "name", "SUM(amount) AS total")
    .Where("name LIKE 'A%'")
    .GroupBy("name")
    .Having("COUNT(*) > 2")
    .OrderBy("name")
    .ToString();

You can only create a new instance of Query. The other classes have a protected constructor. The point of the hierarchy is to "disable" methods. For instance, the GroupBymethod returns a GroupedQuerywhich is the base class of Queryand does not have a Wheremethod (the where method is declared in Query). Therefore it is not possible to call Whereafter GroupBy.

您只能创建 的新实例Query。其他类有一个受保护的构造函数。层次结构的重点是“禁用”方法。例如，该GroupBy方法返回 a GroupedQuery，它是 的基类Query并且没有Where方法（ where 方法在 中声明Query）。因此无法在Where之后调用GroupBy。

It is however not perfect. With this class hierarchy you can successively hide members, but not show new ones. Therefore Havingthrows an exception when it is called before GroupBy.

然而它并不完美。使用此类层次结构，您可以连续隐藏成员，但不能显示新成员。因此Having在之前调用时抛出异常GroupBy。

Note that it is possible to call Whereseveral times. This adds new conditions with an ANDto the existing conditions. This makes it easier to construct filters programmatically from single conditions. The same is possible with Having.

请注意，可以Where多次调用。这AND向现有条件添加了带有 的新条件。这使得从单个条件以编程方式构建过滤器变得更加容易。也可以使用Having.

The methods accepting field lists have a parameter params string[] fields. It allows you to either pass single field names or a string array.

接受字段列表的方法有一个参数params string[] fields。它允许您传递单个字段名称或字符串数​​组。

---

---

Fluent interfaces are very flexible and do not require you to create a lot of overloads of methods with different combinations of parameters. My example works with strings, however the approach can be extended to other types. You could also declare predefined methods for special cases or methods accepting custom types. You could also add methods like ExecuteReaderor ExceuteScalar<T>. This would allow you to define queries like this

Fluent 接口非常灵活，不需要您创建大量具有不同参数组合的方法重载。我的示例适用于字符串，但是该方法可以扩展到其他类型。您还可以为特殊情况或接受自定义类型的方法声明预定义的方法。您还可以添加像ExecuteReader或这样的方法ExceuteScalar<T>。这将允许您定义这样的查询

var reader = new Query<Employee>(new MonthlyReportFields{ IncludeSalary = true })
    .Where(new CurrentMonthCondition())
    .Where(new DivisionCondition{ DivisionType = DivisionType.Production})
    .OrderBy(new StandardMonthlyReportSorting())
    .ExecuteReader();

Even SQL commands constructed this way can have command parameters and thus avoid SQL injection problems and at the same time allow commands to be cached by the database server. This is not a replacement for an O/R-mapper but can help in situations where you would create the commands using simple string concatenation otherwise.

即使以这种方式构造的 SQL 命令也可以具有命令参数，从而避免 SQL 注入问题，同时允许数据库服务器缓存命令。这不是 O/R 映射器的替代品，但可以帮助您使用简单的字符串连接创建命令的情况。

回答by MikeSW

My way of dealing with that is actually simplistic and ORM agnostic. My view for a repository is this: The repository's job is to provide the app with the model required for the context, so the app just asks the repo for whatit wants but doesn't tell it howto get it.

我处理这个问题的方式实际上很简单，而且与 ORM 无关。我对存储库的看法是这样的：存储库的工作是为应用程序提供上下文所需的模型，因此应用程序只询问存储库它想要什么，但不告诉它如何获取它。

I supply the repository method with a Criteria (yes, DDD style), which will be used by the repo to create the query (or whatever is required - it may be a webservice request). Joins and groups imho are details of how, not the what and a criteria should be only the base to build a where clause.

我为存储库方法提供了一个 Criteria（是的，DDD 风格），repo 将使用它来创建查询（或任何需要的东西 - 它可能是一个 web 服务请求）。加入和组恕我直言是如何的细节，而不是什么和标准应该只是构建 where 子句的基础。

Model = the final object or data structure neede by the app.

模型 = 应用程序需要的最终对象或数据结构。

public class MyCriteria
{
   public Guid Id {get;set;}
   public string Name {get;set;}
    //etc
 }

 public interface Repository
  {
       MyModel GetModel(Expression<Func<MyCriteria,bool>> criteria);
   }

Probably you can use the ORM criteria (Nhibernate) directly if you want it. The repository implementation should know how to use the Criteria with the underlying storage or DAO.

如果需要，您可能可以直接使用 ORM 标准（Nhibernate）。存储库实现应该知道如何将 Criteria 与底层存储或 DAO 一起使用。

I don't know your domain and the model requirements but it would be strange if the best way is that the app to build the query itself. The model changes so much that you can't define something stable?

我不知道您的域和模型要求，但如果最好的方法是应用程序本身来构建查询，那将会很奇怪。模型变化如此之大，以至于您无法定义稳定的东西？

This solution clearly requires some additional code but it doesn't couple the rest of the to an ORM or whatever you're using to access the storage. The repository does its job to act as a facade and IMO it's clean and the 'criteria translation' code is reusable

这个解决方案显然需要一些额外的代码，但它不会将其余的代码与 ORM 或您用来访问存储的任何东西结合起来。存储库的工作是充当外观，IMO 很干净，并且“标准翻译”代码是可重用的

回答by Stu

I've done this, supported this and undone this.

我已经做到了这一点，支持这一点并撤消了这一点。

The major problem is this: no matter how you do it, the added abstraction does not gain you independence. It will leak by definition. In essence, you're inventing an entire layer just to make your code look cute... but it does not reduce maintenance, improve readability or gain you any type of model agnosticism.

主要问题是：无论你怎么做，增加的抽象都不会让你获得独立性。根据定义，它会泄漏。从本质上讲，你发明了一个完整的层只是为了让你的代码看起来很可爱……但它不会减少维护、提高可读性或让你获得任何类型的模型不可知论。

The fun part is that you answered your own question in response to Olivier's response: "this is essentially duplicating the functionality of Linq without all the benefits you get from Linq".

有趣的是，您在回答 Olivier 的回答时回答了自己的问题：“这实际上是在复制 Linq 的功能，而没有从 Linq 中获得的所有好处”。

Ask yourself: how could it not be?

问问自己：怎么可能呢？