Note: phiên bản Tiếng Việt của bài này ở link dưới.

https://duongnt.com/datetime-net6-postgresql-vie

Recently, I upgraded a project from .NET Core 3.1 to .NET 6. This project uses Entity Framework Core with the npgsql efcore provider to interact with a PostgreSQL database. The process went relatively smoothly, until a bunch of my integration tests started to fail with the following error.

Cannot write DateTime with Kind=Unspecified to PostgreSQL type 'timestamp with time zone', only UTC is supported. 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.

Since all unit tests still passed, I was confused. In today’s article, we will find the cause of this error, and try two solutions to fix it.

You can download the sample code from the link below.

https://github.com/duongntbk/PostgreSql6IssueDemo

Set up a demo environment

To run the sample project, we need a PostgreSQL database. The most simple way is to use a Docker image. Alternatively, you can install PostgreSQL database on your machine or use a remote server.

After your database is up and running, please update the connection string in this file with your database name, username, and password. Then you can run the following command in the DbSetup folder to restore the database (you might have to install the dotnet ef tool).

dotnet ef database restore.

This will create a database with a very simple table called Person.

And populate it with some test data.

Reproduce the error

The old code in .NET Core 3.1

The Net3.1 project inside the sample solution contains the original .NET Core 3.1 code. As we can see here, our program simply updates the NickName of one record to a random Guid.

var baby = await dbContext.People.SingleOrDefaultAsync(p => p.Name == "Baby Doe");
baby.NickName = Guid.NewGuid();
dbContext.Set<Person>().Update(baby);
await dbContext.SaveChangesAsync();

We run the following command from the Net3.1 folder.

dotnet run

After execution, we can verify that the NickName of Baby Doe has been updated.

The non-working code in .NET 6

The Net6 project inside the sample solution contains the upgraded .NET 6 code. We run it with the following command.

dotnet run

The result is a Microsoft.EntityFrameworkCore.DbUpdateException.

Why did we get a datetime error?

A change in the LINQ provider

As part of moving the project to .NET 6, I upgraded the the npgsql efcore provider to the latest 6.x version. One of the biggest changes in version 6.x is how timestamps are handled. If the type in the database is timestamp with timezone then we must map it to a DateTime object with DateTime.Kind == DateTimeKind.Utc on the C# side. Conversely, we must map the type timestamp without timezone to a DateTime object with DateTime.Kind == DateTimeKind.Local (or DateTimeKind.Unspecified). At first glance, I thought I was writing a DateTimeKind.Local/DateTimeKind.Unspecified object into a timestamp with timezone column in the database . But that didn’t sound right for two reasons.

• I confirmed that the database is using timestamp without time zone.

  

• I made sure to always convert DateTime objects into Utc time before saving them into the database. This means they should have DateTime.Kind == DateTimeKind.Utc already.

Getting to the actual cause

My first clue came from investigating a retrieved record from the database. This is the value of Dob from the record where Name == "Baby Doe". As we can see, the DateTimeKind of Dob is Unspecified, even though it is supposed to be Utc.

Then I found some hints in this issue in the npgsql repository, in particular this comment.

the error message does not indicate that your PostgreSQL column type is wrong – Npgsql has no knowledge of the column type you’re inserting into, etc. It says that you’ve asked Npgsql to send an Unspecified DateTime as a PG timestamptz type, e.g. by setting NpgsqlDbType.TimestampTz on your parameter; so the mismatch isn’t between the value and the column, but rather between the value and your parameter type.

And I found this question on Stackoverflow, which helped me fill in the rest.

The EF type mapping – which manages the PG type of the parameter sent (timestamp vs. timestamptz) – is determined only by the CLR type of the parameter (DateTime), without looking at its contents (i.e. the Kind). And the default EF mapping for DateTime is timestamptz, not timestamp

Finally, I think I understood the chain of events that led to the error above.

• Because the Dob column inside the database is timestamp without timezone, when npgsql retries that column, its DateTimeKind is set to Unspecified.
• When we write that record back into the database, because Person.Dob is of type DateTime, npgsql set the corresponding to timestamptz (which is timestamp with timezone).
• npgsql detects that it is attempting to set the parameter of a property with DateTimeKind == Unspecified to timestamp with timezone. This contradiction throws an exception.

This also explains why unit tests did not catch this issue. It is because I used EF Core In-Memory to mock out the database. This allowed our unit tests to run fully independently from other dependencies. But its side effect that sometimes we miss little quirks like this.

How to fix this error?

Enable the legacy behaviour

The error message from npgsql efcore already gave us a hint to fix this issue. We can enable the Npgsql.EnableLegacyTimestampBehavior flag as a temporary work-around. We can set this switch via the AppContext.

public DomainContextLegacy(DbContextOptions<DomainContextLegacy> options)
    : base(options)
{
    AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
}

We can verify that the new DomainContextLegacy context can safely update data by running the following command inside the Net6 folder.

dotnet run legacy

The command above will set a new value for the NickName of Baby Doe.

But what if we don’t want to enable the default legacy? Let’s explore a different solution in the next section.

Use a conversion to set DateTimeKind for the DateTime object

As mentioned before, the cause of this error is because the DateTimeKind of Person.Dob properties retrieved by npgsql is set to Unspecified. This means if we set it back to Utc then this error will no longer occur. Indeed, the following code runs successfully.

var baby = await dbContext.Set<Person>().SingleOrDefaultAsync(p => p.Name == "Baby Doe");
baby.NickName = Guid.NewGuid().ToString();
baby.Dob = DateTime.SpecifyKind(baby.Dob, DateTimeKind.Utc); // <-- set DateTimeKind of Dob to Utc
dbContext.Set<Person>().Update(baby);
await dbContext.SaveChangesAsync();

However, it is dangerous to manually set the DateTimeKind. We would have to duplicate this code in different places. And we might even forget to include this conversion. Fortunately, the DbContext class allows us to add a conversion to a property in the DTO object. This conversion will be applied every time npqsql retrieves data from the database, or writes data into the database. We can use the conversion below to automatically set the DateTimeKind of Dob to Utc.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .Property(p => p.Dob)
        .HasConversion
        (
            src => src.Kind == DateTimeKind.Utc ? src : DateTime.SpecifyKind(src, DateTimeKind.Utc),
            dst => dst.Kind == DateTimeKind.Utc ? dst : DateTime.SpecifyKind(dst, DateTimeKind.Utc)
        );
}

We can test this fix with the following command.

dotnet run fixed

Again, the NickName of BabyDoe has been updated.

![Enable Legacy](/wp-content/uploads/2022/10/datetime-net6-postgresql-7.png)

Conclusion

It turns out, updating my project to .NET 6 was trickier than I expected. The issue this time highlighted the value of defense in depth. Even though this bug slipped through my unit tests, it was caught by my integration tests. This is because my integration tests use an actual PostgreSQL instance instead of EF Core In-Memory.