Does streaming large result sets with NHibernate actually work?

I'm using the method that ayende proposed here:

http://ayende.com/Blog/archive/2010/06/27/nhibernate-streaming-large-result-sets.aspx

on SQL Server 2005 with NHibernate 3.0.

My tests (trying to stream a result set with 10,000,000 items) indicate that the results are not being streamed (it appears the entire result set is loaded into memory first).

How can I get this to work? I will accept any solution that permits streaming of result sets in NHibernate, it doesn't have to be exactly ayend开发者_开发知识库e's solution.

You can stream results from Linq with the following extension method with NH 4.0.0.4000 (Pass ISessionImplementor in if you don't like the reflection hack):

public static EnumerableImpl Stream<T>(this IQueryable<T> source)
{
    var provider = ((NhQueryable<T>) source).Provider as DefaultQueryProvider;
    var sessionImpl = (ISessionImplementor)provider.GetType()
        .GetProperty("Session", BindingFlags.NonPublic | 
            BindingFlags.Instance).GetValue(provider);
    var expression = new NhLinqExpression(source.Expression, sessionImpl.Factory);
    var query = sessionImpl.CreateQuery(expression);
    query.SetParameters(expression.ParameterValuesByName);
    provider.SetResultTransformerAndAdditionalCriteria(
        query, expression, expression.ParameterValuesByName);
    return (EnumerableImpl)((AbstractQueryImpl2)query).Enumerable();
}

private static void SetParameters(this IQuery query, 
    IDictionary<string, Tuple<object, IType>> parameters)
{
    foreach (var parameterName in query.NamedParameters)
    {
        var param = parameters[parameterName];
        if (param.Item1 == null)
        {
            if (typeof(IEnumerable).IsAssignableFrom(param.Item2.ReturnedClass) &&
                param.Item2.ReturnedClass != typeof(string))
                query.SetParameterList(parameterName, null, param.Item2);
            else query.SetParameter(parameterName, null, param.Item2);
        }
        else
        {
            if (param.Item1 is IEnumerable && !(param.Item1 is string))
                query.SetParameterList(parameterName, (IEnumerable)param.Item1);
            else if (param.Item2 != null)
                query.SetParameter(parameterName, param.Item1, param.Item2);
            else query.SetParameter(parameterName, param.Item1);
        }
    }
}

You'll need to wrap it in a using statement to ensure the reader is closed:

using (var results = session.Query<Fark>().Take(50).Where(x => x.Enabled).Stream())
{
    results.ForEach(x => writer.WriteLine(x.ToCsv()));
}

Ayende says this:

We still have the problem of memory consumption, though. The session will keep track of all the loaded objects, and if we load a lot of data, it will eventually blow out with an out of memory exception.

This is likely to be the cause. Did you ensure that the session does not track the objects? Try selecting not entities but some member of them to test this.