NHibernate & LINQ: Using Fetch() with custom ToPagedList method

Question

I have a problem with using the nHibernate Fetch() (or FetchMany()) method with my paging method which uses Futures to get the information it needs. And I'm not sure how to fix it but I know exactly what it's doing. Let me explain what I have so far.

I'm using an extension method found here to let me get a row count as a Future value. it looks like this.

public static IFutureValue<TResult> ToFutureValue<TSource, TResult>(this IQueryable<TSource> source, Expression<Func<IQueryable<TSource>, TResult>> selector) where TResult : struct
        {
            var provider = (NhQueryProvider)source.Provider;
            var method = ((MethodCallExpression)selector.Body).Method;
            var expression = Expression.Call(null, method, source.Expression);
            return (IFutureValue<TResult>)provider.ExecuteFuture(expression);
        }

It works great, using this I can then combine getting a row count and getting my result set into a signle paging method that only generates a single database hit. My Paging method looks like this.

public static IList<T> ToPagedList<T>(this IQueryable<T> query, int pageIndex, int pageSize, out int count)
        {
            var futureCount = query.ToFutureValue(x => x.Count());
            var futureResults = pageIndex == 0 && pageSize == 0 ? query.ToFuture() : query.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToFuture();
            count = futureCount.Value;
            return futureResults.ToList();
        }

With this in place I can make queries like the following.

repositoryInstance.Query().Where(x=>x.SomeTextField.Contains("lake")).ToPagedList(pageIndex, pageSize, out count);

This much works like a charm. However now I need to be able to define certain relationships as eager loaded, in this case it might look like this.

repositoryInstance.Query().Where(x=>x.SomeTextField.Contains("lake")).FetchMany(x=>x.Details).ToPagedList(pageIndex, pageSize, out count);

This blows up with a QueryException with the description "Query specified join fetching, but the owner of the fetched association was not present in the select list". The reason for this is because of my paging method, it's trying to apply an aggregate row count on the query which has the Fetch() on it, and you can't do that, because the error is right. The owner of the fetched association wasn't in the select list (at least it isn't in one of them), my select is essentially for the first query is for a single integer. I thought maybe I could rip out the Expression nodes once in the ToPagedList that are for any fetching but I have no idea if it's possible or how ugly it would be. I'm looking for some alternatives that might let be get the behavior I want while bypassing the limitation on how Fetching works. Any thoughts?

score 1 · Accepted Answer · answered Jun 14 '11 at 02:39

1

You could modify ToPagedList as follows:

public static IList<T> ToPagedList<T>(this IQueryable<T> query, int pageIndex,
                       int pageSize, out int count,
                       Func<IQueryable<T>, IQueryable<T>> filterResult)
{
    var futureCount = query.ToFutureValue(x => x.Count());
    //change only the results query, not the count one
    if (customAction != null)
        query = filterResult(query);
    var futureResults = pageIndex == 0 && pageSize == 0
                            ? query.ToFuture()
                            : query.Skip((pageIndex - 1) * pageSize)
                                   .Take(pageSize).ToFuture();
    count = futureCount.Value;
    return futureResults.ToList();
}

And use it like this:

repositoryInstance.Query().Where(x => x.SomeTextField.Contains("lake"))
                  .ToPagedList(pageIndex, pageSize, out count,
                               q => q.FetchMany(x => x.Details));

I know it's slightly less elegant, but it will work, and it's way easier than modifying an existing expression.

answered Jun 14 '11 at 02:39

Diego Mijelshon

52,548
16
116
154

Yeah I had thought about that option too. You're right it's a little less elegant, but it should work. I was just hoping someone might have run into a similar problem and might have a better solution. I'll modify my ToPagedList extension method for now and I can always try alternatives later. Part of the reason I have to do this is because for some reason when I use LINQ to query my database the Join.Fetch() behavior defined on my fluent nHibernate mapping isn't working. Works fine for ICriteria though . doing it on the IQueryable gives me more flexibility, but it's odd. – Nick Albrecht Jun 14 '11 at 15:27
@Yarx: join fetch doesn't work with Linq and HQL by design. Anyway, you might also have problems using paging together with FetchMany. My suggestion is to avoid it and use `batch-size` instead. – Diego Mijelshon Jun 14 '11 at 16:15
I'm not familiar with batch-size, I'll have to take a look around and see what I can find for information about it and how it applies to fetch like behavior. I am familiar with the problems with paging and fetch though, what I had done in the past was use a nested query to get the top/next X row ids that matched my criteria, then use those ids to get all objects with the Ids including the fetched child rows, so I get just 10 unique root results, even though it might be 170 rows. I believe I had to use `criteria1.SetResultTransformer(Transformers.DistinctRootEntity);` as well – Nick Albrecht Jun 14 '11 at 16:58
@Yarx: Read [18.1.5. Using batch fetching](http://www.nhforge.org/doc/nh/en/index.html#performance-fetching-batch) – Diego Mijelshon Jun 14 '11 at 20:01

NHibernate & LINQ: Using Fetch() with custom ToPagedList method

1 Answers1