6 Replies Latest reply on Dec 26, 2019 3:01 PM by Mark Williams

    Using NUMBER(5) in db with EF Core 2.2

    Nirav.Bhatt

      Hi Team,

       

      I am using .Net Core 2.2 and EF Core 2.2.

       

      I have ColumnX in database with type NUMBER(5).

       

      When I scaffold the table it will generate C# datatype as short.

       

      As Number(5) database can contain value 999999. However C# type short can have max value 32767.

       

      Now when the ColumnX has a value greater than 32767, the application raises an exception the value expected is Int16 but the actual value is Int16.

       

      To resolve this issue changed the datatype of ColumnX from short to int.

      However, I don't want my datatype to change to NUMBER(10) on migration, in fluent API, I added below code.

      entity.Property<int>(e => e.ColumnX)

                      .HasColumnName("ColumnX")

                      .HasColumnType("NUMBER(5)");

       

      When I do that I receive below exception.

       

      System.InvalidOperationException: An exception occurred while reading a database value for property 'Table.ColumnX'. The expected type was 'System.Int32' but the actual value was of type 'System.Int32'. ---> System.InvalidCastException: Specified cast is not valid.

         at Oracle.ManagedDataAccess.Client.OracleDataReader.GetInt16(Int32 i)

         at lambda_method(Closure , DbDataReader )

         --- End of inner exception stack trace ---

         at Microsoft.EntityFrameworkCore.Storage.TypedRelationalValueBufferFactoryFactory.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)

         at lambda_method(Closure , DbDataReader )

         at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)

         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)

         at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)

         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()

         at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()

         at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext() System.InvalidOperationException: An exception occurred while reading a database value for property 'Table.ColumnX'. The expected type was 'System.Int32' but the actual value was of type 'System.Int32'. ---> System.InvalidCastException: Specified cast is not valid.

         at Oracle.ManagedDataAccess.Client.OracleDataReader.GetInt16(Int32 i)

         at lambda_method(Closure , DbDataReader )

         --- End of inner exception stack trace ---

         at Microsoft.EntityFrameworkCore.Storage.TypedRelationalValueBufferFactoryFactory.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)

         at lambda_method(Closure , DbDataReader )

         at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)

         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)

         at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)

         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()

         at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()

         at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

       

       

      The only way I can make it work is if omit datatype.

      entity.Property<int>(e => e.ColumnX)

                      .HasColumnName("ColumnX");

       

      To me, it seems like an issue.

      Let me know your thoughts.

        • 1. Re: Using NUMBER(5) in db with EF Core 2.2
          Nirav.Bhatt

          Alex Keh - Product Manager-Oracle did you get chance to look at this thread?

          • 2. Re: Using NUMBER(5) in db with EF Core 2.2
            Alex Keh - Product Manager-Oracle

            Is the data type being modified to something else besides NUMBER(5)? If not, then no the "HasColumnType("NUMBER(5)")" setting is not needed anyway.

            • 3. Re: Using NUMBER(5) in db with EF Core 2.2
              Nirav.Bhatt

              Number(5) database can contain value 99999. However C# type short can have max value 32767.

               

              Now when the ColumnX has a value greater than 32767, the application raises an exception the value expected is Int16 but the actual value is Int32.

               

              So, when we want application to accept value 99999, we should have the datatype as INT and in database the type should be NUMBER(5).

               

              If we do not specify HasColumnType("NUMBER(5)") then system and we run a migration, it will create column with "NUMBER(10)" instead of NIMBER(5).

              • 4. Re: Using NUMBER(5) in db with EF Core 2.2
                Alex Keh - Product Manager-Oracle

                I was able to have Migration create a NUMBER(5) in the DB using annotations:

                            [Column(TypeName = "NUMBER(5)")]

                            public int MyNum { get; set; }

                 

                Or using Fluent API.

                            protected override void OnModelCreating(ModelBuilder modelBuilder)

                            {

                                modelBuilder.Entity<Blog>().Property(t => t.MyNum).HasColumnType("NUMBER(5)");

                            }


                Can you use this Fluent API?

                • 5. Re: Using NUMBER(5) in db with EF Core 2.2
                  Mark Williams

                  Apologies for jumping in here — especially as I don't "do" EF but what seems to be the issue here is that the Oracle Core and EF Core providers don't provide a mechanism for changing the data type mapping. I believe that the Oracle .NET and EF 6 providers had such a mechanism. I may not be correctly remembering that and, in any case, it doesn't really help the situation here.

                   

                  While it is possible to use either annotations or the fluent api to force the database column to be of a certain type (as has been shown in the thread already) the main issue is that there appears to be no way to get the Oracle providers to treat a NUMBER(5) column as anything other than a short/Int16. Indeed, reviewing the Migrations Data Type Mapping section in the documentation we can see that short/Int16 does map to NUMBER(5) database column. The same holds true for Scaffolding Or Reverse Engineering Data Type Mapping.

                   

                  As an example, let's say I have the following very simple class:

                   

                  public class Item
                  {
                    public int ItemId { get; set; }
                    public string ItemDesc { get; set; }
                    public int ItemLoc { get; set; }
                  }
                  

                   

                  Let's also say I override the OnModelCreating method to contain:

                   

                  modelBuilder.Entity<Item>().Property(t => t.ItemLoc).HasColumnType("NUMBER(5)");
                  

                   

                  Now if I add a migration we should see the following in the CreateTable override:

                   

                  ItemLoc = table.Column<short>(type: "NUMBER(5)", nullable: false)
                  

                   

                  Notice that even though the property is an int/Int32 due to the NUMBER(5) you see "short" as part of the add column operation (i.e., table.Column<short>).

                   

                  Now when a value is fetched from the column the Oracle Core providers will attempt to use OracleDataReader.GetInt16() and this is a problem when the value is greater than 32,767 as the OP has shown. Again from the above referenced documentation NUMBER(5) maps to short/Int16 and there doesn't appear to be a way to override this when the database column is, in fact, NUMBER(5). In other words, 99,999 is perfectly valid in a NUMBER(5) column but will cause the exception when used as input to the OracleDataReader.GetInt16() method.

                   

                  Continuing with the simple class from above let's say I do the following:

                   

                  using (var db = new ItemContext())
                  {
                    var item1 = new Item() { ItemDesc = "Item Description 1", ItemLoc = 11111 };
                    var item2 = new Item() { ItemDesc = "Item Description 2", ItemLoc = 99999 };
                  
                    db.Items.Add(item1);
                    db.Items.Add(item2);
                  
                    db.SaveChanges();
                  }
                  
                  using (var db = new ItemContext())
                  {
                    var items = db.Items;
                  
                    foreach (Item i in items)
                    {
                      Console.WriteLine("Item[{0}]: Description: {1}, Location: {2}", i.ItemId, i.ItemDesc, i.ItemLoc);
                    }
                  }
                  

                   

                  This will successfully insert the values into the table but when the test tries to fetch the ItemLoc=99999 the exception will be raised.

                   

                  So, unless there is a way to change the Oracle Core provider mapping of NUMBER(5) short/Int16 I don't know what can be done about this. Of course there may be something simple I don't know about in this regard because, as I say, I don't "do" EF.

                   

                  Regards,

                  Mark

                  • 6. Re: Using NUMBER(5) in db with EF Core 2.2
                    Nirav.Bhatt

                    Thanks a lot Mark, for sharing your view.

                     

                    You are correct, I am facing issue when I fetch the records. It occurs only when the value is more than 32767.

                    • 7. Re: Using NUMBER(5) in db with EF Core 2.2
                      Mark Williams

                      Hi Nirav,

                       

                      I've been thinking about this issue a bit and I've recently learned something that might be helpful.

                       

                      It seems that beginning in EF Core 2.1 a feature known as Value Conversions was introduced. This looks interesting as, according to the linked documentation, these allow property values to be converted when reading from or writing to the database. Exactly what we need here.

                       

                      So, using my previous Item entity example from earlier in this thread, I performed a quick test and the initial results look promising.

                       

                      Here's the modified OnModelCreating method to illustrate adding a simple (and possibly naïve) Value Conversion for the ItemLoc property:

                      protected override void OnModelCreating(ModelBuilder modelBuilder)
                      {
                        modelBuilder.Entity<Item>().Property(t => t.ItemLoc).HasColumnType("NUMBER(5)");
                        modelBuilder.Entity<Item>().Property(t => t.ItemLoc).HasConversion(
                          t => Convert.ToInt32(t),
                          t => Convert.ToInt32(t));
                      }
                      

                       

                      Generating a migration after doing this shows that the property which previously was treated as a C# short (and resulted in OracleDataReader.GetInt16 being called) is now treated as a C# int:

                      ItemLoc = table.Column<int>(type: "NUMBER(5)", nullable: false)
                      

                       

                      That is, table.Column<short> has become table.Column<int> and NUMBER(5) is still maintained which is desirable.

                       

                      Executing the simple example does not result in the previously seen exception and produces the expected results where a value greater than 32767 is correctly read from a NUMBER(5) column:

                      Item[1]: Description: Item Description 1, Location: 11111
                      Item[2]: Description: Item Description 2, Location: 99999
                      

                       

                      Perhaps the Value Conversions feature is something that you can take a look at for your specific implementation. It at least looks like a possible path forward.

                       

                      Regards,

                      Mark