Npgsql EF 6: inserting a UTC date time value fails although the underlying column has a timestampTz-CodePudding

I recently updated my app to: Vs 2019, EntityFramework.6.4.4, EntityFramework6.Npgsql.6.4.3 and Npgsql.6.0.4

Before, everything was working well. After the update, I tested different use cases on inserting and reading to and from the database (postgreSql V13).

On inserting a simple entity with a timestampTz value, I got the following exception:

Cannot write DateTime with Kind=UTC to PostgreSQL type 'timestamp without time zone', consider using 'timestamp with time zone'. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.

Here are the code snippets I use. The entity class (POCO):

    [DataContract]
    [Table("TSHH_ShiftHistory", Schema = "ppc")]
    public class TSHH_ShiftHistory
    {
        [DataMember]
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int SHH_RecordingOrder { get; set; }
        //..
        [DataMember]
        public DateTime? SHH_Started_UTC { get; set; }
        [DataMember]
        public DateTime? SHH_Finished_UTC { get; set; }
        // .. 
    }

And writing to the database:

    shiftHistory = new TSHH_ShiftHistory();
    shiftHistory.SHH_Started_UTC = dateTimeStarted; // in UTC format
    shiftHistory.SHH_Finished_UTC = dateTimeFinished; // in UTC format

    db.TSHH_ShiftHistory.Add(shiftHistory);

    int sc = db.SaveChanges();

Here are the sites, where I tried to get help:

https://www.npgsql.org/doc/release-notes/6.0.html#timestamp-rationalization-and-improvements https://www.npgsql.org/doc/types/datetime.html#timestamps-and-timezones https://www.npgsql.org/doc/api/NpgsqlTypes.NpgsqlDbType.html

Npgsql EF 6: Timestamp fails when using ExecuteSqlInterpolatedAsync https://github.com/npgsql/npgsql/issues/2669 https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations#the-valueconverter-class

The solution:

AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);

Of course, I read it in the articles, but I didn't saw a need to go backwords, when I want the new behaviour concerning timestamp with time zone mapping since npgsql 6.0. But here it comes. I experimented with setting the switch to true and false. Supprisingly, the exception didn't raise, independently of the set value.

At least I uncommented the line "AppContext.SetSwitch(..);" and the exception raised again on inserting a UTC DateTime value.

My suspect: I wonder, if the missing "AppContext.SetSwitch(..);" statement is an not initalised behaviour and leads unpredictable result.

I would be pleased, if anyone could confirm my suspect, maybe @Shay Rojansky?

CodePudding user response：

You're using the old (non-core) EF6, which most probably would have to be adjusted as well in order to account for the new timestamp behavior (the EF Core provider definitely did). That's unfortunately not going to happen, since EF6 isn't being developed any more.

I would in general recommend against running EF6 with newer versions of Npgsql; those combinations aren't tested, and may fail in various ways. With EF6 being effectively frozen, I'd recommend keeping the same version of Npgsql as well.