Is it possible to convert timestamptz to DateTime instead of using DateTimeOffset everywhere?

[kolev9605]

We are doing a PostgreSQL migration to our codebase. One of our services is not yet migrated to .NET 8 but it is using the same DB as the other services which are already migrated to .NET 8.

The problem we are facing is the following:

Let's take a look first at the .NET 8 setup:

Service 1, .NET 8, EF Core 8.

Versions:

...
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.6" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.4" />
...

DB Context & entity:

public class SomeEntity
{
    public int Id { get; set; }

    public DateTime SomeDate { get; set; }
}

public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }

    public DbSet<SomeEntity> SomeEntities { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.ApplyConfigurationsFromAssembly(typeof(MyDbContext).Assembly);

        modelBuilder.Entity<SomeEntity>()
            .Property(x => x.SomeDate)
            .HasColumnType("timestamptz");
    }
}

When I generate a migration, it works fine and the type chosen is timestamp with time zone. From that point onward, read/write operations work fine, and I am able to fetch a UTC timestamp into a DateTime property.

In .NET Framework, though, it is not like that.

Service 2, .NET Framework 4.8, EF6.

Versions:

...
<package id="EntityFramework" version="6.4.0" targetFramework="net48" />
<package id="EntityFramework6.Npgsql" version="6.4.3" targetFramework="net48" />
<package id="Npgsql" version="4.1.3" targetFramework="net48" />
...

DB Context & Entity:

public class SomeEntity
{
    public int Id { get; set; }

    public DateTime SomeDate { get; set; }
}

public class MyDbContext : DbContext
{
    public MyDbContext()
        : base("MyConnection")
    {
    }

    public virtual DbSet<SomeEntity> SomeEntities { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.HasDefaultSchema("public");

        modelBuilder.Entity<SomeEntity>()
            .Property(x => x.SomeDate)
            .HasColumnType("timestamptz");
    }
}

If I try to add a migration here, though, I get the following error:

Schema specified is not valid. Errors: 
(8,12) : error 2019: Member Mapping specified is not valid. The type 'Edm.DateTime[Nullable=False,DefaultValue=,Precision=]' of member 'SomeDate' in type 'NpgsqlTesting.Web.Data.SomeEntity' is not compatible with 'Npgsql.timestamptz[Nullable=False,DefaultValue=,Precision=7]' of member 'SomeDate' in type 'CodeFirstDatabaseSchema.SomeEntity'.

It works fine if I change the type of SomeDate to DateTimeOffset.

I understand (kind of) why it works like that reading the breaking changes with version 6.0 of Npgsql, but is there something we can do to force EF to map the timestamptz columns to DateTime properties?

[Emill]

The type system in EF6 is extremely limited and has more or less no options for such customizations. I suggest you to use EF Core instead on .NET 8.

[kolev9605]

The type system in EF6 is extremely limited and has more or less no options for such customizations. I suggest you to use EF Core instead on .NET 8.

The other service is .NET Framework, so using EF Core is not an option. I will state that more explicitly in the post.

The problem really is how do I store proper UTC timestamp everywhere in the database and at the same time make both services (one is .NET 8 and the other is .NET Framework 4.8) to work properly with the timestamps in the DB.

[Emill]

I would in that case suggest you to have some middle layer where you convert a DateTimeOffset when reading the DB into a DateTime and the other way when you write to the DB. Unless you want to modify the source code of this repo.

The type system in EF6 is not flexible at all. It's designed to work with SQL Server. PostgreSQL has a different set of date/time types which is not really 100% compatible. A different issue with the DateTime type in dotnet is that there is an associated Kind with every value (Local, UTC or Unknown) which complicates it further.

[kolev9605]

I would in that case suggest you to have some middle layer where you convert a DateTimeOffset when reading the DB into a DateTime and the other way when you write to the DB. Unless you want to modify the source code of this repo.

The type system in EF6 is not flexible at all. It's designed to work with SQL Server. PostgreSQL has a different set of date/time types which is not really 100% compatible. A different issue with the DateTime type in dotnet is that there is an associated Kind with every value (Local, UTC or Unknown) which complicates it further.

So far the only solution I have is to store timestamp without timezone in the database and have interceptors in the application setting the Kind to UTC. This is working, but I wanted to use Npgsql as intended, that's why I am asking is there any solution to this case.

some middle layer where you convert a DateTimeOffset when reading the DB into a DateTime and the other way when you write to the DB

How would you achieve that?

[Emill]

The intended use in EF6 when you want to store a time stamp that specifies a specific point in time is to use DateTimeOffset rather than a DateTime with UTC as kind. The "offset" here is the offset from UTC+0. "timestamp without time zone" is mapped to DateTime (therefore with an unknown offset).

With "some middle layer", that would be an "interceptor" or something in your application code that converts between the desired types, outside of EF. From your description it seems you have structured your application in a modular way where it seems this would be possible, as you say you can already set the Kind to UTC.

[kolev9605]

So, the only way I managed to make both .NET Framework and .NET 8 to work with the same DB is the following:

1. All of the timestamps in the database are with type timestamp without timezone
2. Both application will have some interceptor or value converter to set Kind.Utc to all timestamps fetched from the DB
3. In .NET 8 I had to use AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
4. DateTime is used in both applications instead of DateTimeOffset for timestamps fetched from the DB.