gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-05 01:36:38 25 4
gpt4 key购买 nike

我有这样一个实体:

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

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

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

我希望 hibernate 一次获取所有信息,在 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 的 Employment 实体。但是,我不知道如何...

编辑:添加映射

数据库表:

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");
}
}

最佳答案

在你编辑之后,我看到你有一个键引用而不是正常的多对一。

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

using NHibernate.Linq;

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

关于join - 从数据库中获取带有连接实体的整个实体并避免延迟加载,nHibernate QueryOver,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/10498490/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com