How do I debug "Invalid index X for this SqlParameterCollection with Count X"

Question

I'm working on a project that's written in C# and uses Fluent NHibernate. I'm getting the infamous exception "Invalid index X for this SqlParameterCollection with Count X". I've tried several suggestions from different SO solutions, but I just can't find the mapping problem.

The error never happens when reading data, only when updating. The table being updated is pretty simple so the map for that table is also simple. When the update happens, NHibernate constructs a simple one table update query, so there's not a lot actually happening when the error occurs.

My update code is wrapped in a transaction

using (var tx = Session.BeginTransaction())
{
    try
    {
         result = PerformUpdate(obj, modifiedBy);
         if (result.Succeeded)
         {
             tx.Commit();
             Session.Flush();
         }
         else 
             RollbackTransaction(tx);

         return result;
      }
      catch (Exception ex)
      {
          tx.Rollback();
          throw;
      }
   }
}

The exception gets thrown on the tx.Commit().

I'm fairly confident that this is a mapping problem somewhere in the schema, but I can't find it. How do I inspect the list of SqlParameters so I can debug this? There has to be a way to inspect the list of SqlParameters so I can see what doesn't belong.

Answer 1

As a person who had the unfortunate pleasure of working with messy legacy Nhibernate project I suggest the following to make your life much easier and survive the hostile territory:

1- "If" your database is SQL Server : Turn on SQL Server profiler and apply the required filter (Database name & command text) so you see what sql command is exactly passed on to the server. For me this would be the starting point.

2- If SQL server is not your back-end engine, your best friend at the time of debugging would be NHibernate profiler. This does exactly what you are looking for by showing the values of each parameter passed to the server plus final queries. It also shows the open sessions and whether they are closed or not. You can download the trial version and have a look.

3- For future code surgery always configure your project to turn on tracing on demand: Follow NLog with Nhibernate to record what sql statements are being passed to sql server and save them in a separate log.

Above tools and suggestions are the things I would have tried, If I would have been stuck in your situation.

As the last tip : If you did not manage to find the problems using above method, take the issue into your own bare knuckle hands and run the update using your own SQL native statement. Sometimes figuring out why NHibernate doesn't produce desired SQL statement takes more time than creating and executing the update statement manually. This is your last and final shot. It might not sound clean but most NHibernate projects are not clean anyway.