How to map Oracle database NUMBER to c# bool type in .NET Core?

Question

I have Oracle database table with column of type NUMBER. Due to legacy reasons this column represents boolean such that value 0 represents false and value -1 represents true.

I need to map this table to the C# class and thus map this column to bool property with the specified mapping values. I am using linq2db as ORM in a .NET Core applications (console and asp.net). Is there a way to tell Oracle Managed Client (Oracle.ManagedDataAccess.Core) to automatically perform this mapping for all database queries I perform from my code?

linq2db 3.0.0-rc1 scheduled for release this week also adds per-column conversion configuration support using value converters github.com/linq2db/linq2db/wiki/… — user1641529
– user1641529, Commented Jun 17, 2020 at 10:23

user2457870 · Accepted Answer · 2019-03-21 09:10:33Z

2

If you're using Fluent API to configure your models, from EF Core 2.1 onwards, you can use Value Conversions.

At this time there isn't a Built-in converter for NumberToBool but it can be written this way:

var converter = new ValueConverter<bool, int>(
    v => v ? -1 : 0,
    v => (v == -1));

entity.Property(e => e.IsNew)
    .HasColumnName("ISNEW")
    .HasConversion(converter);

answered Mar 21, 2019 at 9:10

user2457870

7102 gold badges10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1641529 · Accepted Answer · 2018-12-28 16:54:04Z

1

You need to configure mappings between System.boolean and "number" types in your mapping schema

// converter to query parameter
ms.SetConverter<bool, DataParameter>(val => new DataParameter { Value = <convert to number> });
// converter to query literal
ms.SetValueToSqlConverter(typeof(bool), (sb,tp,v) =>
{
    if (v is bool val) sb.Append(<number literal>);
    else               sb.Append("NULL");
});
// converter from db value to boolean
ms.SetConverter<int, bool>(val => val != 0);

Also probably you want to configure it only for columns, marked with "number" DbType, so use configuration overloads, that take dbtype as from/to type parameter.

answered Dec 28, 2018 at 16:54

user1641529

Comments

jdweng · Accepted Answer · 2018-12-28 12:18:02Z

1

The model in Net library has classes. You would need to edit or override the class and add code below

    public class MyTable
    {
        private Boolean myBool { get; set; }

        public int OracleNumber
        {
            get { return (myBool == false) ? 0 : -1; }
            set { myBool = (value == -1) ? myBool = true : myBool = false; }
        }
    }

answered Dec 28, 2018 at 12:18

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to map Oracle database NUMBER to c# bool type in .NET Core?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related