Comparing EFCore Database Providers-part-2

IDName
1 Part 1
2 Part 2
3 Part 3

I have started with a simple table  –  Department( id autogenerated , name) and generate EFCore context and classes with scaffolding templates https://learn.microsoft.com/en-us/ef/core/managing-schemas/scaffolding/templates?tabs=dotnet-core-cli .

Then I have created a simple text ( XUnit + LightBDD ) in order to test

–  generateDatabase from classes

– CRUD

– some simple search .

Works perfectly, so to the next – more different tables ( datetime, binary, money and so on)  => scaffold code => make tests.

the first problem – column type

When generating the scaffolding the OnModelCreating generates .HasColumnType – for example, 

modelBuilder.Entity<Tbl_DATETIME>(entity =>
{
    entity.HasKey(e => e.ID).HasName("PK__Tbl_DATE__3214EC27B76F5021");

    entity.Property(e => e.DataColumn).HasColumnType("datetime");
});

And PostgresSql has no type that is called “datetime”, but “timestamp” – https://www.postgresql.org/docs/current/datatype-datetime.html

So what I do is to have OnModelCreatingPartial that is called AFTER all set and re-write

modelBuilder.Entity<Tbl_DATETIME>(entity =>
{
    entity.HasKey(e => e.ID).HasName("PK__Tbl_DATE__3214EC27B76F5021");

    entity.Property(e => e.DataColumn).HasColumnType(null);
});

Even if column type is null, the CreateDatabase knows that the field is DateTime


public partial class Tbl_DATETIME
{
    public int ID { get; set; }

    public DateTime? DataColumn { get; set; }
}

so all is ok.

Second Problem – datetime in UTC

The DateTime that is on the class generates a column that supports ONLY UTC kind in datetimes . The error is

–> System.InvalidCastException : Cannot write DateTime with Kind=Local 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.

Youn

Makes sense, usual in database you want just the UTC and transform for each user

Third Problem – datetime have a millisecond difference sometimes

This is the test:

var dataM = new Tbl_DATETIME();
dataM.ID = 3;
dataM.DataColumn = DateTime.Now.AddDays(100);
using (var db = await startDatabase.GetNewContext<SimpleTablesMultipleData>())
{
    await db.Tbl_DATETIMEModify(dataM);
}
using (var db = await startDatabase.GetNewContext<SimpleTablesMultipleData>())
{
    var verify = await db.Tbl_DATETIMEGetSingle(dataM.ID);
    verify.Should().NotBeNull();
    verify!.DataColumn!.Should().NotBeNull();
    verify!.DataColumn.Should().Be(dataM.DataColumn);

}

And the error is

Xunit.Sdk.XunitException : Expected verify!.DataColumn to be <2023-12-13 21:45:39.6089917>, but found <2023-12-13 21:45:39.608991>.

You can see the Ok tests at https://ignatandrei.github.io/TestEFCoreDatabaseProviders/

And the problems at https://ignatandrei.github.io/TestEFCoreDatabaseProviders/problems.html