在 NHibernate 中使用业务规则避免 N+1 选择

发布于 2024-07-25 06:26:39 字数 870 浏览 3 评论 0原文

我知道避免 Hibernate/NHibernate 中 N+1 选择问题的基本方法,但遇到了该问题的变体,我找不到好的解决方案。

我映射了以下三个实体:项目、类别和客户。 项目与类别多对多关联,类别与客户多对一映射。 到目前为止,没有什么特别的。

我的应用程序中的标准查询是获取给定客户的所有商品。 我使用以下标准执行此操作,尝试急切地获取项目的类别,以避免在检查项目的“类别”属性时进行 N+1 选择:

ICriteria criteria = mySession.CreateCriteria(typeof(Item));
    .CreateCriteria("Categories", NHibernate.SqlCommand.JoinType.InnerJoin)
        .Add(Expression.Eq("Customer", c));
criteria.SetFetchMode("Categories", FetchMode.Eager);

return criteria.List();

但是,这不起作用,NHibernate 稍后仍会通过每个项目的一个选择来获取类别在。

我相信发生的事情是 NHibernate 知道第一个查询的结果被过滤(在客户上),并且查询返回的类别可能不完整,因此它稍后必须执行单独的查询来获取类别。 (这个假设正确吗?NHibernate 必须以这种方式工作才能确保正确的结果,这对我来说似乎是合理的。)

但是,根据我的业务规则(或您想要的名称),一个项目不能属于更多类别比一个客户,所以实际上我知道第一个查询的结果实际上是完整的。

我的问题是:我可以以任何方式告诉NHibernate 这个业务规则吗? 在这种情况下是否有另一种方法可以避免 N+1 选择(这似乎很常见)?

I know the basic ways to avoid the N+1 selects problem in Hibernate/NHibernate, but have run into a variant of the problem that I can't find a good solution to.

I have the following three entities mapped: Item, Category and Customer. An item is associated many-to-many to Category, and a Category is mapped many-to-one to Customer. So far, nothing special.

A standard query in my application is to get all the items for a given customer. I do this using the following criteria, trying to fetch the items' categories eagerly, to avoid N+1 selects when examining the items' Categories property:

ICriteria criteria = mySession.CreateCriteria(typeof(Item));
    .CreateCriteria("Categories", NHibernate.SqlCommand.JoinType.InnerJoin)
        .Add(Expression.Eq("Customer", c));
criteria.SetFetchMode("Categories", FetchMode.Eager);

return criteria.List();

However, this doesn't work, NHibernate still fetches the categories with one select per item later on.

What I believe is going on is that NHibernate knows that the result from the first query is filtered (on Customer), and that the categories returned by the query might not be complete, hence it later has to do a separate query to get the categories. (Is this assumption correct? It seems reasonable to me that NHibernate must work this way to ensure correct results.)

However, according to my business rules (or what you want to call them), an item can't belong to categories from more than one customer, so in reality I know the result from the first query is actually complete.

My question is: can I tell NHibernate about this business rule in any way? Is there another way to avoid N+1 selects in this type of situation (which seems pretty common)?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

ヅ她的身影、若隐若现 2024-08-01 06:26:39

将尝试回答我自己的问题,因为到目前为止我还没有得到任何答案。

我的解决方案是将问题分为两个查询:首先获取属于相关客户的商品的 ID:

IQuery query = mySession.CreateQuery("select item.Id from Item as item "
    + "join item.Categories as category "
    + "join category.Customer customer "
    + "where customer.id=:id")
    .SetInt32("id", c.Id);
IList itemIds = query.List();

然后获取实际商品,而不涉及对客户的任何限制,仅使用 ID。 这样 NHibernate 就知道它可以从单个连接中获取所有类别,从而避免问题中提到的 N+1 选择:

ICriteria criteria = mySession.CreateCriteria(typeof(MapItem))
    .SetFetchMode("Categories", FetchMode.Eager)
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .Add(Expression.In("Id", itemIds));
IList items = criteria.List();

我无法想出任何解决方案来将其简化为有效的单个查询。 此外,这种方法迫使程序员对 NHibernate 的内部工作原理了解得太多,而且当您编写新的查询或条件时很容易错过这一点。 更通用的解决方案会更好。

Will try to answer my own question, since I haven't got any answer so far.

My solution is to divide the problem in to two queries: first get the IDs of the items belonging to the customer in question:

IQuery query = mySession.CreateQuery("select item.Id from Item as item "
    + "join item.Categories as category "
    + "join category.Customer customer "
    + "where customer.id=:id")
    .SetInt32("id", c.Id);
IList itemIds = query.List();

I then fetch the actual items without involving any restriction on customer, just using the IDs. This way NHibernate knows it can get all categories from a single join, avoiding the N+1 selects mentioned in the questions:

ICriteria criteria = mySession.CreateCriteria(typeof(MapItem))
    .SetFetchMode("Categories", FetchMode.Eager)
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .Add(Expression.In("Id", itemIds));
IList items = criteria.List();

I couldn't come up with any solution that reduced this to a working, single query. Also, this approach forces the programmer to know a little too much about NHibernate's inner workings, and it's really easy to miss when you're writing a new query or criteria. A more general solution would be preferable.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文