NHibernate - Could not execute query - Input string was not in a correct format

Question

I've been scratching my head with this one for a while now and I'm clueless as to what's wrong.

Overview: I have two tables in a MySQL database. Both map correctly to the database (I can load data in) and I am able to query to one table, but not the other.

Solutions I've looked into: Type conversion issues between the table and the C# code, mapping issues, SQL format issues.

The code that fails is as follows:

Configuration config = new Configuration();
config.Configure();
ISessionFactory sessionFactory = config.BuildSessionFactory();
var schema = new SchemaUpdate(config);
schema.Execute(true, true);
results = session.CreateSQLQuery("SELECT * FROM Stats_Table") // Exception thrown here
    .AddEntity(typeof(TestStats))
    .List<TestStats>();

---

The class:

public class Stats
{
    public virtual Guid      Id        { get; set; }
    public virtual Guid      TestId    { get; set; }
    public virtual String    Name      { get; set; }
    public virtual TResult   Result    { get; set; }
    public virtual DateTime? Timestamp { get; set; }
    public virtual UInt32    Duration  { get; set; }
    public virtual String    Notes     { get; set; }

    public Stats() 
    {
    }

    public Stats(TestCase tc, TResult Res, DateTime? Time, UInt32 Dura, String ResultNote="")
    {
        Id = Guid.NewGuid();
        TestId = tc.Id;
        Name = tc.TestName;
        Result = Res;
        Timestamp = Time;
        Duration = Dura;
        Notes = ResultNote;
    }

    public Stats(Guid T_Id, string Name, TResult Res, DateTime? Time, UInt32 Dura, String ResultNote="")
    {
        Id = Guid.NewGuid();
        TestId = T_Id;
        Name = Name;
        Result = Res;
        Timestamp = Time;
        Duration = Dura;
        Notes = ResultNote;
    }
}

---

Mapping file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="Test_Database"
                   namespace="Test_Database.TestClasses">
  <class name="Test_Database.TestClasses.Stats"
         table="Stats_Table"
         lazy="true">

    <id name="Id" column="ID" type="Guid">
      <generator class="assigned" />
    </id>

    <property name="TestId"    column="TestID" />
    <property name="Name"      column="Name" />
    <property name="Result"    column="Result" />
    <property name="Timestamp" column="Timestamp" />
    <property name="Duration"  column="Duration" />
    <property name="Notes"     column="Notes" />

  </class>
</hibernate-mapping>

---

I get an NHibernate GenericADOException: "could not execute query\r\n[ SELECT * FROM Stats_Table ]\r\n[SQL: SELECT * FROM Stats_Table]"

With an InnerException: "Input string was not in a correct format."

---

StackTrace:

at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.String.System.IConvertible.ToInt32(IFormatProvider provider) at System.Convert.ToInt32(Object value) at NHibernate.Type.PersistentEnumType.SystemInt32EnumConverter.Convert(Object input) in p:\nhibernate-core\src\NHibernate\Type\PersistentEnumType.cs:line 88 at NHibernate.Type.PersistentEnumType.AbstractEnumConverter`1.ToObject(Type enumClass, Object code) in p:\nhibernate-core\src\NHibernate\Type\PersistentEnumType.cs:line 33 at NHibernate.Type.PersistentEnumType.GetInstance(Object code) in p:\nhibernate-core\src\NHibernate\Type\PersistentEnumType.cs:line 203 at NHibernate.Type.PersistentEnumType.Get(IDataReader rs, Int32 index) in p:\nhibernate-core\src\NHibernate\Type\PersistentEnumType.cs:line 189 at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) in p:\nhibernate-core\src\NHibernate\Type\NullableType.cs:line 253 at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in p:\nhibernate-core\src\NHibernate\Type\NullableType.cs:line 195 at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in p:\nhibernate-core\src\NHibernate\Type\AbstractType.cs:line 131 at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String[][] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session) in p:\nhibernate-core\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:line 2518 at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session) in p:\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 989 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) in p:\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 944 at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session) in p:\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 876 at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) in p:\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 342 at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in p:\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 473 at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in p:\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 251 at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in p:\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1564

---

And from NHibernate:

at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in p:\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1573 at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in p:\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1472 at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in p:\nhibernate-core\src\NHibernate\Loader\Loader.cs:line 1467 at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters) in p:\nhibernate-core\src\NHibernate\Loader\Custom\CustomLoader.cs:line 276 at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) in p:\nhibernate-core\src\NHibernate\Impl\SessionImpl.cs:line 2108 at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) in p:\nhibernate-core\src\NHibernate\Impl\SessionImpl.cs:line 2091 at NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters) in p:\nhibernate-core\src\NHibernate\Impl\SessionImpl.cs:line 2077 at NHibernate.Impl.SqlQueryImpl.ListT in p:\nhibernate-core\src\NHibernate\Impl\SqlQueryImpl.cs:line 163 at TestManager_Database.Program.Main(String[] args) in C:\Users\cryan\Documents\Visual Studio 2010\Projects\TestManager_Database\TestManager_Database\Program.cs:line 287 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

I've tried going through the source code for NHibernate but I didn't figure anything out from it.

Any help with this would be greatly appreciated.

Answer 1

Your enum - TResult is probably being saved as a string value and trying to be parsed as an int value (guessing from the error message)

Try changing your config for the result property to:

<property name="Result" column="Result" type="NHibernate.Type.EnumStringType`1[[MyNamespace.TResult, MyAssembly]], NHibernate"/>

where MyNamespace.TResult, MyAssembly is the fully qualified name of your enum TResult

Alternatively, change the Result column to be an int :)