.NET Dapper nullable cast error

.net dapper nullable orm

Question

Using .NET Dapper, I am having an issue getting a database field that contains an integer value (0/1) to map to a nullable boolean property in a class.

To keep things simple, I have stripped down and renamed the class to the bare minimum needed to reproduce the problem:

public class Test
{
    public bool? TestField { get; set; }
}

If the following code is called to populate the Test class:

var Results = DBConnection.Query<Test>("SELECT 0 As TestField]").ToList();

the following error will be thrown:

Invalid cast from 'System.Int32' to 'System.Nullable`1[[System.Boolean, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]

If I remove the question mark, making the field a non-nullable boolean (i.e. public bool TestField), everything works fine.

The immediate answer might appear to remove the nullable and call it a day. However, the reason that won't work is because we are using this same class to serialize records to and from a web service, and we need to be able to tell the difference between false and null. I thought of having two classes, one with nullable property types, and one without, but then I have the added overhead of maintaining two classes instead of one.

A custom data transformation during a property set would be ideal. Though, I haven't found anything in the dapper documentation to suggest that this is even possible.

Accepted Answer

Looks like there may be an issue in the Dapper code regarding nullable bool/long, etc.

Here are three lines from the source code (lines 2375-2377 version 1.12.1.1). The issue is on the first line:

il.Emit(OpCodes.Ldtoken, unboxType); // stack is now [target][target][value][member-type-token]
il.EmitCall(OpCodes.Call, typeof(Type).GetMethod("GetTypeFromHandle"), null); // stack is now [target][target][value][member-type]
il.EmitCall(OpCodes.Call, typeof(Convert).GetMethod("ChangeType", new Type[] { typeof(object), typeof(Type) }), null); // stack is now [target][target][boxed-member-type-value]

When this code is emitted, it becomes equivalent to the following line of code:

Convert.ChangeType(0, typeof(bool?));

Unfortunately, this throws the error that I am seeing. By changing the first il.Emit( ) line above to the following:

il.Emit(OpCodes.Ldtoken, nullUnderlyingType ?? memberType); // stack is now [target][target][value][member-type-token]

The equivalent line of code generated becomes this, noting the typeof(bool?) at the end no longer has the nullable question mark after it:

Convert.ChangeType(0, typeof(bool));

This line of code does not throw an error.

So the solution was a recompile of the source. I will submit this change back to the project for them to review to see if this will cause any unwanted side effects.


Popular Answer

Bit should map nicely into bool:

create table Test
(
    TestField bit null
)

Another option would be to do something like:

var Results = DBConnection.Query<Test>("SELECT cast(case TestField when 1 then 1 when 0 then 0 else null end as bit)  As TestField from Test").ToList();


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why