Is it possible to get a lazy IEnumerable from an NHibernate query using ICriteria?

Question

I'm working with NHibernate and need to retrieve and process up to 2 million rows. Ideally, I could process each row - one at a time - without NHibernate loading all 2 million in memory at once (because, you know, that hurts).

I'd prefer to get an IEnumerable which would call the data reader iteratively for each read so I could process the data read - then discard it. By doing it this way I save a boatload of memory, and begin processing results far faster. I could also improve performance through multithreading and/or the use of PLinq.

Is this possible with NHibernate's ICriteria? Everything it returns seems to be IList, and fully loaded before handing the collection reference off. Why IList instead of IEnumerable?!

I don't mean "lazy" in the traditional sense that NHibernate uses with regards to loading child or parent objects. I want a lazy IEnumerable meaning someway of getting a IEnumerable from an ICriteria object. ICriteria only has a List() method which loads the results in an ArrayList.

Answer 1

What you want to do is wrap your data access in a method like so:

public IEnumerable<YourObject> GetALotOfRows() {
  ..execute DataReader
  while(..read..) {
    yield return yourObject;
  }
}

Don't have VS or nHibernate handy now, so sorry for semi-pseudo code. But the key here is to use "yield return".

Answer 2

What kind of operation is it that you have to do it row by row ? I'm just curious :).

You could try to page the results - get the first 10, the next 10 ... and so on.

EDIT: So you would have

Session.CreateCriteria(typeof(T)).SetFirstResult(0).SetMaxResults(1).UniqueResult<T>();
Session.CreateCriteria(typeof(T)).SetFirstResult(1).SetMaxResults(1).UniqueResult<T>();
Session.CreateCriteria(typeof(T)).SetFirstResult(2).SetMaxResults(1).UniqueResult<T>();

You get the picture, I guess it's not the best way, it's not IEnumerable ... but it would work. You could also do SetMaxResults(10) or something bigger, so not to send 1 at a time.

Answer 3

ICriteria doesn't have any methods that return an IEnumerable, but IQuery does.