CODEWITHSUNDEEP
c#dappernpgsqldapper-simplecrud
user8377550
user8377550

Reputation:

Dapper.SqlMapper.AddTypeHandler appears not to be called with Postgres SimpleCRUD where clause

I have just upgraded from Npgsql v2.2.3 to v3.2.4.1 (now V3.2.5) on a system using Dapper and SimpleCRUD - other absent developers customised version of latter. Its not really my field - I was just trying to do some PostGIS bits with the v3 support...

We have a custom version of DateTime that, under Npgsql v3.x stricter typing, requires us to set the NpgsqlDbType to NpgsqlTypes.NpgsqlDbType.Timestamp. This is done via a Dapper.SqlMapper.AddTypeHandler in the SimpleCRUD code. The custom version forces date time kind to be UTC.

Whilst this works as expected for database inserts, it appears not to being called when a WHERE clause is used - not sure if its just in isolated cases, or just in one or two places.

I can see from the Postgres logs that, the parameters for the WHERE clause are being adjusted for UTC/Local conversion : parameters: $1 = '2017-07-27 13:29:51+01'

Via breakpoints, I can also see that the type handler is not being entered for the WHERE clause, but is for INSERT etc.

QUESTIONS:

Should I be expecting the Dapper.SqlMapper type handler to be called for WHERE clause parameters?

If it is not expected behaviour, how do I force NpgsqlDbType to NpgsqlTypes.NpgsqlDbType.Timestamp when using Dapper.SimpleCRUD?

Many thanks

I have updated to latest NuGet package of Npgsql (3.2.5) and Dapper (1.50.2). Not sure of the origin of Dapper.SimpleCRUD but it would have started life in 2014 before being what looks to have been potentially heavily customised so would rather continue with this rather than upgrading and adding the customisations back in.

Upvotes: 2

Views: 1248

Answers (1)

user8377550
user8377550

Reputation:

Managed to sort the issue.

I added a SqlMapper.ICustomQueryParameter handler (DateTimeUTCParameter) and called this from SimpleCRUD as part of ConstructDynamicParameters instead of doing the conversion directly.

In the end, it was a straight forward change, once I had identified what was needed.

    private static DynamicParameters ConstructDynamicParameters(Dictionary<string, object> conditionParameters)
    {
        var dynParms = new DynamicParameters();
        foreach (var kvp in conditionParameters)
        {
            if (kvp.Value is DateTimeUTC)
                dynParms.Add(string.Format("@{0}", kvp.Key), new DateTimeUTCParameter((DateTimeUTC)kvp.Value));
            else
                dynParms.Add(string.Format("@{0}", kvp.Key), kvp.Value);
        }
        return dynParms;
    }

Upvotes: 1

Related Questions