n休眠 - 在按顺序选择中选择子选择

本文关键字：选择 顺序 休眠 | 更新日期: 2023-09-27 17:56:37

我有许多消息的对话，我想按照对话消息表中最后一条消息的创建日期的顺序对对话进行排序。

我将如何在 nHibernate 的查询中写这个。我应该使用子查询吗？

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

n休眠 - 在按顺序选择中选择子选择

通过对 C# 命名的一些猜测，NHibernate 映射...这将是这样：

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，我们将获得对话消息的最大（创建日期）

相关文章：