使用 Dapper 获取日期时间作为 UTC

问题描述

我正在使用 Dapper 将我的实体映射到 SQL Server CE.如果我用 Kind=Utc 保存 DateTime，当我读回它时，我会得到一个 DateTime 和 Kind=Unspecified>，这会导致各种问题.

I'm using Dapper to map my entities to SQL Server CE. If I save a DateTime with Kind=Utc, when I read it back I get a DateTime with Kind=Unspecified, which leads to all kind of problems.

示例:

var f = new Foo { Id = 42, ModificationDate = DateTime.UtcNow };
Console.WriteLine("{0} ({1})", f.ModificationDate, f.ModificationDate.Kind);
connection.Execute("insert into Foo(Id, ModificationDate) values(@Id, @ModificationDate)", f);
var f2 = connection.Query<Foo>("select * from Foo where Id = @Id", f).Single();
Console.WriteLine("{0} ({1})", f2.ModificationDate, f2.ModificationDate.Kind);

此代码提供以下输出:

20/09/2012 10:04:16 (Utc)
20/09/2012 10:04:16 (Unspecified)

我知道我应该使用 DateTimeOffset，但不幸的是 SQL CE 不支持这种类型.

I know I should be using a DateTimeOffset, but unfortunately SQL CE has no support for this type.

有解决方法吗?我可以告诉 Dapper 假设所有日期都有 DateTimeKind.Utc 吗?更一般地说，我有哪些选项可以自定义映射?

Is there a workaround? Can I tell Dapper to assume that all dates have DateTimeKind.Utc? And more generally, what are my options to customize the mapping?

我目前的解决方法是在 Dapper 实现结果后修补日期，但它有点味道......

My current workaround is to patch the dates after Dapper has materialized the result, but it kind of smells...

var results = _connection.Query<Foo>(sql, param).Select(PatchDate);

...

static Foo PatchDate(Foo f)
{
    if (f.ModificationDate.Kind == DateTimeKind.Unspecified)
        f.ModificationDate = DateTime.SpecifyKind(f.ModificationDate, DateTimeKind.Utc);
    return f;
}

推荐答案

为寻求简单修复的其他人添加此答案.现在可以通过在 Dapper 中添加 SqlMapper.TypeHandler 来实现这一点.

Adding this answer for anyone else who comes looking for a simple fix. This is possible now with the addition of SqlMapper.TypeHandler in Dapper.

添加此类以将值从 db 转换为指定为 UTC 类型的日期时间.

Add this class to convert the value from the db to a datetime with the kind specified as UTC.

public class DateTimeHandler : SqlMapper.TypeHandler<DateTime>
{
    public override void SetValue(IDbDataParameter parameter, DateTime value)
    {
        parameter.Value = value;
    }

    public override DateTime Parse(object value)
    {
        return DateTime.SpecifyKind((DateTime)value, DateTimeKind.Utc);
    }
}

然后在我的 Web API 的 Global.asax 文件中，我将类型处理程序添加到 dapper.

Then in my Global.asax file of my Web API I add the type handler to dapper.

SqlMapper.AddTypeHandler(new DateTimeHandler());

如果您需要确保始终将日期插入为 UTC，那么您可以使用 SetValue 方法:

If you need to ensure you are always inserting dates as UTC, then on the SetValue method you can use:

parameter.Value = DateTime.SpecifyKind(value, DateTimeKind.Utc);

这篇关于使用 Dapper 获取日期时间作为 UTC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！