How to use generic and Nullable types in Dapper for materialization?

Question

I have this call:

public IObservable<T> Subscribe(object topic, string service, string connectionString, string query)
{
    try
    {
        this.connection.ConnectionString = connectionString;
        this.connection.Open();
        this.connection.Query<T>(query, new { transactionid = topic }).ToObservable().Subscribe(message => this.subject.OnNext(message));
        return this.subject;
    }
    catch (Exception e)
    {
        this.subject.OnError(e);
        return this.subject;
    }
    finally
    {
        this.subject.OnCompleted();
        this.connection.Close();
    }
}

This is my query:

with IDS as  (select L1_ID, L2_ID, L1_VALUE, L2_VALUE 
from MYDB where MYDB.ID = :transactionid) 
select * from 
(select L1_ID as ID, round(L1_VALUE, 28) as VALUE from IDS
union 
select L2_ID as ID, round(L2_VALUE, 28) as VALUE from IDS) UN

This throw this error:

A parameterless default constructor or one matching signature (System.String ID, System.Decimal VALUE) is required for System.Tuple2[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Nullable1[[System.Decimal, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] materialization

Answer 1

The problem here isn't the Nullable<T>, it is the Tuple<,>.

Dapper assumes one of two patterns. Let's assume our data has columns

ID      varchar
VALUE   decimal

(since that seems to be the case in your scenario)

To load that into a T, it wants to do either (after it has determined that 0 is ID and 1 is VALUE, etc):

T row = new T() { ID = reader.GetInt32(0), VALUE = reader.GetDecimal(1) };

or

T row = new T(ID: reader.GetInt32(0), VALUE: reader.GetDecimal(1));

note that I'm simplifying a lot of stuff here, and it is fairly forgiving about case sensitivity, but that's basically what it wants.

Now, the problem is: Tuple<T1,T2> doesn't have either of those. It has the constructor:

public Tuple(T1 item1, T2 item2);

which won't work here - dapper can't be absolutely sure what is meant to go where, so it doesn't try. This sounds harsh, but dapper tries not to mind about column order, and in the general case (where the columns aren't all different types), it is not clear what the correct approach should be when there is no match.

Options:

1. create a custom type of the form:

   public class SomeType { // could also be a struct, immutable, whatever
       public int Id {get;set;}
       public decimal Value {get;set;}
   }
   

   and use T === SomeType

2. use the non-generic API and re-map:

   Query(query, new { transactionid = topic }).Select(
       row => Tuple.Create((int)row.ID, (decimal)row.VALUE
   ).Whatever(...);
   

3. name your result columns item1 and item2 (yeuch!)