Mapping '-infinity' to DateTimeOffset fails when used with Npgsql

[yuretz]

The following code

var result = connection.QuerySingleOrDefault<Dto>("SELECT '-infinity'::timestamptz AS Value");

public class Dto
{
    public DateTimeOffset Value { get; set; }
}

throws a DataException: Error parsing column 0 (value=1/1/0001 12:00:00 AM - DateTime) with the stack trace:

   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in /_/Dapper/SqlMapper.cs:line 3706
   at Deserialized3c1b0f9-a4d0-4ee4-9b2d-42e7fb1f3c42(IDataReader )
   at Dapper.SqlMapper.QueryRowImpl[T](IDbConnection cnn, Row row, CommandDefinition& command, Type effectiveType) in /_/Dapper/SqlMapper.cs:line 1198
   at Dapper.SqlMapper.QuerySingleOrDefault[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 814
   at UserQuery.Main(), line 3

Expected result is no exception thrown, DateTimeOffset.MinValue returned in Dto.Value

I'm using Dapper v2.0.123 and Npgsql v6.0.5

This issue might be related to the discussion here #1716 and the fact that Npgsql returns -infinity as DateTime.MinValue, which throws when casted to DateTimeOffset if local timezone is UTC+N. Could it be somehow treated as a special case then?

[PSanetra]

@yuretz did you find a workaround for this issue, besides from setting the AppContext switch, mentioned in the npgsql docs?

Edit: My final workaround was putting a Directory.Build.props file into the root directory of my repository with the following content:

<Project>
    <ItemGroup>
        <!--
        Fixes https://github.com/DapperLib/Dapper/issues/1791
        See https://www.npgsql.org/doc/types/datetime.html#infinity-values
        -->
        <RuntimeHostConfigurationOption Include="Npgsql.DisableDateTimeInfinityConversions" Value="true" />
    </ItemGroup>
</Project>

[yuretz]

@PSanetra Nope, we are just setting the switch, programmatically, as a part of global connection configuration, so that it is more explicit. Would be cool if it was possible to work around it with a custom type handler, but this is not supported for primitive types like DateTimeOffset, unfortunately.