title: OutOfMemory Exception while fetching large sized data using QueryOver<> in Linq to Nhibernate
body: <p>I have a asp.net web application that is using Linq to NHibernate in NHibernate 3.0.</p>
  
  <p>In a function, I need to get around 20000 records from a table which contains 10 million records with 20 columns.</p>
  
  <p>I am using Session.QueryOver&lt;>() method for fetching records.</p>
  
  <p>The functional code is:</p>
  
  <pre><code>public IList&lt;BatchDetails&gt; GetBatchRecordsOnBatchId_BatchSize(int batchId,int stratingRowdId,int batchSize)
      {
          // If the stratingRowdId will be 0 than frist count of the Batch Records equivlent to the batchSize will be return
  
          //If the batchSize will be 0 than all the Batch Records starting from the RowId equivlent to the stratingRowdId will be return
  
          // If both the stratingRowdId &amp; batchSize are 0 than all the BatchReocrds for the BatchId will be return
  
  
  
          if (batchId &lt;= 0)
          {
  
              throw new ArgumentException();
          }
          using (var session = _sessionFactory.OpenSession())
          {
              using (var transaction = session.BeginTransaction())
              {
                  try
                  {
                      //Get Batch data from the Database for the BatchId
                      var batchData = from batchRecords in session.QueryOver&lt;BatchDetails&gt;()
                                          .Where(x =&gt; x.BatchId == batchId)
                                          .List().Skip(stratingRowdId).Take(batchSize)
                                      select batchRecords
                          ;
  
                      transaction.Commit();
                      return batchData.ToList();
                  }
                  catch (ArgumentException ex)
                  {
                      if (transaction != null) transaction.Rollback();
  
  
                      throw;
                  }
                  catch (Exception exception)
                  {
                      if (transaction != null) transaction.Rollback();
  
                      throw;
                  }
                  finally
                  {
                      session.Flush();
                  }
              }
  
          }
      }
  </code></pre>
  
  <p>This code is working till the table have 2 lac records. </p>
  
  <p>But after adding 10 lac records in the table, this method throws an error as:</p>
  
  <blockquote>
    <p>NHibernate.Util.ADOExceptionReporter| Exception of type 'System.OutOfMemoryException' was thrown.</p>
    
    <p>NHibernate.Util.ADOExceptionReporter| Exception of type 'System.OutOfMemoryException' was thrown.</p>
    
    <p>NHibernate.Util.ADOExceptionReporter| Exception of type 'System.OutOfMemoryException' was thrown.</p>
    
    <p>[DAL.GetBatchRecordsOnBatchId]:Unheld error was occured in the application,Exception : could not execute query
    [ SELECT this_.ReferenceId as Referenc1_2_0_, this_.AccountNumber as AccountN2_2_0_, this_.AccountStatus as AccountS3_2_0_, this_.AccountType as AccountT4_2_0_, this_.AccountSubType as AccountS5_2_0_, this_.AccountDescription as AccountD6_2_0_, this_.ActivationDate as Activati7_2_0_, this_.CombinedBilling as Combined8_2_0_, this_.PlanAmount as PlanAmount2_0_, this_.PlanCode as PlanCode2_0_, this_.CustomerName as Custome11_2_0_, this_.CustomerEmail as Custome12_2_0_, this_.CustomerPhone as Custome13_2_0_, this_.CustomerAddress as Custome14_2_0_, this_.CustomerCity as Custome15_2_0_, this_.CustomerState as Custome16_2_0_, this_.CustomerZipCode as Custome17_2_0_, this_.PaymentAmount as Payment18_2_0_, this_.PaymentCurrency as Payment19_2_0_, this_.PaymentDate as Payment20_2_0_, this_.TransactionId as Transac21_2_0_, this_.BatchId as BatchId2_0_ FROM TIOTestDB.dbo.BatchDetails this_ WHERE this_.BatchId = ? ]
    Positional parameters:  #0>3
    [SQL: SELECT this_.RefereId as Referenc1_2_0_, this_.AccNumber as AccountN2_2_0_, this_.AcStatus as AccountS3_2_0_, this_.AcType as AccountT4_2_0_, this_.AccSubType as AccountS5_2_0_, this_.AccountDescription as AccountD6_2_0_, this_.ActivationDate as Activati7_2_0_, this_.CombinedBilling as Combined8_2_0_, this_.PlanAmount as PlanAmount2_0_, this_.PlanCode as PlanCode2_0_, this_.CustomerName as Custome11_2_0_, this_.Email as Custome12_2_0_, this_.Phone as Custome13_2_0_, this_.Address as Custome14_2_0_, this_.City as Custome15_2_0_, this_.State as Custome16_2_0_, this_.ZipCode as Custome17_2_0_, this_.PayAmount as Payment18_2_0_, this_.Currency as Payment19_2_0_, this_.PayDate as Payment20_2_0_, this_.TransactionId as Transac21_2_0_, this_.bhId as bhId2_0_ FROM bDetails this_ WHERE this_.bhId = ?]</p>
  </blockquote>
  
  <p>as i am executing this function in a foreach() iteration, 
  Although the query executes 1 or 2 times and retrieve data but after that it throws the Out of memory exception.</p>
  
  <p>As an experienced NHibernate developer , i can understand that i need to restructure the LINQ query to optimize its performance .</p>
  
  <p>Also i have searched over the internet but i could not get much information.</p>
  
  <p>An earliest reply would be appreciated.</p>