Forum Stats

  • 3,854,668 Users
  • 2,264,397 Discussions
  • 7,905,749 Comments

Discussions

EF code first migration does not map DateTimeOffset to TIMESTAMP WITH TIMEZONE

Piotr Lewandowski
Piotr Lewandowski Member Posts: 7 Green Ribbon
edited Jul 11, 2022 9:19AM in ODP.NET

According to the latest documentation for ODP.NET EF Core, a column of DateTimeOffset type should be mapped to "TIMESTAMP WITH TIMEZONE" Oracle data type.

However, when creating a migration script via "dotnet ef migrations add" command, the actual type of that column is NVARCHAR2(48). I managed to make the mapping to the expected type by changing the .net data type to DateTime and the column type name to "TIMESTAMP WITH TIMEZONE", but that doesn't seem right.

Example class before the change:

  [Table("ExampleTable", Schema = "MySchema")]
  public class ExampleTable
  {
    [Column("PK")]
    public int Pk { get; set; }
   
    [Column("CREATED_AT")]
    public DateTime CreatedAt { get; set; }
  }

DateTime changed to DateTimeOffset:

  [Table("ExampleTable", Schema = "MySchema")]
  public class ExampleTable
  {
    [Column("PK")]
    public int Pk { get; set; }
   
    [Column("CREATED_AT")]
    public DateTimeOffset CreatedAt { get; set; }
  }


Actual result after running the "dotnet ef migrations add test" command:

  public partial class test : Migration
  {
    protected override void Up(MigrationBuilder migrationBuilder)
    {
      migrationBuilder.AlterColumn<string>(
        name: "CREATED_AT",
        schema: "MySchema",
        table: "ExampleTable",
        type: "NVARCHAR2(48)",
        nullable: false,
        oldClrType: typeof(DateTime),
        oldType: "TIMESTAMP");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
      migrationBuilder.AlterColumn<DateTime>(
        name: "CREATED_AT",
        schema: "MySchema",
        table: "ExampleTable",
        type: "TIMESTAMP",
        nullable: false,
        oldClrType: typeof(string),
        oldType: "NVARCHAR2(48)");
    }
  }


If the documentation is right, what else should I do to make this mapping to be automatic (without specifying the type name)? Or it is a bug?

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,129 Employee

    It's probably a bug.

    I just tested this scenario with DB 21c, EFC 6 and the latest ODP.NET EF Core 21c on NuGet Gallery using a class with a property:

          public DateTimeOffset dto { get; set; }

    After undertaking a migration, the schema object created was a Timestamp TZ(7) data type. So, I was unable to reproduce the bug.

    What Oracle EF Core, EF Core, and DB versions are you using? Can you share a simple test case and the files generated in your Migration folder?

  • Piotr Lewandowski
    Piotr Lewandowski Member Posts: 7 Green Ribbon

    Oracle.EntityFrameworkCore 5.21.61

    Microsoft.EntityFrameworkCore 5.0.17

    Oracle 18c

  • Piotr Lewandowski
    Piotr Lewandowski Member Posts: 7 Green Ribbon

    I updated the question with the test case and the actual result.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,129 Employee

    That's interesting. When I run add-migration changing the data type from DateTime to DTO, I get TS(7) with TZ as expected. I used Oracle.EntityFrameworkCore 5.21.61 and Microsoft.EntityFrameworkCore 5.0.17.


    protected override void Up(MigrationBuilder migrationBuilder)

        {

          migrationBuilder.AlterColumn<DateTimeOffset>(

            name: "CREATED_AT",

            table: "Blogs",

            type: "TIMESTAMP(7) WITH TIME ZONE",

            nullable: false,

            oldClrType: typeof(DateTime),

            oldType: "TIMESTAMP(7)");

        }


        protected override void Down(MigrationBuilder migrationBuilder)

        {

          migrationBuilder.AlterColumn<DateTime>(

            name: "CREATED_AT",

            table: "Blogs",

            type: "TIMESTAMP(7)",

            nullable: false,

            oldClrType: typeof(DateTimeOffset),

            oldType: "TIMESTAMP(7) WITH TIME ZONE");

        }

      

    Can you turn on verbose tracing (-v) for your migrations add command to see what is going on at a more granular level that's different and why?

  • Piotr Lewandowski
    Piotr Lewandowski Member Posts: 7 Green Ribbon
    edited Jul 12, 2022 9:36AM

    Yes, I will.

    Though, there is one difference between our test cases. I declared a schema, while you didn't. That would be odd reason, but still possible?

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,129 Employee

    I added the following data annotation, but it didn't make a difference for data type conversion:

        [Table("BlogTable", Schema = "BLOG")]

  • Piotr Lewandowski
    Piotr Lewandowski Member Posts: 7 Green Ribbon

    Alex, I just discovered that another team has recently made some changes to our base code, which leads to a situation where some types are being mapped to types I have not expected. This mapping needs to be fixed on our side and this is definitely not a mistake on yours.

    I'm sory to confuse you and that you had to spend some time on it.

    Thanks for you help.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,129 Employee
    edited Jul 19, 2022 7:27PM

    No problem, Piotr. I'm glad you found a resolution.