Please add support for Nodatime

[derekgreer]

Please add support for Nodatime

[mgravell]

And what would support for nodatime look like? Genuine question - not meant in a sarcastic way (tone doesn't convey well on the internet).

For example, if you are a nodatime user, it might be possible for you to create a plugin to do this via the "type handler" API, similar to how Dapper.EntityFramework adds support for the EF geography / geometry types. A separate Dapper.Nodatime package / project (referencing dapper) could be added and maintained by anyone. Likewise, it could be added to the main dapper solution, but I would be reluctant to add it to the core library, simply because of dependency explosion, etc.

Your thoughts?

[mgravell]

More on type handlers: http://blog.marcgravell.com/2014/07/dapper-gets-type-handlers-and-learns.html - and here's an example of one: https://github.com/StackExchange/dapper-dot-net/blob/master/Dapper.EntityFramework%20NET45/DbGeographyHandler.cs

[mattjohnsonpint]

FYI - I am a contributor to Noda Time, and am looking into this. Likely, it will be a Dapper.NodaTime package, as suggested.

Incidentally - I did something similar for RavenDB awhile back.

[mgravell]

I'll look forward to it ;p

[mattjohnsonpint]

Sorry for the delay. I've got a good start here. https://github.com/mj1856/Dapper-NodaTime

Question - does Dapper support complex types? Noda's ZonedDateTime would have to be saved as two separate fields in SQL (datetimeoffset + nvarchar)

[mgravell]

Currently: no. It simply hasn't needed it isn't necessarily something I'm opposed to - it just hasn't been needed so far. On 5 Dec 2014 06:36, "Matt Johnson" notifications@github.com wrote:

Sorry for the delay. I've got a good start here. https://github.com/mj1856/Dapper-NodaTime

Question - does Dapper support complex types? Noda's ZonedDateTime would have to be saved as two separate fields in SQL (datetimeoffset + nvarchar)

— Reply to this email directly or view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/198#issuecomment-65753017 .

[NickCraver]

@mj1856 Any update on this? It's been a while, I'm curious what you're doing for a current solution.

[mattjohnsonpint]

Sorry, I haven't had much time to play with it since then. The parts that are finished should work. Feel free to fork and send a PR if you like, or LMK what parts you need next.

[Birgerg]

I'm struggling to get Dapper to support reading Oracles TimestampTZ to NodeTime.ZonedDateTime. (Writing it went fine using a SqlMapper.TypeHandler)

Do I have to change the Dapper source to make it map TimestampTZ to anything other that System.DateTime when reading ?

[mattjohnsonpint]

@BirgerGro - Good question. I know timestamptz in PostgreSQL doesn't actually store the time zone part. It just uses it in the session, then converts to UTC and stores as UTC. On retrieval, it uses whatever the current session's time zone is. I'm not sure if Oracle has the same behavior or if it actually stores the supplied time zone. I'd have to experiment more.

Would you please open a separate issue so I don't forget? Thanks. https://github.com/mj1856/Dapper-NodaTime/issues

[dlumeida]

@mgravell Npgsql states that

Since 4.0, Npgsql supports type plugins, which are external nuget packages that modify how Npgsql maps PostgreSQL values to CLR types. One of these is the NodaTime plugin, which makes Npgsql read and write NodaTime types. The NodaTime plugin is now the recommended way to interact with PostgreSQL date/time types, and isn't the default only because of the added dependency on the NodaTime library. https://www.npgsql.org/doc/types/nodatime.html

When using the Npgsql.NodaTime plugin it doesn't allow to use any (.Net) native date and time types, instead only NodaTime types are allowed, what makes @mj1856 Dapper-NodaTime not compatible since it relies in converting Noda types into DateTime/DateTimeOffset.

When using SELECT command Dapper works well, but when setting value to parameters the exception throw new NotSupportedException($"The member {name} of type {type.FullName} cannot be used as a parameter value"); in SqlMapper.cs is raised. In order to make it work with Npgsql.NodaTime I had to implement something like this

public class InstantHandler : SqlMapper.TypeHandler<Instant>
{
    private InstantHandler() { }

    public override void SetValue(IDbDataParameter parameter, Instant value)
    {
        parameter.Value = value;
    }

    // This is not necessary since Npgsql alredy provide the correct typed value
    public override Instant Parse(object value)
    {
        return (Instant)value;
    }
}

Isn't there a better way? maybe handling it in the LookupDbType method returning DbType.Object instead of raising an exception? Thanks.

[alyssa-dahlberg]

Isn't there a better way? maybe handling it in the LookupDbType method returning DbType.Object instead of raising an exception? Thanks.

Note that if you are using the Npgsql.NodaTime plugin you can tell Dapper to pass the types through as is and let the plugin deal with them like this:

        SqlMapper.AddTypeMap(typeof(Instant), DbType.DateTime2);

I'm not sure how you'd add that logic to Dapper itself without the external dependency, but you could do it with a another package I guess.

[alyssa-dahlberg]

I note there is a special case in there for the LINQ binary type, but I assuming we probably don't want to encourage that style of code?

https://github.com/StackExchange/Dapper/blob/e47d819d4e3be933af97bde6b1b6f4f8c41fa534/Dapper/SqlMapper.cs#L327

[sam-mfb]

Currently: no. It simply hasn't needed it isn't necessarily something I'm opposed to - it just hasn't been needed so far. On 5 Dec 2014 06:36, "Matt Johnson" notifications@github.com wrote:

Sorry for the delay. I've got a good start here. https://github.com/mj1856/Dapper-NodaTime Question - does Dapper support complex types? Noda's ZonedDateTime would have to be saved as two separate fields in SQL (datetimeoffset + nvarchar)

I realize this is now several years old, but it seems like generally these days there is more movement towards being more intentional about datetime storage (e.g., TemporalJS proposal, .NET 6 Preview time features, RFC3339 Extension proposal), and, selfishly, I'd like to be able to use those formats--especially ZonedDateTime--in apps that use Dapper.

The stumbling block seems to be what was identified several years ago, namely, that while there seems to be some movement towards having a unified code object and serialization format for ZonedDateTime, various flavors of SQL are still going to require you to store that information in two fields, namely, a datetime field for the date-time-offset and a char field for the time zone information. And that is where we hit the problem above, i.e., for dapper to support a type like ZonedDateTime there needs to be a way to map this single type back and forth two database columns.

I'm not sure if that's enough to justify adding to Dapper but I thought I'd at least ask. It doesn't seem like there's any other way to solve this problem--at least not until major dbs support a unified zoneddatetime type.