Limit number of rows deleted via HQL

[DmitryMak]

There is currently no way to limit number of rows that should be deleted via HQL query. This is a very common scenario when implementing retention policies on large databases. See Chunking section here: Big Deletes. Example:

session
        .CreateQuery("delete from Widgets where _toDelete = true")

        .SetMaxResults(1_000) <--- NOT SUPPORTED (ignored silently)

        .ExecuteUpdate();

This code will not work if the number of rows matching the predicate is large. There seem to be no way to batch this. The workarounds are not ideal:

1. Reduce number of rows via predicate (e.g. when deleting by timestamps, delete last N seconds). This is brittle because timestamps may not be heterogeneous.
2. Use native SQL. E.g. LIMIT N in MySQL. Downside is that it makes code db dependent, hard to unit test against in-memory db.
3. Load objects before deleting them. This is obviously wasteful and has serious performance implications.
4. Use larger timeouts. This is brittle.

Can the limit be added to HQL deletes? It seems to be supported by all major databases.