Dapper problem with a nullable Guid? types

Question

I have problem with Dapper and Guid? type. I created that demo project:

Database MySQL (table entities):

CREATE TABLE `entities` (
  `id` int NOT NULL AUTO_INCREMENT,
  `notnullguid` char(36) NOT NULL,
  `nullguid` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

First Guid field - not null, second - nullable (with NULL as default value).

Demo data:

id	notnullguid	nullguid
1	7b137c4e-38db-11eb-adec-a85e45e499a0	NULL
2	f57b9036-38db-11eb-adec-a85e45e499a0	f57b906b-38db-11eb-adec-a85e45e499a0

My entity:

public class Entity
{
    public int Id {get; set; }
    public Guid NotNullGuid { get; set; }
    public Guid? NullGuid { get; set; }
}

My query:

//PROBLEM:
Entity e1 = db.Query<Entity>("SELECT * FROM entities WHERE id=1").First(); //Not working

//WORKING - all variations:
Entity e2 = db.Query<Entity>("SELECT * FROM entities WHERE id=2").First(); //Working
Entity e3 = db.Query<Entity>("SELECT notnullguid FROM ents WHERE id=1;").First(); //Working
Entity e4 = db.Query<Entity>("SELECT notnullguid, NULL as nullguid FROM ents WHERE id=1;").First(); //Working
Entity e5 = db.Query<Entity>("SELECT notnullguid, IFNULL(nullguid, NULL) FROM ents WHERE id=1;").First(); //Working

If I get id=1 I have that exception:

System.Data.DataException: 'Error parsing column 2 (nullguid=7b137c4e-38db-11eb-adec-a85e45e499a0 - Object)' This exception was originally thrown at this call stack:
System.Data.DataException HResult=0x80131501 Message=Error parsing column 2 (nullguid=7b137c4e-38db-11eb-adec-a85e45e499a0 - Object)
Source=Dapper StackTrace: at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in //Dapper/SqlMapper.cs:line 3665 at Dapper.SqlMapper.d__1401.MoveNext() in /_/Dapper/SqlMapper.cs:line 1102 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 //Dapper/SqlMapper.cs:line 725 at DapperGuidParsing.Program.Main(String[] args) in C:\Users\Dmytro\source\repos\DapperGuidParsing\DapperGuidParsing\Program.cs:line 21

This exception was originally thrown at this call stack: System.ThrowHelper.ThrowArgumentOutOfRangeException(System.ExceptionArgument, System.ExceptionResource) System.Text.UTF8Encoding.GetString(byte[], int, int) MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(int, MySql.Data.MySqlClient.MySqlField, MySql.Data.Types.IMySqlValue) MySql.Data.MySqlClient.ResultSet.this[int].get(int) MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(int, bool) MySql.Data.MySqlClient.MySqlDataReader.GetValue(int) MySql.Data.MySqlClient.MySqlDataReader.this[int].get(int) Inner Exception 1: ArgumentOutOfRangeException: Non-negative number required.

Why? :| And how I can fix this?

Some tech info: .NET Core 3.1 Console App, Dapper 2.0.78, MySql.Data 8.0.22.

What if you do Select notnullguid, nullguid From entities Where id =1? — juharr
Post the full exception, not just the message. You can get it easily with Exception.ToString() or by clicking on Click Details on the exception popup. The full text contains the stack trace that shows which chain of calls resulted in the exception. The problem could be caused by a specific Dapper method, or by the MySQL driver itself. Oracle's drivers leave something to be desired, which is why people created third-party providers like MySqlConnector — Panagiotis Kanavos

GRUNGER GRUNGER · Accepted Answer · 2020-12-09T18:28:29

Everything worked as it should, when you install Nuget package MySqlConnector

//Working
IDbConnection db = new MySqlConnector.MySqlConnection(this.ConnectionString);

//Not working (default MySqlConnection from MySql.Data package)
//IDbConnection db = new MySql.Data.MySqlClient.MySqlConnection(this.ConnectionString);

Dapper problem with a nullable Guid? types

1 Answers