NHibernate Hanging on Lazy Loaded Query

Question

First of all, I'm using Fluent NHibernate with LinqToNHibernate.

I've got a query to do a search on a table based on what data the user entered. So, for example, I'm doing something like this:

        'build the initial query that we will filter--this is lazy loaded
    Dim results As IEnumerable(Of Customers) = Me.GetCustomers()

    'filter by owner name
    If String.IsNullOrEmpty(OwnerName) = False Then
        results = results.Where(Function(s) s.Name.Contains(OwnerName))            
    End If

    'execute query
    results = results.ToList()

So essentially I'm building a where statement on the sql statement if the user wants to search by name. I'm using all lazy configurations in my mappings, so the query shouldn't be retrieving the items until the "ToList()" is called. The problem is that NHibernate hangs on this statement. I thought at first it was because there were so many records in the db(about 500,000).

But I changed the line where I am filtering the results to this:

results = SessionFactoryProvider.SessionFactory.CurrentSession.Linq(Of Customer).Where(Function(c) c.Name.Contains(OwnerName))

And it works very quickly. But I can't seem to figure out why. Any ideas?

Answer 1

In the first case you are retrieving all the records and using Linq-to-objects to filter that list.

In the second case, you are sending the query to the database (NHibernate.Linq converts your expression into a SQL WHERE clause).

Now, I don't know what is the return type of GetCustomers, but since you're storing it into an IEnumerable(Of Customer), .NET has no way of knowing about an underlying provider. If GetCustomers' code is something like:

Return CurrentSession.Linq(Of Customer)

...Then the problem is with that conversion. Just change the first line to:

Dim results As IQueryable(Of Customers) = Me.GetCustomers()