title: NHibernate ToList() and then query over that causes multiple queries
body: <p>I have a list of items ( that does not change ) so querying that with NHibernate and converting to in memory list like below</p>
  
  <p><code>List&lt;Items&gt; myItems = _repository.All().ToList();</code></p>
  
  <p>later when user does a search and clicks search button i do below</p>
  
  <p><code>myItems.Where(item =&gt; item.Name == searchTerm)</code></p>
  
  <p>I see that first code causes below query (equivalent not exact)</p>
  
  <p><code>SELECT * FROM Items</code></p>
  
  <p>then second code causes another hit to database, even though it is in memory object.</p>
  
  <p><code>SELECT * FROM Items Where Name = 'Stackoverflow'</code></p>
  
  <p><strong>What do i do to stop that unnecessary query ?</strong></p>
  
  <p>Each item can belong to multiple users, so Item has a mapping like below</p>
  
  <p><code>HasMany(x =&gt; x.Users).KeyColumn("UserId");</code></p>
  
  <p>In a page that displays number of users that have Items i have code like below</p>
  
  <pre><code>foreach(Item item in AllItems)
  {
  
     var itemName = item.Name;
     var itemUserCount = item.Users.Count;
  
  }
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>How do i optimize the above mapping to work efficiently?
  </code></pre>
  
  <p>Resources:</p>
  
  <p><a href="http://www.hibernatingrhinos.com/products/nhprof/learn/alert/SelectNPlusOne" rel="nofollow">http://www.hibernatingrhinos.com/products/nhprof/learn/alert/SelectNPlusOne</a></p>