Dapper nullable bool Conversion error and solution

The recent use of Entityframewok to generate a database, using dapper to access the database, produced an unexpected bug, the following is the example of the bug and the solution.

Because the database is generated with EntityFramework, the default is Entityframewok convert bool to tinyint (1),

Use dapper query data times wrong (part of the data is empty, part of the data is not empty, and the first query out of the data can be empty, otherwise there will be no error).

1. Define a class:

public class Bugnullable
{
public int ID {get; set;}
public string Name {get; set;}
public bool? Isbug {get; set;}

Public DateTime? updatedtime {get; set;}
}

2. Generate Database Tables

CREATE TABLE ' bugnullable ' (
' ID ' INT (one) not NULL auto_increment,
' Name ' VARCHAR (not NULL DEFAULT ' 0 '),
' Isbug ' TINYINT (1) null DEFAULT NULL,
' Updatedtime ' TIMESTAMP null DEFAULT null,
PRIMARY KEY (' ID ')
)
Collate= ' Utf8_general_ci '
Engine=innodb
Auto_increment=3
;
3. Fill data

INSERT into ' bugnullable ' (' ID ', ' Name ', ' isbug ', ' updatedtime ') VALUES
(1, ' test ', NULL, NULL),
(2, ' test1 ', 1, ' 2017-01-14 10:05:15 ');

4. Dapper. NET test code

Class Program
{
static void Main (string[] args)
{
<summary>
Database connection string
</summary>
var connectionString = configurationmanager.appsettings["DefaultConnection"]. Trim ();
using (var conn = new Mysqlconnection (connectionString))
{
var bugs = conn. Query<bugnullable> ("SELECT * from Bugnullable ORDER by ID ASC;"). ToList (); No bug
Sqlmapper.gridreader GR = conn. Querymultiple ("SELECT * from Bugnullable ORDER by ID ASC;"); /bug
var bugs = gr. Read<bugnullable> (). ToList ();

Bugs. ForEach (h = {
Console.WriteLine ($ "Id:{h.id},name:{h.name},isbug:{h.isbug},updatedtime:{h.updatedtime}");
});
Conn. Close ();
}

Console.ReadLine ();
}
}

public class Bugnullable
{
public int ID {get; set;}
public string Name {get; set;}
public bool? Isbug {get; set;}

Public DateTime? updatedtime {get; set;}
}

5. Results

6. Solution: Modify the Database field size (the tinyint (1) corresponding to bool expands appropriately, such as tinyint (2) or bit (1).

Dapper nullable bool Conversion error and solution