SQLite NotSupportedException object is a multicolumn type

[fairking]

Nh 5.3.3 (also 5.3.2) (not sure about older versions) .NET Core 3.1

Config:

var dbId = NHibernate.SimpleMapping.Generators.IdentityGenerator.WebHash();
_connectionString = $"FullUri=file:memorydb_{dbId}.db?mode=memory&cache=shared";
var cfg = new Configuration()
                .SetProperty(Environment.ReleaseConnections, "on_close")
                .SetProperty(Environment.DefaultFlushMode, FlushMode.Commit.ToString())
                .SetProperty(Environment.Isolation, System.Data.IsolationLevel.ReadCommitted.ToString())
                .SetProperty(Environment.Hbm2ddlAuto, SchemaAutoAction.Create.ToString())
                .SetProperty(Environment.Dialect, typeof(SQLiteDialect).AssemblyQualifiedName)
                .SetProperty(Environment.ConnectionDriver, typeof(SQLite20Driver).AssemblyQualifiedName)
                .SetProperty(Environment.ShowSql, "true")
                .SetProperty(Environment.FormatSql, "true")
                .SetNamingStrategy(ImprovedNamingStrategy.Instance);

Having the following sqlite query:

var result = _session
    .CreateSQLQuery("select column1, column2, avg(column3) as column3_avg from my_table group by column1, column2")
    .SetResultTransformer(new AliasToBeanResultTransformer(typeof(MyViewModel)))
    .ListAsync<MyViewModel>();

Where the column3 is nullable integer and the column3_avg property is int?.

Gives the following error:

---- System.NotSupportedException : object is a multicolumn type
Stack Trace:
Loader.DoListAsync(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder, CancellationToken cancellationToken) line 1471
Loader.ListIgnoreQueryCacheAsync(ISessionImplementor session, QueryParameters queryParameters, CancellationToken cancellationToken) line 1345
SessionImpl.ListCustomQueryAsync(ICustomQuery customQuery, QueryParameters queryParameters, IList results, CancellationToken cancellationToken) line 1212
SessionImpl.ListCustomQueryAsync(ICustomQuery customQuery, QueryParameters queryParameters, IList results, CancellationToken cancellationToken) line 1218
AbstractSessionImpl.ListAsync(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results, CancellationToken cancellationToken) line 139
AbstractSessionImpl.ListAsync[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters, CancellationToken cancellationToken) line 149
SqlQueryImpl.ListAsync[T](CancellationToken cancellationToken) line 77
BaseService.GetListAsync[TViewModel](Query input, BeanAlias`1 beanAlias, Action`1 aliasToBeanAction, Int32 top) line 415
SurveyService.GetSingleSurveyReport(SingleSurveyReportQueryVm query) line 747
SurveyTests.BasicTest() line 148
--- End of stack trace from previous location where exception was thrown ---
----- Inner Stack Trace -----
AnyType.NullSafeGetAsync(DbDataReader rs, String name, ISessionImplementor session, Object owner, CancellationToken cancellationToken) line 32
ScalarResultColumnProcessor.ExtractAsync(Object[] data, DbDataReader resultSet, ISessionImplementor session, CancellationToken cancellationToken) line 155
ResultRowProcessor.ExtractResultRowAsync(Object[] data, DbDataReader resultSet, ISessionImplementor session, CancellationToken cancellationToken) line 115
ResultRowProcessor.BuildResultRowAsync(Object[] data, DbDataReader resultSet, Boolean hasTransformer, ISessionImplementor session, CancellationToken cancellationToken) line 90
Loader.GetRowFromResultSetAsync(DbDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder, Action`2 cacheBatchingHandler, CancellationToken cancellationToken) line 197
Loader.DoQueryAsync(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder, CancellationToken cancellationToken) line 302
Loader.DoQueryAndInitializeNonLazyCollectionsAsync(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder, CancellationToken cancellationToken) line 80
Loader.DoListAsync(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder, CancellationToken cancellationToken) line 1461

Any idea why is it happening guys? Is there something not implemented yet?

Thanks

[fairking]

If I remove that avg column from the select, the query is passed.

[bahusoid]

Seems some incorrect type is detected for your avg field. Just register it manually via AddScalar:

.AddScalar("column3_avg", NHibernateUtil.Int32 /* or whatever type is needed */); 

[maca88]

I am unable to reproduce this exception. The following test:

public class MyViewModel
{
    public string CustomerId { get; set; }

    public string ShipName { get; set; }

    public int? Average { get; set; }
}

[Test]
public void Test()
{
var result = session
    .CreateSQLQuery("select CustomerId, ShipName, avg(EmployeeId) as Average from Orders group by CustomerId, ShipName")
    //.AddScalar("CustomerId", NHibernateUtil.String)
    //.AddScalar("ShipName", NHibernateUtil.String)
    //.AddScalar("Average", NHibernateUtil.Int32)
    .SetResultTransformer(new AliasToBeanResultTransformer(typeof(MyViewModel)))
    .List<MyViewModel>();
}

throws:

System.ArgumentException : Object of type 'System.Double' cannot be converted to type 'System.Nullable`1[System.Int32]'.

for Average property which is expected as avg function produce an integral value. For some reason GetHibernateType returns AnyType in your case when auto discovering column types. Did you register any custom types using TypeFactory.RegisterType method?

[fairking]

Did you register any custom types using TypeFactory.RegisterType method?

I don't do any AddScalar calls or any other methods to register types. Also I don't have any custom types (User Types).

If I do:

public decimal? Average { get; set; }

I got the same error.

However if I register a type AddScalar("Average", NHibernateUtil.Int32) the error disappears. I am going to use it as a workaround. :-)

Thanks guys

[fairking]

Just want to mention one more thing. It only happens with SQLite. MSSQL same query is working fine. As I use MSSQL db in production and SQLite in memory in query tests.