NHibernate QueryOver Where with internal select

Question

I have a problem with translating this SQL into QueryOver notation. Can you help me?

SELECT * FROM Alerts a
WHERE a.CreatedOn = (
    SELECT MAX(b.CreatedOn) FROM Alerts b WHERE b.UserFk=a.UserFk);

I try to select last alert for every user. I use CreatedOn and cannot use Id.

I have so far:

session.QueryOver(() => alertAlias)
        .SelectList(list => list
            .Select(() => alertAlias.User.Id)
            .SelectSubQuery(
                QueryOver.Of<Alerts>()
                    .Where(x => x.User.Id == alertAlias.User.Id)
                    .OrderBy(x => x.CreatedOn).Desc
                    .Select(x => x.CreatedOn)
                    .Take(1)));

I know it adds user's last alert date to every user's alert row. But I want to have only last alerts.

Please, show us the code where you have a problem. You'll surely get some help then. Meanwhile take a look [here](http://stackoverflow.com/a/20537260/1679310) or [there](http://stackoverflow.com/a/22838831/1679310) or... — Radim Köhler, Oct 15 '14 at 08:36

score 0 · Accepted Answer · edited Jun 10 '16 at 16:19

Your attempt is using subquery inside of a SELECT statement. But we need to move it into WHERE. This should be the way:

// this is a subquery (SELECT ....
var subquery = QueryOver.Of<Alerts>()
        .Where(x => x.User.Id == alertAlias.User.Id)
        .OrderBy(x => x.CreatedOn).Desc
        .Select(x => x.CreatedOn)
        .Take(1)));

// main Query could now have or do not have that subquery selected
var query = session.QueryOver(() => alertAlias)
    .SelectList(list => list
        .Select(() => alertAlias.User.Id)
        // could be there
        .SelectSubQuery(subquery)
    )
    // but mostly here we do use WHERE clause
    .WithSubquery
        .WhereProperty(() => alertAlias.CreatedOn)
        .Eq(subquery)
        ;

// such a query could be returned as a list of arrays
var results = query.List<object[]>();

We can also use some Result Transformer, but this is another story...

Thanks! I wasn't aware of .WithSubquery.WhereProperty. Links from your first comment helped also but this approach is simpler. — Kamil, Oct 16 '14 at 13:24
Great to see that sir ;) NHibernate is amazing tool... but a bit challenging to learn... but later ;) Good luck — Radim Köhler, Oct 16 '14 at 13:24

NHibernate QueryOver Where with internal select

1 Answers1