Is there a simple way using data annotations or a custom type to use a value stored as a string in SQL as a DateTime in EF?

Question

I have a database where all of the dates and times are stored as strings [yyyyMMdd] and [hhmmss] respectively.

Is there a data annotation I can add in the POCO class so that it is recognised as the type that it should be?

EG:

[Column(TypeName="varchar(8)", Format="yyyyMMdd"] // Format does not exist!
public DateTime MyDate { get; set; }

Or if not, is there a way to define a custom Type and specify how EF should convert the data between the property value and SQL?

NOTE: I have no desire to use a private and public property pair to convert this client-side. I want basic queries to go to the database which this would prevent.

There is a similar question here which has an answer that I am not looking for: convert-value-when-mapping

Add a standard property of type `string` that maps to the real database column. Then add an extra property (type `DateTime`) that just reads and writes from the `string` property. Then your C# code can deal with the properly typed `DateTime` but keep the `string` up to date automatically. — mjwills, Aug 21 '18 at 12:26
Note, if this was me, I'd be pushing **very hard** to fix the root problem (which is the incorrect data type in the database). — mjwills, Aug 21 '18 at 12:26
Around 1000 tables (rough estimate). Otherwise I'd push it too — Steve Harris, Aug 21 '18 at 14:48
why don't you write some stored procedures to access the data? there you could make the necessary casting — balint, Aug 22 '18 at 07:55

score 2 · Accepted Answer · answered Aug 23 '18 at 10:02

Ok, I have figured it out myself by downloading and investigating the EF source code. A few classes need to be overriden. This works in EF Core 2.1.2 which I am using so no guarantees for older or newer versions (as the API states that these classes may be changed) but hopefully only small changes if there are issues.

So the following will enable a DateTime property (representing just the date) in the POCO to be useable with the actual SQL data being a string. It will be simple to derive additional classes for Times and Booleans.

Need a class to convert between string and date:

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;
    }
}

This class inherits the EF SqlServerDateTimeTypeMapping and makes use of the above converter.

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();
    }
}

Then need a class to override the type mapping service:

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);
    }
}

The EF default mapping service can be replaced in the OnConfiguring method of the DbContext:

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

Now specifying the property in the POCO looks like this:

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

score -1 · Answer 2 · answered Aug 21 '18 at 13:01

-1

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; }
}

answered Aug 21 '18 at 13:01

Abdou

330
1
9

Thanks but EF treats this class as an entity and throws an exception that there is no primary key. Even if it didn't, I don't see a way to specify the column name as this class would need to be used for all dates. – Steve Harris Aug 21 '18 at 15:38
this is just a snippet of code that you're supposed to add to the classes where there is a date property not a standalone class – Abdou Aug 21 '18 at 20:05
Then this isn't even an attempt to answer my question! – Steve Harris Aug 22 '18 at 07:36

Is there a simple way using data annotations or a custom type to use a value stored as a string in SQL as a DateTime in EF?

2 Answers2

Linked