Changing the Dapper mapping for .net DateTime to use DbType DateTime2 and reinstating it back again

Question

Similar to the question How can I get Dapper to map .net DateTime to DateTime2; but I want to be able to set it back again afterwards.

The currently accepted answer to that question involves changing a Dapper source file; but I'm using the NuGet package, so that won't work for me. As pointed out by the first comment on the accepted answer, this is not reversible - "What if some are DateTime and others are DateTime2?" - that's my scenario: different queries need different mappings (each query would only need one or the other).

I'm using the higher-voted answer to the same question. However, this approach also seems to not be reversible. It seems like whatever value is set when the first query is done remains, and is not changeable after that.

The following code is an MCVE. If you run it, you'll see the type is always shown as "datetime", and the values never have more precision than a millisecond (as you would expect for a datetime); despite the attempts to change the mapping. Then you must comment out the first call to "PerformDapperQuery()", and run it again: you will now see that the type is always returned as "datetime2", and the values have the full 7-digit precision on fractions of a second (as you would expect for a datetime2).

public static void Main()
{
    // I know this is marked as obsolete, and I am open to suggestions for alternatives.
    // see https://github.com/StackExchange/Dapper/issues/798
#pragma warning disable 618
    var oldValue = SqlMapper.LookupDbType(typeof(DateTime), null, false, out var handler);
#pragma warning restore 618

    PerformDapperQuery();
    SqlMapper.AddTypeMap(typeof(DateTime), DbType.DateTime2);
    PerformDapperQuery();
    SqlMapper.AddTypeMap(typeof(DateTime), DbType.DateTime);
    PerformDapperQuery();
    SqlMapper.AddTypeMap(typeof(DateTime), oldValue);
    PerformDapperQuery();
 }

private static void PerformDapperQuery()
{
    using (var connection = new SqlConnection("server=localhost;Database=master;Integrated Security=SSPI;"))
    {
        var parameters = new { Param = DateTime.Now };
        using (var reader = connection.ExecuteReader(
            "SELECT sql_variant_property(@Param, 'BaseType'), CAST(@PARAM AS datetime2(7))", parameters))
        {
            Assert.That(reader.Read(), Is.True);
            string type = reader.GetString(0);
            DateTime value = reader.GetDateTime(1);
            Console.WriteLine($"Output: {type},{value:o}");
        }
    }
}

So the first part of the problem is: How can I change Dapper's mapping of DateTime more than once? The second part of the problem is that I want to reinstate the previous mapping; but as you can see, LookupDbType is marked as Obsolete, so I'd be interested if there are any alternative approaches.

EDIT after the explanation of caching was given by Damien_The_Unbeliever

I changed the query above to be $"SELECT sql_variant_property(@Param, 'BaseType'), CAST(@PARAM AS datetime2(7)) -- {DateTime.Now:o}", so that it would be different each time, and sure enough, that did change the behaviour.

The reason I came across this was that I wanted to add something to wrap around particular Dapper queries to make them use DateTime2 rather than DateTime, so I had written this class:

internal sealed class DapperDateTime2MapperScope : IDisposable
{
    private readonly DbType? _predecessor;
    private bool _isDisposed;

    public DapperDateTime2MapperScope()
    {
        _predecessor = SqlMapperGetDbType();
        SqlMapper.AddTypeMap(typeof(DateTime), DbType.DateTime2);
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    private void Dispose(bool disposing)
    {
        if (!_isDisposed)
        {
            if (disposing)
            {
                if (_predecessor.HasValue)
                {
                    SqlMapper.AddTypeMap(typeof(DateTime), _predecessor.Value);
                }
            }

            _isDisposed = true;
        }
    }

    private DbType SqlMapperGetDbType()
    {
#pragma warning disable 618
        return SqlMapper.LookupDbType(typeof(DateTime), null, false, out var handler);
#pragma warning restore 618
    }
}

which can then be used to wrap a Dapper query in a using block, to make that query use the DateTime2 mapping:

using (new DapperDateTime2MapperScope())
{
    -- Perform Dapper query here
}

and I then wrote unit tests of that class - one test without the using and one with the using; and I found that the unit tests interacted with each other: they would work individually but when all tests were run, one or other test would fail. And the reason (thanks to Damien's explanation) is Dapper's caching of queries. The good news is that I think that's fine - the problem was being suffered by the unit tests because they were using the same query; but in my real codebase, if I wrap a particular query in this using then I would always want that query to use that mapping. So basically this was just a problem for my unit tests, not for genuine usage of the class.

Answer 1

Your code does correctly change the type mappings - but dapper aggressively caches queries.

If your actual queries vary between the datetime and datetime2 use cases (I would expect they would) then that should be fine. Otherwise you can purge the query cache yourself (but obviously that may have other, knock-on, undesirable consequences):

        public static void Main()
        {
            // I know this is marked as obsolete, and I am open to suggestions for alternatives.
            // see https://github.com/StackExchange/Dapper/issues/798
#pragma warning disable 618
            var oldValue = SqlMapper.LookupDbType(typeof(DateTime), null, false, out var handler);
#pragma warning restore 618

            PerformDapperQuery();
            SqlMapper.AddTypeMap(typeof(DateTime), DbType.DateTime2);
            SqlMapper.PurgeQueryCache();
            PerformDapperQuery();
            SqlMapper.AddTypeMap(typeof(DateTime), DbType.DateTime);
            SqlMapper.PurgeQueryCache();
            PerformDapperQuery();
            SqlMapper.AddTypeMap(typeof(DateTime), oldValue);
            SqlMapper.PurgeQueryCache();
            PerformDapperQuery();
        }

For your use of LookupDbType, I think you could just use GetDbType instead.