Nhibernate Criteria: 'select max(id)...'

Question

Can I use a Criteria to execute a t-sql command to select the max value for a column in a table?

'select @cus_id = max(id) + 1 from customers'

Ta

Ollie

Answer 1

Use Projection:

session.CreateCriteria(typeof(Customer))
  .SetProjection( Projections.Max("Id") )
  . UniqueResult();

Answer 2

Max(id) + 1 is a very bad way to generate ids. If that's your goal, find another way to generate ids.

Edit: in answer to LnDCobra:

it's bad because it's hard to make sure that the max(id) you got is still the max(id) when you do the insert. If another process inserts a row, your insert will have the same id, and your insert will fail. (Or, conversely, the other process's insert will fail if your insert happened first.)

To prevent this, you have to prevent any other inserts/make your get and subsequent insert atomic, which generally means locking the table, which will hurt performance.

If you only lock against writes, the other process gets max(id), which is the same max(id) you got. You do your insert and release the lock, it inserts a duplicate id and fails. Or it tries to lock too, in which case it waits on you. If you lock against reads too, everybody waits on you. If it locks against writes also, then it doesn't insert the duplicate id, but it does wait on your read and your write.

(And it breaks encapsulation: you should let the rdbms figure out its ids, not the client programs that connect to it.)

Generally, this strategy will either:
* break
* require a bunch of "plumbing" code to make it work
* significantly reduce performance
* or all three

and it will be slower, less robust, and require more hard to maintain code than just using the RDBMS's built in sequences or generated autoincrement ids.