CODEWITHSUNDEEP
c#sqllinqnhibernate
Jay Pete
Jay Pete

Reputation: 4259

NHibernate Linq Generics unbounded resultset

Can anyone tell me why these two code snippets give me two different SQL executions:

First

return nHibernateSession.Query<TEntity>()
  .Where(_filter)
  .Select(_selector)
  .FirstOrDefault();

Where I pass in the arguments

Func<Product, bool> _filter = x => x.Id == 10;
Func<Product, string> _selector = x => x.Name;

When inspecting the query using NHibernate Profiler, this shows that I fully hydrate the entire collection of products in an unbounded query. It selects all fields and all rows, and then I guess it filters the resultset and only returns the name.

Second

And one where I have explicitly specified my query. I tried this to debug the previous generic expression.

return nHibernateSession.Query<Product>()
  .Where(x => x.Id == 10)
  .Select(x => x.Name)
  .FirstOrDefault();

This one behaves as I would expect the previous one to behave. It only selects the name column, and applies a WHERE clause that ensures I only get product 10.

Upvotes: 1

Views: 121

Answers (1)

Taher Rahgooy
Taher Rahgooy

Reputation: 6696

NHibernate needs the expression tree of the filter in order to generate SQL from it. When you pass a Func to it, NHibernate cannot translate it to SQL, so it retrieves all data from DB and then applies the Func filter to it. change the first one to

Expression<Func<Product, bool>> _filter = x => x.Id == 10;
Expression<Func<Product, string>> _selector = x => x.Name;

In this case you give the whole expression to NHibernate, therefore a restricted query can be generated based on it.

Upvotes: 1

Related Questions