How to pass sub Select * to Join by fluent nhibernate

Question

Can NH already create JOIN SELECT queries like

 Select * From table1 Join ( Select * From table2 ...?

And why he can`t.

In my task I have a simple table:

TABLE [Message](
  [Id] [int] NOT NULL,  
  [DocId] [int] NOT NULL,
  [RecipientId] [bigint] NOT NULL,
  [Text] [nvarchar](255) NULL,
  [ArrivalDate] [date] NOT NULL

And Select:

SELECT msg.*
FROM [Message] msg
JOIN( SELECT msgMaxForDoc.DocId, MAX(msgMaxForDoc.ArrivalDate) AS ArrivalDate
      FROM [Message] msgMaxForDoc
      GROUP BY msgMaxForDoc.DocId) docAndDate ON msg.DocId = docAndDate.DocId AND msg.ArrivalDate = docAndDate.ArrivalDate
WHERE msg.RecipientId = @UserId

Full task sounds like: "Select all messages for user. If document (represented by DocId) contain more than one message, then get latest message". In result I must select one latest message for all DocId restricted by UserId

I found a similar question here: nhibernate queryover join with subquery to get aggregate column but it doesn't help (I don't have any links to another tablet DocId is just number).

And here Join a Subquery with NHibernate (but I can't separate select by two parts...)

score 0 · Answer 1 · answered Jul 15 '14 at 14:17

As far as I know, it's impossible to join on a sub query using NHibernate. However, you could probably achieve your goal using the "WHERE EXISTS" method:

var dCriteria = DetachedCriteria.For<Message>("grouped")
.SetProjection(Projections.GroupProperty("grouped.DocId"))
.SetProjection(Projections.Max("grouped.ArrivalDate"))
.Add(Restrictions.EqProperty("msg.DocId", "grouped.DocId"))
.Add(Restrictions.EqProperty("msg.ArrivalDate", "grouped.ArrivalDate"))
.Add(Restrictions.Eq("grouped.RecipientId", @UserId));

var userMessages = Session.CreateCriteria<Message>("msg")
.Add(Subqueries.Exists(dCriteria)).List<Message>();

How to pass sub Select * to Join by fluent nhibernate

1 Answers1