使用NHibernate 3.0.0时要对抗笛卡尔乘积(x-join) [英] Fighting cartesian product (x-join) when using NHibernate 3.0.0

问题描述

我数学不好，但是我很了解笛卡尔积是什么.
这是我的情况(简体):

I'm bad at math but I kind get idea what cartesian product is.
Here is my situation (simplified):

public class Project{
 public IList<Partner> Partners{get;set;}
}
public class Partner{
 public IList<PartnerCosts> Costs{get;set;}
 public IList<Address> Addresses{get;set;}
}
public class PartnerCosts{
 public Money Total{get;set;}
}
public class Money{
 public decimal Amount{get;set;}
 public int CurrencyCode{get;set;}
}
public class Address{
 public string Street{get;set;}
}

我的目标是有效地加载整个项目.

My aim is to effectively load entire Project.

问题当然是:

• 如果我想急于加载合作伙伴及其成本，查询将返回数以万亿计的行
• 如果我延迟加载Partner.Costs，db会收到垃圾邮件请求(比第一种方法快一点)

如我所读，常见的解决方法是使用MultiQueries，但我有点不理解.
因此，我希望通过这个确切的例子来学习.

As I read, common workaround is to use MultiQueries, but I kind a just don't get it.
So I'm hoping to learn through this exact example.

如何有效加载整个项目?

P.s.我正在使用NHibernate 3.0.0.
请不要使用hql或字符串形式的标准api方法发布答案.

P.s. I'm using NHibernate 3.0.0.
Please, do not post answers with hql or string fashioned criteria api approaches.

推荐答案

好，我为自己写了一个反映您的结构的示例，它应该可以工作:

Ok, I wrote an example for myself reflecting your structure and this should work:

int projectId = 1; // replace that with the id you want
// required for the joins in QueryOver
Project pAlias = null;
Partner paAlias = null;
PartnerCosts pcAlias = null;
Address aAlias = null;
Money mAlias = null;

// Query to load the desired project and nothing else    
var projects = repo.Session.QueryOver<Project>(() => pAlias)
    .Where(p => p.Id == projectId)
    .Future<Project>();

// Query to load the Partners with the Costs (and the Money)
var partners = repo.Session.QueryOver<Partner>(() => paAlias)
    .JoinAlias(p => p.Project, () => pAlias)
    .Left.JoinAlias(() => paAlias.Costs, () => pcAlias)
    .JoinAlias(() => pcAlias.Money, () => mAlias)
    .Where(() => pAlias.Id == projectId)
    .Future<Partner>();

// Query to load the Partners with the Addresses
var partners2 = repo.Session.QueryOver<Partner>(() => paAlias)
    .JoinAlias(o => o.Project, () => pAlias)
    .Left.JoinAlias(() => paAlias.Addresses, () => aAlias)
    .Where(() => pAlias.Id == projectId)
    .Future<Partner>();

// when this is executed, the three queries are executed in one roundtrip
var list = projects.ToList();
Project project = list.FirstOrDefault();

我的班级名称不同，但反映的结构完全相同.我替换了名字，希望没有错别字.

My classes had different names but reflected the exact same structure. I replaced the names and I hope there are no typos.

说明:

联接需要别名.我定义了三个查询来加载所需的Project，Partners及其Costs和Partners及其Addresses.通过使用.Futures()，我基本上告诉NHibernate在我实际想要结果的那一刻，使用projects.ToList()在一次往返中执行它们.

The aliases are required for the joins. I defined three queries to load the Project you want, the Partners with their Costs and the Partners with their Addresses. By using the .Futures() I basically tell NHibernate to execute them in one roundtrip at the moment when I actually want the results, using projects.ToList().

这将导致三个SQL语句确实在一次往返中执行.这三个语句将返回以下结果: 1)与您的专案一排 2)与合作伙伴及其成本(和金钱)共x行，其中x是项目合作伙伴的成本总数 3)与合作伙伴及其地址共y行，其中y是项目合作伙伴的地址总数

This will result in three SQL statements that are indeed executed in one roundtrip. The three statements will return the following results: 1) 1 row with your Project 2) x rows with the Partners and their Costs (and the Money), where x is the total number of Costs for the Project's Partners 3) y rows with the Partners and their Addresses, where y is the total number of Addresses for the Project's Partners

您的数据库应返回1 + x + y行，而不是x * y行，这将是笛卡尔积.我希望您的数据库确实支持该功能.

Your db should return 1+x+y rows, instead of x*y rows, which would be a cartesian product. I do hope that your DB actually supports that feature.

这篇关于使用NHibernate 3.0.0时要对抗笛卡尔乘积(x-join)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！