使用 NHibernate 3.0.0 时对抗笛卡尔积(x-join)

本文介绍了使用 NHibernate 3.0.0 时对抗笛卡尔积(x-join)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我的数学很差，但我知道笛卡尔积是什么.
这是我的情况(简化):

公共类项目{公共 IList合作伙伴{get;set;}}公开课合作伙伴{公共 IList成本{get;set;}公共 IList地址{get;set;}}公共类 PartnerCosts{公共资金总额{get;set;}}公开课金钱{公共十进制金额{get;set;}公共 int CurrencyCode{get;set;}}公共类地址{公共字符串 Street{get;set;}}

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

问题当然是:

如果我尝试急切加载合作伙伴及其成本，查询将返回无数行
如果我延迟加载 Partner.Costs，db 会收到垃圾请求(比第一种方法快一点)

据我所知，常见的解决方法是使用 MultiQueries，但我有点不明白.
所以我希望通过这个确切的例子来学习.

如何有效加载整个项目?

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

解决方案

好的，我为自己写了一个例子来反映你的结构，这应该有效:

int projectId = 1;//用你想要的 id 替换它//QueryOver 中的连接需要项目 pAlias = null;合作伙伴 paAlias = null;PartnerCosts pcAlias = null;地址 aAlias = null;货币别名 = 空；//查询加载所需的项目，没有别的var projects = repo.Session.QueryOver(() => pAlias).Where(p => p.Id == projectId).未来();//查询以使用成本(和金钱)加载合作伙伴var Partners = repo.Session.QueryOver(() => paAlias).JoinAlias(p => p.Project, () => pAlias).Left.JoinAlias(() => paAlias.Costs, () => pcAlias).JoinAlias(() => pcAlias.Money, () => mAlias).Where(() => pAlias.Id == projectId).Future();//查询以加载具有地址的合作伙伴var Partners2 = repo.Session.QueryOver(() => paAlias).JoinAlias(o => o.Project, () => pAlias).Left.JoinAlias(() => paAlias.Addresses, () => aAlias).Where(() => pAlias.Id == projectId).Future();//执行此操作时，三个查询在一次往返中执行var list = projects.ToList();项目项目 = list.FirstOrDefault();

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

说明:

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

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

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

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.

Problem of course is:

If I try to eager load partners and their costs, query returns gazillion rows
If I lazy load Partner.Costs, db gets request spammed (which is a bit faster than first approach)

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.

How to effectively load whole Project?

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.

Explanation:

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().

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 Project2) x rows with the Partners and their Costs (and the Money), where x is the total number of Costs for the Project's Partners3) y rows with the Partners and their Addresses, where y is the total number of Addresses for the Project's Partners

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)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！