Unhelpful mapping exception from SqlMapper when using AdoMdConnection

[piers7]

If I use Dapper (v1.42) with the AdoMdConnection against an Analysis Services (tabular) data source, and my dataset has nulls in it, and I've got the wrong type on my model (I used decimal?, should have been long?) I can get the SqlMapper to chuck a very unhelpful exception:

[NullReferenceException: Object reference not set to an instance of an object.]
   Deserializedc085230-89d5-4caa-85a3-080f0e4007b3(IDataReader ) +711

[DataException: Error parsing column 10 ([Current]=<null>)]
   Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:4153
   Deserializedc085230-89d5-4caa-85a3-080f0e4007b3(IDataReader ) +1074
   Dapper.<QueryImpl>d__61`1.MoveNext() in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:1608
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +381
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58

'[Current]' in the above is the name of a column in the dataset, which is, in some cases, null, so I'd set the appropriate property in my model class to Nullable<double>. Eventually I converted to .Query() (ie dynamic) and did my own projection, and finally noticed that the column was actually Nullable<long>.

What's strange is that I can't repo this using a SQL data source - if I intentionally muck up the types there, it All Just Works(TM):

void Main()
{
    // against SQL, it all works
    this.Connection
        .Query<Model>("select 'Test' as Column1, cast(null as int) as Column2, cast(null as int) as Column3")
        .Dump()
    ;

    this.Connection
        .Query<Model>("select 'Test' as Column1, cast(null as float) as Column2, cast(null as float) as Column3")
        .Dump()
    ;
}

public class Model{
    public string Column1{get;set;}
    public double? Column2{get;set;}
    public int? Column3{get;set;}
}

You can repo against SSAS tabular like this (linqpad script):

string tabularConnectionString = "SSAS instance or URL to PowerPivot workbook in SharePoint";
string nameOfTable = "Any table in your SSAS model";

void Main()
{
    var connection = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(tabularConnectionString);
    var dax = string.Format(@"
            evaluate
                addcolumns(
                    '{0}',
                    ""Temp"", blank()
                )
        ", nameOfTable);

    // this works - mapping to DapperSqlRow is ok
    connection
        .Query(dax)
        .Dump()
    ;

    // This doesn't works - mapping to object doesn't work
    // nb: the type mapper is required due to DAX column names being wrapped in [] etc...
    Dapper.SqlMapper.SetTypeMap(typeof(Model), new Dapper.CustomPropertyTypeMap(typeof(Model), (type,colName) => 
        type.GetProperties().FirstOrDefault (p => colName.EndsWith("[" + p.Name + "]"))
    ));
    connection
        .Query<Model>(dax)
        .Dump()
    ;
}

public class Model{
    // Change Temp to string or long? to stop it failing
    public double? Temp {get;set;}
}

I'd like (if possible) the error message to be improved in this (type mismatch) scenario.

[mgravell]

I can get the SqlMapper to chuck a very unhelpful exception:

Oh, I don't know about that ... it told you the column that was playing up, the current value that it was tripping over, and the inner exception ;p If only all exceptions were so kind...

I'm going to struggle to reproduce this because I'm not even remotely familiar with SSAS or AdoMdConnection; is there any chance you can tell me what ex.InnerException.StackTrace is when this explodes?

[piers7]

That's the entirety of the inner exception stack trace up there, in the original ex.ToString():

StackTrace
   at Deserialize151a40c0-fa73-452a-89cb-41020d7f795a(IDataReader ) 

I truncated the outer exception stack trace originally as from ASP.Net MVC had heap much noise in it, but here it is from my linqpad repo:

Error parsing column 4 ([Temp]=<null>)
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 4153
   at Deserialize151a40c0-fa73-452a-89cb-41020d7f795a(IDataReader )
   at Dapper.SqlMapper.<QueryImpl>d__61`1.MoveNext() in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1608
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1479
   at UserQuery.Main()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

If I thought this was limited to just SSAS / ADOMD I wouldn't even raise it (I expect I'm in a set of 1 even using dapper for this), but seems like there's the possibility of a general issue here.

[mgravell]

but seems like there's the possibility of a general issue here

Agreed. Which is why my first step is to try to repro it :) so far, I haven't managed.

On Thu, 17 Mar 2016 02:25 Piers Williams, notifications@github.com wrote:

That's the entirety of the inner exception stack trace up there, in the original ex.ToString():

StackTrace at Deserialize151a40c0-fa73-452a-89cb-41020d7f795a(IDataReader )

I truncated the outer exception stack trace originally as from ASP.Net MVC had heap much noise in it, but here it is from my linqpad repo:

Error parsing column 4 ([Temp]=) at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 4153 at Deserialize151a40c0-fa73-452a-89cb-41020d7f795a(IDataReader ) at Dapper.SqlMapper.d__611.MoveNext() in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1608 at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) in D:\Dev\dapper-dot-net\Dapper NET40\SqlMapper.cs:line 1479 at UserQuery.Main() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

Interestingly some of the line numbers in the stack trace are different from what I put above. I've repo'd with the same nuget (1.42) so maybe a framework 4 vs 4.5 difference or something.

If I thought this was limited to just SSAS / ADOMD I wouldn't even raise it (I expect I'm in a set of 1 even using dapper for this), but seems like there's the possibility of a general issue here.

— You are receiving this because you commented. Reply to this email directly or view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/478#issuecomment-197658553

[NickCraver]

Closing out to cleanup