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.

OrderID，OrderDate，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选择实体和聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！