Error from Dapper with DateTime column in clickhouse

[markatosi]

I'm providing an example implementation to show the error

create table test ( aDate DateTime)
Engine = MergeTree()
Order by aDate;

insert into test (aDate) values ('2022-01-01 12:13:34');

public class TestError
{
public DateTime ADate { get; set; }

public static IEnumerable GetData()
{
var sql = "select aDate as ADate from test";
using var connection = new ClickHouseConnection($"Host={host};Database={db};");
connection.Conn.Query(sql);
}
}

This results in the following error:

System.Data.DataException: Error parsing column 0 (ADate=01/01/2022 12:13:34 +00:00 - Object)
---> System.InvalidCastException: Object must implement IConvertible.
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at Deserializeadc23d33-683a-47b3-93b7-b6c5eebe8df8(IDataReader )
--- End of inner exception stack trace ---
at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in /_/Dapper/SqlMapper.cs:line 3706
at Deserializeadc23d33-683a-47b3-93b7-b6c5eebe8df8(IDataReader )
at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext()
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, Nullable`1 commandType)

This query works fine with a different clickhouse ADO provider using Dapper.

I'm using:
Octonica.ClickHouseClient 2.2.8
Dapper 2.0.123
.Net 6.0

Query returns properly with: ClickHouse.Client 4.2.2

[markatosi]

I see that the default column datatype returned for Clickhouse DateTime is System.DateTimeOffset
I adjusted my class property thusly:

`public class TestError
{
private DateTimeOffset _aDateTimeOffset;
private DateTime _aDateTime;

  public DateTimeOffset ADateTimeOffest 
 {
        get => _aDateTimeOffset;
        set
        {
            _aDateTimeOffset = value;
            _aDateTime = value.DateTime;
        }
 }
  public DateTime ADate { 
      get => _aDateTime;
      set => _aDateTime = value; 
    }

public static IEnumerable GetData()
{
var sql = "select aDate as ADate as ADateTimeOffest from test";
using var connection = new ClickHouseConnection($"Host={host};Database={db};");
connection.Conn.Query(sql);
}
}`

Anyway, this solves the immediate issue. I need to only use DateTime downstream as I don't have control of that code and it expects a System.DateTime and not a System.DateTimeOffset