从数据库中获取具有连接实体的整个实体，并避免延迟加载，nHibernate QueryOver

Question

我有一个这样的实体：

public class Employment
{
    public virtual Company Company {get; set;}
    public virtual Person Person {get; set;}
    public virtual string Description {get; set;}
}

提供其他两个实体之间的关系。他们有相应的 DTO，我想返回一个包含有关个人和公司的所有信息的结果集。查询是在就业表上执行的，我的问题是 Hibernate 为每个公司和个人生成一个选择语句。

在我的数据库中，就业表有 1000 行。Nhibernate生成2001个选择语句，一个用于就业列表，一个用于每个个人和公司，因为我将它们映射到DTO的。

我希望休眠一次获取所有信息，在SQL中我会做这样的事情：

SELECT e.Description, c.A, c.B, c.C, p.D, p.E, p.F
FROM Employment e
JOIN Company c ON e.Company_Id = c.Company_Id
JOIN Person p ON e.Person_Id = p.Person_Id;

甚至

SELECT Description FROM Employment;
SELECT c.A, c.B, c.C FROM Employment e
JOIN Company c ON e.Company_Id = c.Company_Id;
SELECT p.D, p.E, p.F FROM Employment e
JOIN Person p ON e.Person_Id = p.Person_Id;

我是nHibernate，QueryOver的新手用户，我也欢迎Linq-To-Entities的答案，但我更喜欢避免使用LINQ查询表达式。

我在网上看遍了，阅读了JoinQuery，JoinAlias和Fetch，并得出了这样的结论：

//This works, but the objects are retrieved as PersonProxy and CompanyProxy,
//generating 2 SELECT statements for each Employment I map to EmploymentDto
var queryOver =
    session.QueryOver<Employment>()
    .Fetch(x => x.Person).Eager
    .Fetch(x => x.Company).Eager
var mapResult = MappingEngine.Map<IList<EmploymentDto>>(queryOver.List());    

//This works, but the objects are still retrieved as PersonProxy and CompanyProxy,
var queryOver =
    session.QueryOver<Employment>()
        .JoinAlias(x => x.Person, () => personAlias, JoinType.InnerJoin)
        .JoinAlias(x => x.Company, () => companyAlias, JoinType.InnerJoin);
var mapResult = MappingEngine.Map<IList<EmploymentDto>>(queryOver.List());

JoinQuery也提供了相同的结果。我觉得我在这里错过了一些重要的东西。应在查询中或之前执行某些操作。List(( 获取所有子实体，而不是加载包含大量使用 PersonProxy 和 CompanyProxy 的就业实体的列表。但是，我无法找出如何...

编辑：添加了映射

数据库表：

TABLE Company(
Id,
A,
B,
C)
TABLE Person(
Id,
D,
E,
F);
TABLE Employment(
Person_Id,
Company_Id,
Description);

实体

public class Company
{
    public virtual string Id { get; set; }
    public virtual string A { get; set; }
    public virtual bool B { get; set; }
    public virtual bool C { get; set; }
}
public class Person
{
    public virtual string Id { get; set; }
    public virtual string D { get; set; }
    public virtual string E { get; set; }
    public virtual string F { get; set; }
}
public class Employment
{
    public virtual Person Person { get; set; }
    public virtual Company Company { get; set; }
    public virtual string Description { get; set; }
    public override bool Equals(object obj)
    {
        Employment toCompare = obj as Employment;
        if (toCompare == null)
            return false;
        return (this.GetHashCode() != toCompare.GetHashCode());
    }
    public override int GetHashCode()
    {
        unchecked
        {
            int results = Person != null ? Person.GetHashCode() : 0;
            results = (results * 397) ^ (Company != null ? Company.GetHashCode() : 0);
            results = (results * 397) ^ (Description != null ? Description.GetHashCode() : 0);
            return results;
        }
    }
}

映射

public class CompanyMap : SyncableClassMap<Company>
{
    public CompanyMap()
    {
        Table("Company");
        Id(x => x.Id).Column("Id").GeneratedBy.Assigned();
        Map(x => x.A).Column("A");
        Map(x => x.B).Column("B").CustomType<YesNoType>();
        Map(x => x.C).Column("C").CustomType<YesNoType>();
    }
}
public class PersonMap : SyncableClassMap<Person>
{
    public PersonMap()
    {
        Table("Person");
        Id(x => x.Id).Column("Id").GeneratedBy.Assigned();
        Map(x => x.D).Column("D");
        Map(x => x.E).Column("E");
        Map(x => x.F).Column("F");
    }
}
public class EmploymentMap : ClassMap<Employment>
{
    public EmploymentMap()
    {
        Table("Employment");
        CompositeId()
            .KeyReference(x => x.Person, "Person_Id")
            .KeyReference(x => x.Company, "Company_Id");
        Map(x => x.Description, "Description");
    }
}

Answer 1

在您

编辑后，我看到您有一个键引用而不是正常的多对一。

不幸的是，这似乎是QueryOver/Criteria的一个限制，即使指定了Fetchmode，它也不会急于加载键引用。但是，Linq to NH 没有此限制。将查询更改为

using NHibernate.Linq;
var results = session.Query<Employment>()
    .Fetch(x => x.Person)
    .Fetch(x => x.Company)
    .ToList();

Answer 2

我遇到了你在这里描述的同样的问题。我将以您的最后一个代码片段为例，因为这是我使其工作的方式：

//This works, but the objects are still retrieved as PersonProxy and CompanyProxy,
var queryOver =
    session.QueryOver<Employment>()
        .JoinAlias(x => x.Person, () => personAlias, JoinType.InnerJoin)
        .JoinAlias(x => x.Company, () => companyAlias, JoinType.InnerJoin);
var mapResult = MappingEngine.Map<IList<EmploymentDto>>(queryOver.List());

首先，您不必指定JoinType.InnerJoin因为这是默认的联接类型。和你一样，我也发现个人和公司都是这样懒洋洋地加载的。

但是，如果将联接类型更改为 JoinType.LeftOuterJoin ，您将看到所有内容都急切加载。至少这是我所经历的。因此，请尝试将代码更改为以下内容：

//This works, but the objects are still retrieved as PersonProxy and CompanyProxy,
var queryOver =
    session.QueryOver<Employment>()
        .JoinAlias(x => x.Person, () => personAlias, JoinType.LeftOuterJoin)
        .JoinAlias(x => x.Company, () => companyAlias, JoinType.LeftOuterJoin);
var mapResult = MappingEngine.Map<IList<EmploymentDto>>(queryOver.List());

我

无法向你解释为什么会这样，我只是根据自己的经验发现这是有效的。如果执行左外连接有问题，则可以在执行映射之前(或同时(尝试在代码中进行相应的过滤。