Nhibernate - Subselect In Select With Order By

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. How would I write this

Solution 1:

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

Conversation conversation = null;
ConversationMessage message = null;

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

// the alias of the Conversation to be injected into subqueryvar 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[]>()
    ;

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