Wrong Datetime a negative year (BC)

[pilif]

Hello,

We found a possible error when loading data from an Oracle database using EntityFramework Core. It happens on column of type DateTime in which the stored time value is BC.

If the field value is within the range defined in C # (that is, from 01/01/0001 AD to 12/31/9999 AD), it will read from the database without any problems.
However, if it is not in this range, in our case, it is a negative year (BC), the records are loaded incorrectly.

Let's say we have a table called "Products," this table has several columns and one is called "Issued" of the type DateTime (Nullable).

When we use a record-retrieval condition where "Issued" is null ("DbContext.ProductsTable.Where (item => item.Issued == null);" it returns all rows where "Issued" is null, and all rows where the date in the column is negative year (BC). When we use the generated SQL and try to run it in Oracle SQL Developer (or another database editor), it returns the expected records, ie those where the column is null.

A similar thing happens in the opposite case. When we use a record-retrieval condition where the "Issued" column is not equal to "null" ("DbContext.ProductsTable.Where (item => item.Issued != Null)" it returns all rows that are not null, but it does not return the rows where value of column "Issued" is BC. When we use a generated SQL query in a database editor, it returns the records according to the assumption, ie all the rows where the column is filled whether BC or AD.

We used EF6 before the transition to EF Core, and we handled this error with Try / Catch because when an attempt was made to retrieve the BC date there was new exception thrown. We logged this exception and we could work with it. Now with EF Core it is not possible, because the exception is not triggered, and even though there are some data in the database, we can not get them with EF Core and we can not work with them anyway.

This behavior is very unexpected, because in both the above mentioned cases, EF Core behaves differently than would be expected and returns data other than the data in the database.

P.S.: These faulty data are passed to the database from an outdated program where we are gradually solve these errors. Which will not be possible now, because our newer program will not even show us.

Thank you in advance for your reply

[Shalex]

Could you please modify the following code so that we can reproduce the issue in our environment?

Code: Select all

CREATE TABLE "f38078" (ID NUMBER(10, 0) PRIMARY KEY, ISSUED TIMESTAMP(6) WITH TIME ZONE);
INSERT INTO "f38078" VALUES(1, TO_TIMESTAMP('2003-01-01 2:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "f38078" VALUES(2, null);
INSERT INTO "f38078" VALUES(3, TO_TIMESTAMP('0100bc-01-01 2:00:00', 'YYYYbc-MM-DD HH24:MI:SS'));

    public partial class F38078 {
        public virtual long ID
        {
            get;
            set;
        }
        public virtual System.DateTimeOffset? ISSUED
        {
            get;
            set;
        }
    }
...
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseOracle(@"User Id=scott;Password=tiger;Server=oracle12c.datasoft.local;Direct=True;Sid=ORCLPDB.datasoft.local;");
        }
        
        private void F38078Mapping(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<F38078>().ToTable(@"f38078", @"SCOTT");
            modelBuilder.Entity<F38078>().Property<long>(x => x.ID).HasColumnName(@"ID").HasColumnType(@"NUMBER").IsRequired().ValueGeneratedNever();
            modelBuilder.Entity<F38078>().Property<System.DateTimeOffset?>(x => x.ISSUED).HasColumnName(@"ISSUED").HasColumnType(@"TIMESTAMP WITH TIME ZONE").ValueGeneratedNever();
            modelBuilder.Entity<F38078>().HasKey(@"ID");
        }
...

            using (var context = new SCOTTModel())
            {
                var nullCount = context.F38078S.Where(item => item.ISSUED == null).Count();
                Console.WriteLine("nulls count = {0}", nullCount); // nulls count = 1
                Console.WriteLine();

                var notNullCount = context.F38078S.Where(item => item.ISSUED != null).Count();
                Console.WriteLine("not nulls count = {0}", notNullCount); // not nulls count = 2
                Console.WriteLine();

                var result = context.F38078S.Where(item => item.ISSUED != null).ToList(); // System.ArgumentOutOfRangeException: 'Year, Month, and Day parameters describe an un-representable DateTime.'
                foreach (var item in result)
                {
                    Console.WriteLine(item.ID + "    " + item.ISSUED);
                }
                Console.WriteLine();
            }