CODEWITHSUNDEEP
nhibernatelinq-to-nhibernatenhibernate-criteriaqueryover
alloha
alloha

Reputation: 131

nhibernate group by and join query

I need nhiberante query (not HQL) equivalent following SQL:

SELECT ur.*
FROM (SELECT MAX(requestTime) rt, macAddress ma
    FROM UpdateRequests
    GROUP BY macAddress) mur
JOIN dbo.UpdateRequests ur
ON mur.ma = ur.macAddress AND mur.rt = ur.requestTime

I had no luck with other similar examples on stackoverflow. Having UpdateRequest mapping, it seems that is not possible with Query API, how about QueryOver?

Upvotes: 1

Views: 323

Answers (1)

alloha
alloha

Reputation: 131

Finally one Guru suggested me to change SQL query without changing execution plan:

SELECT  ur.*
FROM    [dbo].[UpdateRequests] AS ur
WHERE   ur.[RequestTime] = (SELECT MAX(mur.[RequestTime])
                            FROM   [dbo].[UpdateRequests] mur
                            WHERE  mur.[MacAddress] = ur.[MacAddress])

So in code it transforms into: 

session
    .Query<UpdateRequest>()
    .Where(ur => ur.RequestTime == session.Query<UpdateRequest>()
                                          .Where(mur => mur.MacAddress == ur.MacAddress)
                                          .Max(mur => mur.RequestTime))
    .ToList();

And this is exactly what i need.

Upvotes: 1

Related Questions