Adding compatibility with DateTimeOffset values in MySQL

[gmartinezsan]

We are trying to add DateTimeOffset support in DBContext model. This type is mapped to a Timestamp in MySQL. The issue we have is that the types are no compatibles automatically in EF. Thus we need to add some extension method or something else so the user can perhaps create a custom conversion or we can provide maybe a generic.
Is there already any methods we can override or implement in the current EF Core infraestructure?
@rowanmiller
@divega
Any comments are highly appreciated.

[ajcvickers]

@gmartinezsan Have you tried returning the appropriate type mapping in your implementation of RelationalTypeMapper?

[gmartinezsan]

hi @ajcvickers , thanks for the reply.
Yes I did. MySQL server do not have any DateTimeOffset equivalent type. So we use Timestamp. The issue is thrown when the DataReader tries to get the value. I'm getting an Invalid Cast Exception.
I was thinking about overriding the DataReader method. Not sure if that could work.
Here's the call stack

System.InvalidCastException was unhandled by user code
  HResult=-2147467262
  Message=Unable to cast object of type 'System.DateTime' to type 'System.DateTimeOffset'.
  Source=System.Data.Common
  StackTrace:
       at System.Data.Common.DbDataReader.GetFieldValue[T](Int32 ordinal)
       at lambda_method(Closure , DbDataReader )
       at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
       at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()
       at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
       at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
       at lambda_method(Closure , QueryContext )
       at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_1`1.<CompileQuery>b__1(QueryContext qc)
       at System.Linq.Queryable.First[TSource](IQueryable`1 source)
       at MySql.Data.EntityFrameworkCore.Tests.FluentAPITests.CanUseModelWithDateTimeOffset() in D:\Repository\Git\7.0\DEVELOPMENT\Tests\MySql.EntityFrameworkCore.Basic.Tests\FluentAPITests.cs:line 77

Thanks in advance for your time.

[ajcvickers]

@gmartinezsan As of now, EF Core only supports types that can be read from the DbDataReader with a call to GetFieldValue<T>() where T is the type. The object returned from the call must be of type T. It looks like GetFieldValue<DateTimeOffset>() is throwing for MySQL. This means a type conversion is required after reading from the data reader. This is not yet supported--it is being tracked by issue #242.

[gmartinezsan]

Thanks for the information @ajcvickers .. I'm closing this issue and follow up with the #242
Regards,

[Antaris]

@gmartinezsan It is possible to resolve your instances of DateTimeOffset by creating a custom implementation of EntityMaterializerSource. I've tackled something similar where I am getting EFCore to support custom type mapping from a string -> TagSet (a custom type which represents a list of tags).

The trick for me, was intercepting how the value is materialized from the value buffer.

Before:

Expression.Convert(CreateReadValueExpression(valueBuffer, index), type);

After:

Expression.Convert(
   Expression.Convert(CreateReadValueExpression(valueBuffer, index), type),
   typeof(TagSet));

This is possible because I had implemented custom explicit operators on my TagSet type:

public static explicit operator TagSet(string tags) => new TagSet(tags);

See the following gist:
https://gist.github.com/Antaris/ca0f271311fdb84ef3a8db95b149a307#file-customentitymaterializersource-cs

With trying to map DateTimeOffset, you may instead have to generate a delegate method expression whereby you can do the mapping from the timestamp type to a DateTimeOffset, because you can't apply those operators to the DateTimeOffset type.

Perhaps define a helper class

public static class DateTimeMapper
{
    public DateTimeOffset Map(DateTime value) 
        => new DateTimeOffset(
            new DateTime(value.Year, value.Month, value.Day), TimeSpan.Zero);
}

And then generate the mapping expression using a custom EntityMaterializerSource:

public class CustomEntityMaterializerSource : EntityMaterializerSource
{
  public CustomMaterializerSource(IMemberMapper memberMapper)
    : base(mapper) { }

  public override Expression CreateReadValueExpression(Expression valueBuffer, Type type, int index)
  {
    if (type == typeof(DateTimeOffset))
    {
      var method = typeof(DateTimeMapper).GetMethod(nameof(DateTimeMapper.Map));

      return Expression.Call(
        method,
        Expression.Convert(
          CreateReadValueExpression(valueBuffer, index), type));
    }

    return base.CreateReadValueExpression(valueBuffer, type, index);
  }
}

Hopefully the work in #242 makes this somewhat easier.

[idreeshaddad]

Possible with Value Conversions

Define the Converter:

private ValueConverter GetDateTimeToDateTimeOffsetConverter()
{
    var converter = new ValueConverter<DateTimeOffset, DateTime>(
                                requestedWithOffset => requestedWithOffset.DateTime,
                                requested => new DateTimeOffset(requested, TimeSpan.Zero)
                                );

    return converter;
}

Then add it to the property:

entity.Property(e => e.date)
                    .HasColumnName("date")
                    .HasColumnType("timestamp")
                    .HasConversion(GetDateTimeToDateTimeOffsetConverter());