How to map C# ticks to PostgreSQL timestamp when using a Protocol Buffer model with Dapper?

Question

How to map C# ticks to PostgreSQL timestamp when using a Protocol Buffer model with Dapper?

c# dapper npgsql postgresql protobuf-net

Question

I'm using protobuf-net in my application to send logged data from a remote site to the server. There are multiple data types - a sample message of one of these is as follows:

message Sample {
    required int64 recording_time = 1; // UTC Timestamp in Ticks
    required double x_position = 2;
    required double y_position = 3;
    required double x_velocity = 4;
    required double y_velocity = 5;
}

On the server, the object is stored in a PostgreSQL database. All the double fields in the Protocol Buffer (proto2) message are mapped to double fields in PostgreSQL. The timestamp field, uint64 recording_time must be mapped to a timestamp with time zone field in the database.

I want to use the same C# class (with ProtoContract attribute) to serialize Sample on the client, and also with Dapper for database operations (perhaps with an extension, e.g. FastCRUD).

This requires a mapping/conversion between ticks (C# type: long) and timestamp with time zone (C# type: DateTime). What is the best way to implement this, without creating a second class?

This is how I currently write objects to database:

string sql = "COPY samples (recording_time, x_position, y_position, x_velocity, y_velocity) FROM STDIN (FORMAT BINARY)";
using (var writer = conn.BeginBinaryImport(sql))
{
    foreach (Sample sample in sampleList)
    {
        writer.StartRow();

        writer.Write(new DateTime(sample.RecordingTime, DateTimeKind.UTC), NpgsqlTypes.NpgsqlDbType.TimestampTZ);

        writer.Write(sample.X_Position, NpgsqlTypes.NpgsqlDbType.Double);
        writer.Write(sample.Y_Position, NpgsqlTypes.NpgsqlDbType.Double);
        writer.Write(sample.X_Velocity, NpgsqlTypes.NpgsqlDbType.Double);
        writer.Write(sample.Y_Velocity, NpgsqlTypes.NpgsqlDbType.Double);

    }
}

This is how I want to write to database:

foreach (Sample sample in sampleList)
{
    conn.Insert<Sample>(sample);
}

And use the corresponding Query method for retrieval.

Abir

View more on Stack Overflow

Marc Gravell · Accepted Answer · 10/5/2016 2:35:13 PM

I want to use the same C# class (with ProtoContract attribute) to serialize Sample on the client, and also with Dapper for database operations (perhaps with an extension, e.g. FastCRUD).

Personally, I think that's the problem - trying to do two very different things with the same object. However! It isn't necessarily impossible. In particular, note that protobuf-net will happily work with private members. So one option might be:

public WhateverYouNeedForTheDatabase Foo { get; set; }

[ProtoMember(someNumber)]
private WhateverYouNeedForTheSerializer FooSerialized {
    get { return FromX(Foo); }
    set { Foo = ToX(value); }
}

You can do similar with dapper, but it is less convenient. In particular, in your case:

public DateTime RecordingTime { get; set; }

[ProtoMember(1)]
private long RecordingTimeSerialized {
    get { return DateTimeToUnixTime(RecordingTime); }
    set { RecordingTime = UnixTimeToDateTime(value); }
}

This has no additional storage requirement (no extra fields).

How to map C# ticks to PostgreSQL timestamp when using a Protocol Buffer model with Dapper?

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator