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

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);

Answer 2

This post has answers to how to do this, but you shouldn't always depend on TOP for getting the latest entry! (assuming chronological order)

Use a time/index column to get the latest entry based on a timestamp value value.

Answer 3

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.