nHibernate-按选择按顺序进行子选择 [英] nHibernate - subselect in select with order by

问题描述

我的对话中包含许多消息，我想按ConversationMessages表中最后一条消息的创建日期顺序对对话进行排序.

I have conversations with many messages and I want to order the conversations in order of the created date of the last message in ConversationMessages table.

我该如何在nHibernate的queryOver中编写此代码.我应该使用subQuery吗?

How would I write this in a queryOver in nHibernate. Should I use subQuery?

select distinct c.ConversationId,
  (select max(CreatedDate) 
    from tConversationMessage mess where mess.ConversationId = c.ConversationId
  ) as LastMessage 
from tConversation c order by LastMessage

推荐答案

对您的C#命名，NHibernate映射有所猜想……这可能是这样的方式:

With some guessing of your C# naming, NHibernate mapping... this would be the way:

Conversation conversation = null;
ConversationMessage message = null;

// the subselect of a max
var subQuery = QueryOver.Of<ConversationMessage>(() => message)
    .Where(() => message.Conversation.ID == conversation.ID)
    .Select(Projections.Max("CreatedDate"));

// the alias of the Conversation to be injected into subquery
var query = session.QueryOver<Conversation>(() => conversation);

// a select and group by (distinct) clause
query.SelectList(l => l
    .SelectGroup(s => conversation.ID)
    .Select(Projections.SubQuery(subQuery))
    );

// Order by the max Created date (asc)
var list = query
    .OrderBy(Projections.SubQuery(subQuery))
        .Asc
    .List<object[]>()
    ;

，对于每个会话ID，我们将获取ConversationMessage的max(CreateDate)

and for each Conversation ID we will get the max(CreateDate) of ConversationMessage

这篇关于nHibernate-按选择按顺序进行子选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！