NPoco / PetaPoco and Noda Time types

Question

I'm just starting to play around with NPoco, but as of yet I haven't located what I need in the docs.

For instance, let's say I have a field Created, which is an Instant in my domain, but a DateTimeOffset set to UTC in my database. Is there a way to have NPoco convert these types?

Not familiar with NPoco, but you are on the right track. In most cases ORMs and DocDBs will need custom instruction to work with NodaTime types. I've done this for RavenDB with JSON.net, and am working on a solution for Entity Framework. But you will need to understand how your specific ORM maps types to/from the underlying ADO.Net parameters. Ultimately you need `Instant.ToDateTimeOffset()` and `Instant.FromDateTimeOffset()`. Also, for an `Instant`, it would be acceptable to use a UTC `DateTime` instead if you wish. — Matt Johnson-Pint, Jan 12 '14 at 18:40
Just took a quick look, and I see that PetaPoco mentions the ability to install "Value Converters" on their [main list of features](http://www.toptensoftware.com/petapoco/). You should explore that further. I didn't see anything similar for NPoco, so I am guessing you would have to modify their source code. Good luck! — Matt Johnson-Pint, Jan 12 '14 at 18:52
I see an example of creating a value converter in [this answer](http://stackoverflow.com/a/10048731/634824). You can probably adapt that to work with a NodaTime `Instant` instead of the type it was written for. — Matt Johnson-Pint, Jan 12 '14 at 19:04
@MattJohnson thanks for the link, I will just have to put the time into figuring out that IMapper interface. — Matthew Vines, Jan 13 '14 at 21:45

score 2 · Answer 1 · answered Jan 14 '14 at 03:35

This is pretty easy. The example below is what is used to deal with the PostgreSQL datetime types, however you can probably adapt this code fairly easy.

public class Mapper : DefaultMapper
{
    public override Func<object, object> GetFromDbConverter(Type DestType, Type SourceType)
    {
        if (DestType == typeof(DateTimeOffset) || DestType == typeof(DateTimeOffset?)
            || DestType == typeof(DateTime) || DestType == typeof(DateTime?))
        {
            return x =>
            {
                if (x is NpgsqlTimeStampTZ)
                {
                    if (DestType == typeof(DateTime))
                        return (DateTime)((NpgsqlTimeStampTZ)x);
                    if (DestType == typeof(DateTime?))
                        return (DateTime?)((NpgsqlTimeStampTZ)x);
                    if (DestType == typeof(DateTimeOffset))
                        return (DateTimeOffset)((NpgsqlTimeStampTZ)x);
                    if (DestType == typeof(DateTimeOffset?))
                        return (DateTimeOffset?)((NpgsqlTimeStampTZ)x);
                }
                if (x is NpgsqlTimeStamp)
                {
                    if (DestType == typeof(DateTime))
                        return (DateTime)((NpgsqlTimeStamp)x);
                    if (DestType == typeof(DateTime?))
                        return (DateTime?)((NpgsqlTimeStamp)x);
                }
                if (x is NpgsqlDate)
                {
                    if (DestType == typeof(DateTime))
                        return (DateTime)((NpgsqlDate)x);
                    if (DestType == typeof(DateTime?))
                        return (DateTime?)((NpgsqlDate)x);
                }

                return x;
            };
        }

        return base.GetFromDbConverter(DestType, SourceType);
    }

    public override Func<object, object> GetToDbConverter(Type DestType, Type SourceType)
    {
        if (SourceType == typeof(Instance)) {
            return x => { return ((Instance)x).ToDateTimeOffset(); } // etc or something like this
        }

        return base.GetToDbConverter(DestType, SourceType);
    }
}

If you have any further questions post a question to the issues page on github. Cheers, Adam

I'm curious why at runtime, in GetToDbConverter, why are DestType, and SourceType always the same type. I would expect, as did Matt Johnson looking at his code that destType would correspond to the type in the database. — Matthew Vines, Jan 16 '14 at 05:07
The dest column type has to be set. Either using `[ColumnType]` attribute or using `WithDbType` fluent configuration. — Schotime, Jan 17 '14 at 05:19

score 2 · Accepted Answer · edited Sep 17 '21 at 03:34

2

I expanded on the interface that Schotime demonstrated and built it out for several NodaTime types. Too big to paste here, so here is a GIST.

Note that it doesn't include ZonedDateTime, which would have to be serialized to two different fields, so I'm not sure how NPoco would deal with that. I also didn't include Period, since there's no great corresponding type other than a varchar.

Warning: This code is completely untested. I'm just applying what I know about Noda Time conversions to the overrides that the other answer illustrated. Please test it thoroughly before using in your code.

edited Sep 17 '21 at 03:34

Community

1
1

answered Jan 15 '14 at 08:04

Matt Johnson-Pint

230,703
74
448
575

SoureType and DestType in the method GetToDbConverter seem to always be the same type. I do have something working that assumes that I want a DateTimeOffset from Instant, so thanks for putting that out there. I am curious how I could make it handle different output types if I wanted to. – Matthew Vines Jan 16 '14 at 05:10

NPoco / PetaPoco and Noda Time types

2 Answers2