NHIbernate DataReader Equivalent?

Question

I'm somewhat new to NHibernate, so this question may be a no-brainer. Anyway, I'm replacing a project at work that uses ADO.NET that will now be using NHibernate(With LinqToNhibernate). This has worked fine so far, but I've run up against a function that needs to search through a large amount of data(about 200,000 records, test each record using a very specific algorithm(something too complicated for LINQ), and if it matches the criteria I want to add it to the results list. In ADO.NET I used a data reader so I wouldn't have to load all the results in memory. Is there an equivalent way to do this in NHibernate? Would the lazy loading be used for this? Thanks!

Answer 1

Ok from what I remember with the ISession.CreateQuery you can return a list or an enumerable. The List will load all of them into memory (I believe). The IEnumerable will issue a statement to the server to retrieve all of the Ids from the server. Every time you iterate through the ienumerable, it'll check the cache and if it's not in the cache, it'll issue a database call to retrieve the information.

Neither of them is what you want. You either use a ton of memory or hammer the DB.

I really sounds like you're trying to do some kind of ETL operation. Your best bet is to keep a couple of specialized pieces of code around to handle this situation.

If you insist on using NHibernate I would just page through the results in manageable chunks and use stateless sessions here:

http://davybrion.com/blog/2008/10/bulk-data-operations-with-nhibernates-stateless-sessions/

Answer 2

As you might know, when using NHibernate you have to approach working with data in a completely different manner. You're no longer with 'raw data' that comes from the DB like you do in ADO.NET; rather you're working with an OO view on your data.

You do not query the database / tables in NHibernate, but instead, you're querying your entities.

I think that the solution for your problem, can be found in projections. With a projection, you can create your query in NHibernate (using HQL or the ICriteria API).
But, before you execute the query, you specify, via a projection, which properties you're interested in.
NHibernate will then execute a query which retrieves only those properties. This also means that it is not really the entities that are returned. It is rather a DTO that is returned.

So, you will have to create a class which will act as this 'DTO'. It only has the properties that you're interested in, and you also have to 'import' this class so that it is known to NHibernate.

A while ago, I explained on SO how you could use projections. It can be found here

Answer 3

For an edge case backward compatibility like the one you have, you can just create a DbCommand and ExecuteReader on it, like this

        using (var session = this.SessionFactory.OpenSession())
        {
            using (DbCommand command = session.Connection.CreateCommand())
            {
                command.Connection = session.Connection;
                command.CommandText = queryString;

                Command.CommandTimeout = 60 * 100;
                Command.CommandType = CommandType.Text;

                var parameter = Command.CreateParameter();
                parameter.ParameterName = "@ParamName";
                parameter.Value = whatever_value;
                Command.Parameters.Add(parameter);


                var dataReader = Command.ExecuteReader(CommandBehavior.CloseConnection);

                // TODO: Enumerate your data reader
            }
        }