Skip to content

NpgsqlDataReader.GetValue for timestamptz column returns DateTime instead of DateTimeOffset #1444

@phdonnelly

Description

@phdonnelly

I've attached a quick patch which solves the problem for me below.

DateTimeOffsetfix.zip

This manifests as a problem when using Entity Framework in certain circumstances(more details below) but the actual issue stems from the npgsql.TypeHandler code so I thought it was more appropriate to post here; It seems to be a legitimate mishandling/bug, as the CLR->PGSQL conversion is DateTimeOffset->timestamptz, so the reverse operation for PGSQL->CLR should be timestamptz->DateTimeOffset instead of DateTime as it is currently.

The root cause appears to be the fact that TimeStampTzHandler, as a consequence of being subclassed from TimeStampHandler in order to make use of the common NpgsqlDateTime based functions there, is a subclass of TypeHandler<DateTime> instead of being a subclass ofTypeHandler<DateTimeOffset>, and therefore the return type of Read(ReadBuffer buf, int len, FieldDescription fieldDescription = null) is DateTime rather than DateTimeOffset.

The proposed patch moves the common TimeStamp handling functions into a new base class TimeStampCommonHandler, and has TimeStampHandler and TimeStampTzHandler inherit from that.

Steps to reproduce

Create an Entity class that includes a DateTimeOffset parameter with either a DatabaseGeneratedOption.Identity or DatabaseGenerated(DatabaseGeneratedOption.Computed attribute, e.g.

    public class Example
    {
        [Key]
        [TableColumnAttribute(TableColumnType.Id)]
        public string Id { get; set; }

        [Index(IsClustered = true)]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [TableColumnAttribute(TableColumnType.CreatedAt)]
        public DateTimeOffset? CreatedAt { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        [TableColumnAttribute(TableColumnType.UpdatedAt)]
        public DateTimeOffset? UpdatedAt { get; set; }
    }

create a DbContext class to handle it, e.g.

    public class PgContext : DbContext
    {
        private const string connectionStringName = "pgsql";
        public PgContext() : base(connectionStringName)
        {
        }
        public DbSet<Example> ExampleList { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Add(
                new AttributeToColumnAnnotationConvention<TableColumnAttribute, string>(
                    "ServiceTableColumn", (property, attributes) => attributes.Single().ColumnType.ToString()));
        }
    }

then use the context to create and save a new object, e.g.:

            PgContext context = new PgContext();

            Customer e = new Customer();
            e.Id = Guid.NewGuid().ToString();
            context.ExampleList.Add(e);
            context.SaveChanges();

The issue

context.SaveChanges() throws a System.Data.Entity.Infrastructure.DbUpdateException exception, which is the result of the EF trying to Cast the DateTime returned from NpgsqlDataReader.GetValue into a DateTimeOffset

Exception message: A store-generated value of type 'System.DateTime' could not be converted to a value of type 'System.DateTimeOffset' required for member 'CreatedAt' of type 'ConsoleApplication1.Customer'.
Stack trace:
       at System.Data.Entity.Internal.InternalContext.SaveChanges() in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Internal\InternalContext.cs:line 441
       at System.Data.Entity.Internal.LazyInternalContext.SaveChanges() in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Internal\LazyInternalContext.cs:line 202
       at System.Data.Entity.DbContext.SaveChanges() in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\DbContext.cs:line 332
       at ConsoleApplication1.Program.Main(String[] args) in C:\Users\pdadmin\Downloads\wtf\ConsoleApplication1\Program.cs:line 202
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 
       HResult=-2146233087
       Message=A store-generated value of type 'System.DateTime' could not be converted to a value of type 'System.DateTimeOffset' required for member 'CreatedAt' of type 'ConsoleApplication1.Customer'.
       Source=EntityFramework
       StackTrace:
            at System.Data.Entity.Core.Mapping.Update.Internal.PropagatorResult.AlignReturnValue(Object value, EdmMember member) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Mapping\Update\Internal\PropagatorResult.cs:line 287
            at System.Data.Entity.Core.Mapping.Update.Internal.PropagatorResult.SetServerGenValue(Object value) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Mapping\Update\Internal\PropagatorResult.cs:line 226
            at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.BackPropagateServerGen(List`1 generatedValues) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Mapping\Update\Internal\UpdateTranslator.cs:line 558
            at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update() in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Mapping\Update\Internal\UpdateTranslator.cs:line 432
            at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<>c.<Update>b__21_0(UpdateTranslator ut) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\EntityClient\Internal\EntityAdapter.cs:line 74
            at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\EntityClient\Internal\EntityAdapter.cs:line 117
            at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update() in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\EntityClient\Internal\EntityAdapter.cs:line 74
            at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__153_0() in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Objects\ObjectContext.cs:line 3168
            at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Objects\ObjectContext.cs:line 3291
            at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Objects\ObjectContext.cs:line 3166
            at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass148_1.<SaveChangesInternal>b__0() in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Objects\ObjectContext.cs:line 3039
            at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Infrastructure\DefaultExecutionStrategy.cs:line 45
            at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Objects\ObjectContext.cs:line 3038
            at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Objects\ObjectContext.cs:line 3017
            at System.Data.Entity.Internal.InternalContext.SaveChanges() in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Internal\InternalContext.cs:line 437
       InnerException: 
            HResult=-2147467262
            Message=Invalid cast from 'System.DateTime' to 'System.DateTimeOffset'.
            Source=mscorlib
            StackTrace:
                 at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
                 at System.DateTime.System.IConvertible.ToType(Type type, IFormatProvider provider)
                 at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
                 at System.Data.Entity.Core.Mapping.Update.Internal.PropagatorResult.AlignReturnValue(Object value, EdmMember member) in C:\Users\pdadmin\Downloads\wtf\EntityFramework6\src\EntityFramework\Core\Mapping\Update\Internal\PropagatorResult.cs:line 275
            InnerException: 

Further technical details

Npgsql version: 3.0
PostgreSQL version: 9.5
Operating system: Server 2012R2

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions