NHibernate-从聚合中选择完整记录 [英] NHibernate - Select full records from aggregates

问题描述

简而言之:给定NHibernate中的聚合查询(一个具有Max，Min，Count等的查询)，如何修改查询以返回与聚合值关联的完整记录? /p>

我的示例:我有2个表:People(主键:PersonId)与Events具有一对多关系(主键:EventId；其他列:PersonId，EventDate).

我想选择每个人的最后一个事件，并生成这些事件的列表.每个人的最后事件的SQL类似于SELECT PersonId, Max(EventDate) FROM ... GROUP BY PersonId.到目前为止，NHibernate查询看起来像:

ICriteria criteria = session.CreateCriteria<Event>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("PersonId"))
        .Add(Projections.Max("EventDate"))
    );

现在我真正需要的是完整的事件信息.从理论上讲，一种解决方案是实质上通过PersonId和最大EventDate将上述条件连接到Events表(在普通SQL中足够容易).但是我不知道如何在NHibernate中执行此操作.

我愿意接受任何建议(HQL，LINQ等)，只要它避免存储过程和视图并且限于1个或几个查询即可.就我而言，按人发出查询将无法扩展或执行.

解决方案

希望您也对QueryOver开放(可以将其转换为ICriteria)...

Event eventAlias = null;

var topEventsByPerson = Session.QueryOver<Event>(() => eventAlias)
    .WithSubquery.WhereProperty(x => x.EventId).Eq(QueryOver.Of<Event>()
        .Where(x => x.Person == eventAlias.Person)
        .OrderBy(x => x.EventDate).Desc
        .Select(x => x.EventId)
        .Take(1))
    .List();

In short: Given an aggregate query (one with Max, Min, Count, etc) in NHibernate, how can you modify the query to also return the full record associated with the aggregated value?

My example: I have 2 tables: People (primary key: PersonId) with a 1-to-many relationship to Events (primary key: EventId; other columns: PersonId, EventDate).

I want to select the last event per person and generate a list of these events. The SQL for last event per person would be something like SELECT PersonId, Max(EventDate) FROM ... GROUP BY PersonId. So far the NHibernate query looks like:

ICriteria criteria = session.CreateCriteria<Event>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("PersonId"))
        .Add(Projections.Max("EventDate"))
    );

Now what I really need is the full event info. One solution, in theory, is to essentially join the above criteria to the Events table by PersonId and the max EventDate (easy enough in plain SQL). However I'm at a loss of how to perform this in NHibernate.

I'm open to any suggestion (HQL, LINQ, etc.) so long as it avoids stored procedures and views and is limited to 1 or just a few queries. Issuing a query per Person will not be scalable or performant in my case.

解决方案

I hope you are also open to QueryOver (this can be converted to ICriteria)...

Event eventAlias = null;

var topEventsByPerson = Session.QueryOver<Event>(() => eventAlias)
    .WithSubquery.WhereProperty(x => x.EventId).Eq(QueryOver.Of<Event>()
        .Where(x => x.Person == eventAlias.Person)
        .OrderBy(x => x.EventDate).Desc
        .Select(x => x.EventId)
        .Take(1))
    .List();

这篇关于NHibernate-从聚合中选择完整记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！