In part of our app, you can drill down 3 levels, to the detail of one of our aggregate roots. Well, the last level wasn't working. I don't believe I modified any mappings that should affect this object, so I was stumped. The nHProf output was odd. It said
Failed to execute multi criteria[SQL: SELECT count(*) as y0_ FROM ...
WHERE ((this_.MyObjectId= ? and this_.OtherObjectId = ?) and this_.AnotherObjectId = ?);
SELECT TOP (?) this_.FieldName, ...
So I ask myself... Where did these question marks come from? I can't find a thing online that explains it.
In the error message thrown by Visual Studio I get...
{"Input string was not in a
correct format."}
And then this cryptic message block
at
System.Number.ParseDouble(String value, NumberStyles options, NumberFormatInfo
numfmt)
at
System.String.System.IConvertible.ToDouble(IFormatProvider provider)
at
System.Convert.ToDouble(Object value)
at
NHibernate.Type.DoubleType.Get(IDataReader rs, Int32 index)
at
NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
at
NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names,
ISessionImplementor session, Object owner)
at
NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names,
ISessionImplementor session, Object owner)
at
NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs,
Object id, Object obj, ILoadable rootLoadable, String[][]
suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session)
at
NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj,
String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode,
ILoadable rootPersister, ISessionImplementor session)
at
NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i,
ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias,
EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects,
ISessionImplementor session)
at
NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters,
EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey,
LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session)
at
NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet,
ISessionImplementor session, QueryParameters queryParameters, LockMode[]
lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[]
keys, Boolean returnProxies)
at
NHibernate.Impl.MultiCriteriaImpl.GetResultsFromDatabase(IList results)
Um... wha?!? So, somewhere NHibernate is trying to convert something to a double and is failing. Okay, why is it doing that and why is that causing NHibernate to add question marks to the query?
I check out the mappings. Notice that some of the double fields on this aggregate are not nullable when the fields in the view they are populated by is set up to be nullable. Strange. I remember that normally it throws an error that kind of points you to this fact, but I fix it and proceed.
Still no dice.
I check out the query its generating, I check the mapping again, I check everything I can think of... Hours later, I'm going over it again and I start looking at the data. I check the result set for nulls where it should be a non null. Nothing. Then I start comparing the data from the view to the column types.
I find something interesting. We have a double field called Margin. The value returned for this field from SELECT * FROM MyFeature.MyView is '2012-07-01'. My friends, that is NOT a double! I'd missed it my first couple passes because I'd always believed that the types given to a column are inviolate. SQL Server wouldn't lie to me!
Well... Someone updated one of the tables that this view relies on. Apparently they changed the order of some columns. And since the underlying view structure is based (from what I understand) on column index, SQL Server said, "Hey! This is now a Date field!", and started returning values for a different column.
WTH!?!
I'd never even HEARD of such a thing! Called over the DBA and asked him what he thought about a date appearing in a double column and he explained to me what happened. Even gave me the proc he uses to regen all views in a given database. Regenerated the view and voila! Bug is gone :)
If I were a better person, I'd dig into NHibernate's source and offer a patch that gives a better error message for this issue.
Instead I'm writing this blog post, that outlines my frustration, but has all of the keywords I searched for in my initial journey to find the solution to this problem. Hopefully another developer having this issue will stumble upon my blog and waste less time than I did.
Moral of the story...
Don't trust SQL to always return the type you expect
And
If you can't find the solution to your issue on Google, maybe you really have a different problem.
