nhibernate, Retrieve the latest row in a table

Question

A user can have many addresses, but I want to retrieve the latest entry for the user.

In sql I would do:

SELECT TOP 1 *
FROM UserAddress
WHERE userID = @userID

How can I create a criteria query with the same logic?

Is there a TOP functionality?

Answer 1

The following post has answers to how to do this:

http://stackoverflow.com/questions/555045/nhibernate-hqls-equivalent-to-t-sqls-top-keyword

But, you shouldn't always depend on TOP for getting the latest entry! (assuming chronological order)

Have a time/index column to get the latest entry based on their value.

Answer 2

Since the ordering of the contents of a table are subject to movement (reindexing etc), I'd suggest that you have a time stamp of some description to indicate which is the latest. Then get the first ordered by that field.

Answer 3

Assuming that you have some timestamp column (eg. InsertedAt):

    User user = ...;
    var crit = DetachedCriteria.For<UserAddress>()
        .Add(Restrictions.Eq("User", user))
        .AddOrder(Order.Desc("InsertedAt"))
        .SetMaxResults(1);