本文介绍了nHibernate-按选择按顺序进行子选择的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的对话中包含许多消息,我想按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-按选择按顺序进行子选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-13 17:12