CODEWITHSUNDEEP
c#sqlnhibernate
Deeptechtons
Deeptechtons

Reputation: 11125

NHibernate ToList() and then query over that causes multiple queries

I have a list of items ( that does not change ) so querying that with NHibernate and converting to in memory list like below

List<Items> myItems = _repository.All().ToList();

later when user does a search and clicks search button i do below

myItems.Where(item => item.Name == searchTerm)

I see that first code causes below query (equivalent not exact)

SELECT * FROM Items

then second code causes another hit to database, even though it is in memory object.

SELECT * FROM Items Where Name = 'Stackoverflow'

What do i do to stop that unnecessary query ?

Each item can belong to multiple users, so Item has a mapping like below

HasMany(x => x.Users).KeyColumn("UserId");

In a page that displays number of users that have Items i have code like below

foreach(Item item in AllItems)
{

   var itemName = item.Name;
   var itemUserCount = item.Users.Count;

}

Above is causing N queries on User table i.e If Items count = 100, 100 queries are executed on Users table to get the count.

How do i optimize the above mapping to work efficiently?

Resources:

http://www.hibernatingrhinos.com/products/nhprof/learn/alert/SelectNPlusOne

Upvotes: 1

Views: 3865

Answers (2)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123871

What I see as an issue, is the User collection population, done for each item separately. The efficient way how to solve N+1 issue, is to use batch loading. Pretty clear explanation could be found in documentation 19.1.5. Using batch fetching

The Prevent Fluent NHibernate select n+1 question/answer is a bit old, so the syntax used there should be in your case:

HasMany(x => x.Users)
    .KeyColumn("UserId")
    .BatchSize(20) // here we go
    ;

This will reduce the amount of queries significantly. Because lot of users would be loaded at once.

Upvotes: 1

Patryk Ćwiek
Patryk Ćwiek

Reputation: 14328

You'd be better off pulling only the data you need (using LINQ provider):

var result = session.Query<Item>().Where(x => x.Name == searchTerm).Select(x => new { x.Name, UserCount = x.Users.Count() });

(If you need to use that data outside of the function that was pulling it, you will have to select to a dedicated class, or at least a Tuple)

If you really want to pull all the data (using QueryOver API):

var result = session.QueryOver<Item>().Fetch(x => x.User).Eager.List();

That will probably create a LEFT JOIN, but you'd need to eagerly fetch all other entities you might need.

There's an easy way to see if you have a N+1 problem: dispose the session just after pulling the data. If you access a non-eagerly fetched related entity anywhere, you'll get an exception, which makes debugging such situations that much easier:

IList<Item> list;
using(var session = sessionFactory.OpenSession())
{
    list = session.QueryOver<Item>().Fetch(x => x.User).Eager.List();
}
var otherStuff = list.Select(x => x.OtherItem.Price).ToList(); // Boom, exception! OtherItem was not eagerly fetched.

Upvotes: 0

Related Questions