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.

I seriously doubt I can do that. This is a large project, probably about 50 different mapping files. I've tried isolating just the table and the map that has the problem for my own debugging, and was not able to reproduce this. The exception provides VERY little helpful information, so I'm just asking if anyone knows how I can inspect the SqlParameters when debugging. — Steve Wash, Jun 21 '19 at 14:48
I'm not asking for a solution to a specific example of this issue. I'm asking how to access the SqlParameter collection that is referenced in the exception message so I can better determine which property is not mapped correctly. I don't know why someone downvoted me for that. — Steve Wash, Jun 24 '19 at 17:32
`The table being updated is pretty simple so the map for that table is also simple` Show it then. — Nkosi, Jun 24 '19 at 19:30
Have you seen this answer https://stackoverflow.com/a/10306114/5233410 — Nkosi, Jun 24 '19 at 19:30
So it looks like a mapping issue, but there is not much help that can be provided since the classes involved are not known based on limited example provided. — Nkosi, Jun 24 '19 at 19:32

score 3 · Accepted Answer · answered Jun 28 '19 at 15:31

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.

Unfortuntaely for me, I can run the queries that NHibernate creates just fine. The actual error doesn't happen until the Session.Commit. I've also found that using .Load to get the object does not work but using .Get does. Load will use a cached proxy. But I agree with your advice and will use them when I get back to this. The work around does not sit well with me. Didn't actually solve it, but best answer so giving you the credit. — Steve Wash, Jul 03 '19 at 14:28
@SteveWash, in case you use projection to update make sure : "the constructed entity must have the exact same type than the underlying queryable source type. Attempting to project to any other class (anonymous projections excepted) will fail". That was taken from https://nhibernate.info/doc/nhibernate-reference/querylinq.html — MHOOS, Jul 03 '19 at 15:22

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

1 Answers1