CODEWITHSUNDEEP
c#asp.netnhibernate
Blankman
Blankman

Reputation: 267120

nhibernate, Retrieve the latest row in a table

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?

Upvotes: 0

Views: 2807

Answers (3)

Mahesh Velaga
Mahesh Velaga

Reputation: 21971

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.

Upvotes: 1

dariol
dariol

Reputation: 1979

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

Upvotes: 4

Preet Sangha
Preet Sangha

Reputation: 65516

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.

Upvotes: 1

Related Questions