Problem with DateOnly mapping when updating form v2.1.35 to 2.1.44

[glebron-woffu]

I used to have this custom type handler configured:
SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler());

Mapping class is:

public class DapperSqlDateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly>
{
    public override void SetValue(IDbDataParameter parameter, DateOnly date)
        => parameter.Value = date.ToDateTime(new TimeOnly(0, 0));

    public override DateOnly Parse(object value)
        => DateOnly.FromDateTime((DateTime)value);
}

This was working fine, but looks like the DateOnly support was added in 2.1.37 but
Now I am getting this error:

System.Data.DataException
Error parsing column 1 (Date=669745 - Int32)
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in /_/Dapper/SqlMapper.cs:line 3948
Inner exception:
InvalidCastException: Unable to cast object of type 'System.DateTime' to type 'System.DateOnly'.

Thing is, the Date column is column 1, but somehow is parsing column 0 value
Query result:
669745 2023-11-01 NULL NULL ...

So I am a bit confused, in v2.1.35 I didn't have this problem. I removed custom mapper SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler()); and still same problem.
Edit:
In another query, I set date only field as first one:
2023-11-01 0 0
The error is:

System.Data.DataException
Error parsing column 0 (Date=n/a - Unable to cast object of type 'System.DateTime' to type 'System.DateOnly'.)
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in /_/Dapper/SqlMapper.cs:line 3948
   at Deserialize68929759-6271-4665-a68e-ff25a40601eb(DbDataReader)
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command)

The class is:

public record TestClass
{
    public DateOnly Date { get; set; }
    public int First { get; set; }
    public int Second { get; set; }
}

[mkorsukov]

@GonzaloVisma Do you use the System.Data.SqlClient or Microsoft.Data.SqlClient in a combination with Dapper? I have been using the first one with the same workaround you listed above. But recently I have switched to Microsoft.Data.SqlClient and workaroud is not required any more.

[glebron-woffu]

@GonzaloVisma Do you use the System.Data.SqlClient or Microsoft.Data.SqlClient in a combination with Dapper? I have been using the first one with the same workaround you listed above. But recently I have switched to Microsoft.Data.SqlClient and workaroud is not required any more.

I am using Microsoft.Data.SqlClient already, looks like versions > v2.1.35 are unlisted now, maybe they were several issues with those versions an this was one of them.
Also, someone else reported this issue after me #2072 and in that thread there are more answers of this same problem

[mkorsukov]

@GonzaloVisma Here is my current setup for data-related project:

<ItemGroup>
  <PackageReference Include="Dapper" Version="2.1.37" />
  <PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.0" />
</ItemGroup>

[glebron-woffu]

@GonzaloVisma Here is my current setup for data-related project:

<ItemGroup>
  <PackageReference Include="Dapper" Version="2.1.37" />
  <PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.0" />
</ItemGroup>

Mine was

<ItemGroup>
  <PackageReference Include="Dapper" Version="2.1.44" />
  <PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.0" />
</ItemGroup>

[mgravell]

I have been unable to get a repro of this failing; for now I guess I have no choice except to revert that change, but if anyone who was seeing this problem could show a runnable repro against 2.1.44 (or similar affected), I'd be very grateful

[akurone]

I have been unable to get a repro of this failing; for now I guess I have no choice except to revert that change, but if anyone who was seeing this problem could show a runnable repro against 2.1.44 (or similar affected), I'd be very grateful

hello @mgravell,

i am experiencing the same issue; i could not produce a runnable repro; but i tried to pin point it; afaik, there is no direct cast from DateTime to DateOnly:

MSSQL might have a suitable data type that can be casted as DateOnly but i am working with ORACLE and this is not the case here.

i had a custom converter for DateOnly which worked fine before 2.1.44:

with 2.1.44 my custom converter never gets invoked and i get the cast error.

btw, i expected custom converter to take precedence but that did not work as i expected; so i reverted back to 2.1.35

hope this helps.

[ThomasHeijtink]

Is there already an update on this issue?

[glebron-woffu]

Is there already an update on this issue?

I'm affraid there is no news...
I am still using the customs handlers

public class DapperSqlDateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly>
{
    public override void SetValue(IDbDataParameter parameter, DateOnly date)
        => parameter.Value = date.ToDateTime(new TimeOnly(0, 0));

    public override DateOnly Parse(object value)
        => DateOnly.FromDateTime((DateTime)value);
}

public class DapperSqlTimeOnlyTypeHandler : SqlMapper.TypeHandler<TimeOnly>
{
    public override void SetValue(IDbDataParameter parameter, TimeOnly time)
    {
        parameter.Value = time.ToString();
    }

    public override TimeOnly Parse(object value) => TimeOnly.FromTimeSpan((TimeSpan)value);
}

And registering them in program.cs

SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler());
SqlMapper.AddTypeHandler(new DapperSqlTimeOnlyTypeHandler());

[DottorPagliaccius]

I managed to make it work with 2.44.

SqlMapper.RemoveTypeMap(typeof(DateOnly));
SqlMapper.RemoveTypeMap(typeof(DateOnly?));
SqlMapper.RemoveTypeMap(typeof(TimeOnly));
SqlMapper.RemoveTypeMap(typeof(TimeOnly?));

SqlMapper.AddTypeHandler(typeof(DateOnly?), new DateOnlyTypeHandler());
SqlMapper.AddTypeHandler(typeof(TimeOnly?), new TimeOnlyTypeHandler());

For years we've been stuck with this ugly workaround in every project. Why can't we just fix it?

[mgravell]

I can't stop you doing that, but keep in mind that 2.1.44 is unlisted:

which leads onto the answer to your question:

Why can't we just fix it?

Because:

• it isn't that simple - "fixing" something for some people at the cost of breaking things for other people: doesn't work
• at the current time, I simply haven't had time to go through and figure out every possible break/fix scenario, and thread the needle of finding a solution that works for everyone
• almost every moment of effort on this comes from the private-time (i.e. not work time) of a tiny handful of people, mostly me, and our (my) time is finite and limited
• there may exist a temporary workaround via AOT, where I have (today) merged the DateTime etc pieces
• you say you have an ugly workaround that works for today: great! do that! that's better than me deploying things in a sometimes-broken state, until I can get to the bottom of things