有没有一种简单的方法可以使用数据注释或自定义类型,将存储在SQL中的值用作EF中的DateTime?

好吧,我已经通过下载和调查EF源代码找到了答案。有几个类需要重写。这在我使用的EF Core 2.1.2中有效,因此不能保证旧版本或更新版本(因为API声明这些类可能会被更改),但如果有问题的话,希望只有很小的更改。

需要一个类在字符串和日期之间进行转换:

public class StringDateConverter : ValueConverter<DateTime?, string>
{
    // these can be overridden
    public static string StringDateStorageType = "char(8)";
    public static string StringDateStorageFormat = "yyyyMMdd";
    public static string StringDateEmptyValue = "00000000";

    protected static readonly ConverterMappingHints _defaultHints
        = new ConverterMappingHints(size: 48);

    public StringDateConverter()
        : base(ToString(), ToDateTime(), _defaultHints)
    {
    }

    protected new static Expression<Func<DateTime?, string>> ToString()
        => v => DateToString(v);

    protected static Expression<Func<string, DateTime?>> ToDateTime()
        => v => StringToDate(v);

    private static string DateToString(DateTime? date)
    {
        if (date.HasValue)
            return date.Value.ToString(StringDateStorageFormat);

        return StringDateEmptyValue;
    }

    private static DateTime? StringToDate(string date)
    {
        if (!string.IsNullOrWhiteSpace(date) 
            && !(date == StringDateEmptyValue)
            && DateTime.TryParseExact(date, StringDateStorageFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime result))
            return result;

        return null;
    }
}

这个类继承EF SqlServerDateTimeTypeMapping并使用上面的转换器。

public class SqlServerDateTypeMapping : Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDateTimeTypeMapping
{
    public SqlServerDateTypeMapping() 
        : this(StringDateConverter.StringDateStorageType, System.Data.DbType.String)
    {
    }

    public SqlServerDateTypeMapping(string storeType, DbType? dbType = null) 
        : base(storeType, dbType)
    {
    }

    protected SqlServerDateTypeMapping(RelationalTypeMappingParameters parameters)
        : base(parameters)
    {
    }

    public override DbType? DbType => System.Data.DbType.String;

    protected override string SqlLiteralFormatString
        => StoreType == StringDateConverter.StringDateStorageType
            ? "'" + StringDateConverter.StringDateStorageFormat + "'"
            : base.SqlLiteralFormatString;

    public override ValueConverter Converter => new StringDateConverter();

    // ensure cloning returns an instance of this class

    public override RelationalTypeMapping Clone(in RelationalTypeMappingInfo mappingInfo)
    {
        return new SqlServerDateTypeMapping();
    }

    public override RelationalTypeMapping Clone(string storeType, int? size)
    {
        return new SqlServerDateTypeMapping();
    }

    public override CoreTypeMapping Clone(ValueConverter converter)
    {
        return new SqlServerDateTypeMapping();
    }
}

public class SqlServerTypeMappingSource : Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerTypeMappingSource
{
    public SqlServerTypeMappingSource(TypeMappingSourceDependencies dependencies, RelationalTypeMappingSourceDependencies relationalDependencies) : base(dependencies, relationalDependencies)
    {
    }

    protected override RelationalTypeMapping FindMapping(in RelationalTypeMappingInfo mappingInfo)
    {
        if (mappingInfo.ClrType == typeof(DateTime) && mappingInfo.StoreTypeName == StringDateConverter.StringDateStorageType)
            return new SqlServerDateTypeMapping();

        return base.FindMapping(mappingInfo);
    }
}

可以在DbContext的OnConfiguring方法中替换EF default mapping服务:

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.ReplaceService<IRelationalTypeMappingSource, CommonComponents.Data.SqlServerTypeMappingSource>();
        optionsBuilder.UseSqlServer(Data.Configuration.ConnectionString);
    }

现在在POCO中指定属性如下:

    [Column(Order = 10, TypeName = "char(8)")]
    public DateTime? SomeDate { get; set; }

public class ClassName
{
    private const format = "yyyyMMdd" //for example

    [NotMapped]
    public DateTime Date {
       get {
             DateTime.Parse(Date, format, CultureInfo.InvariantCulture);
           }
       set {
             Date = value.ToString(format);
           }
     }

     [column("Date")]
     public string StringDate { get; set; }
}