假设有一个相对简单的领域模型,涉及订单、商品和发货,其中订单是根实体,发货是根实体。我想查找给定订单的所有发货。查询非常直接,但我在NHibernate中看到了不希望看到的行为。
模型
public class Order
{
public Order(){ Items = new List<LineItem>(); }
public virtual int Id { get; private set; }
public virtual DateTime Created { get; set; }
public virtual IList<LineItem> Items { get; private set; }
}
public class LineItem
{
public virtual int Id { get; private set; }
public virtual int Quantity { get; set; }
public virtual Order Order { get; set; }
}
public class Shipment
{
public virtual int Id { get; private set; }
public virtual DateTime Date { get; set; }
public virtual LineItem LineItem { get; set; }
}
LINQ
使用NHibernate.Linq进行此查询:
var shipments = from shipment in session.Linq<Shipment>()
where shipment.LineItem.Order == order
select shipment;
以下 SQL 查询的结果:
SELECT this_.Id as Id5_2_,
this_.Date as Date5_2_,
this_.LineItem_id as LineItem3_5_2_,
lineitem1_.Id as Id4_0_,
lineitem1_.Quantity as Quantity4_0_,
lineitem1_.Order_id as Order3_4_0_,
order2_.Id as Id3_1_,
order2_.Created as Created3_1_,
order2_.IsClosed as IsClosed3_1_
FROM [Shipment] this_
left outer join [LineItem] lineitem1_
on this_.LineItem_id = lineitem1_.Id
left outer join [Order] order2_
on lineitem1_.Order_id = order2_.Id
WHERE lineitem1_.Order_id = 1 /* @p0 */
生成的Shipment对象是正确的,但查询加载了太多数据,因为我只对装运日期感兴趣。订单和行项目数据会立即被丢弃并且从未使用。我已经尝试使用延迟加载以及我能在网上找到的每种获取策略,但我无法让它简单地返回基本数据。
如何减少SQL查询中的噪声,以便仅加载装运数据和行项目的主键以支持延迟加载?类似于这样的内容:
SELECT this_.Id as Id5_2_,
this_.Date as Date5_2_,
this_.LineItem_id as LineItem3_5_2_,
lineitem1_.Id as Id4_0_,
FROM [Shipment] this_
inner outer join [LineItem] lineitem1_
on this_.LineItem_id = lineitem1_.Id
WHERE lineitem1_.Order_id = 1 /* @p0 */
自定义SQL查询(更新)
使用类似下面的自定义SQL查询可以得到所需的性能和正确的行为。但这有点违背了ORM的初衷。为什么NHibernate不能生成如此简单的查询呢?
Session
.CreateSQLQuery(
@"SELECT SH.*, LI.Id FROM Shipment SH
INNER JOIN LineItem LI ON LI.Id = SH.LineItem_id
WHERE LI.Order_id = ?" )
.SetInt32( 0, order.Id )
.List<Shipment>();