NHibernate QueryOver 选择实体和聚合

问题描述

我想要做的是显示一个简单的数据网格，其中包含实体数据及其子项的聚合数据.例如，让我们使用订单和订单项.我想显示订单信息，以及订单项的数量.

What I want to do is display a simple grid of data which contains the entity data, and the aggregate data of its children. For example lets use a Order and line items. I want to display the order information, and the count of line items.

订单 ID、订单日期、NumOfLineItems

OrderID, OrderDate, NumOfLineItems

现在通常在 SQL 中，您可以通过多种方式实现.但这是我能想到的在翻译到 NHibernate 时可能有效的唯一方法.

Now normally in SQL you can do it many ways. But this is the only way I could think of that might work when translating to NHibernate.

SELECT o.OrderID, OrderDate, NumOfLineItems
FROM #Orders o
INNER JOIN
(SELECT o2.OrderID, COUNT(*) As NumOfLineItems FROM #LineItems l
INNER JOIN #Orders o2 ON o2.OrderID = l.OrderID
WHERE UserID = 1 GROUP BY o2.OrderID) t1 ON o.OrderID = t1.OrderID
WHERE UserID = 1

我知道还有其他方法，但我正在尝试考虑 NHibernate 允许我使用 QueryOver 语法的方法.我不想使用派生列.我试图避免编写 SQL.

I know there are other ways, but I'm trying to think of ways that NHibernate will allow me to do using QueryOver syntax. I do not want to use derived columns. I am trying to avoid writing SQL.

对于我的实体，我有一个 Order 实体和一个 AggregatedOrder 实体，在本例中将是我的 DTO，我计划使用转换器 aliastobean 将数据复制到其中.

For my entities I have an Order entity, and an AggregatedOrder entity, which will be my DTO in this case, and I plan to use the transformer aliastobean to copy the data into it.

我完全不知道如何解决这个问题.

I just have absolutely no idea how to figure this out.

到目前为止我所拥有的:

All I have so far:

        QueryOver<LineItem> x = QueryOver.Of<LineItem>()
            .SelectList(p => p .SelectCount(l => l.Id).WithAlias(() => itemAlias.NumOfLineItems))
            .JoinQueryOver<Order>(l => l.Order)
            .Where(o => o.UserID == userID)


        var y = session.QueryOver<Listing>()
            .JoinQueryOver<Bid>(x); // no idea whats going on here

推荐答案

鉴于:

public class Order
{
    public virtual int OrderId {get; set;}
    public virtual DateTime OrderDate {get; set;}
    public virtual IList<LineItem> LineItems {get; set;}
}
public class LineItem
{
    public virtual int Id {get; set;}
    public virtual string Description {get; set;}
}

要使用 QueryOver API 查询 Order + Aggregated LineItem 的投影，您可以执行以下操作:

To query a projection of Order + Aggregated LineItem using QueryOver API you can do the following:

OrderDto orderDto = null;
LineItem items = null;
var results = session.QueryOver<Order>()
     .JoinAlias(o => o.LineItems, () => items)
     .Select(Projections.ProjectionList()
      .Add(Projections.Property<Order>(o=>o.Id).WithAlias(()=>orderDto.OrderId))
      .Add(Projections.Property<Order>(o=>o.DateOrdered).WithAlias(()=>orderDto.DateOrdered))
      .Add(Projections.Count(()=> items.Id).WithAlias(()=>orderDto.ItemCount))
     )
     .TransformUsing(Transformers.AliasToBean<OrderDto>())
    .List<OrderDto>();

这篇关于NHibernate QueryOver 选择实体和聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！