boolean types with entity framework code first and oracle provider

Question

Working in a application that has to work in oracle and sqlServer. In SqlServer we have used "Bit" to store booleans and in oracle number(1).

We have created a code first model were we find few bool properties mapped to database fields.

for example:

public partial class ZPruebaBooleano
{
    public int Numero { get; set; }

    public bool Booleano { get; set; }
}

Mapped as:

    this.ToTable("ZPRUEBABOOLEANO", schema);

    this.HasKey<int>(t => t.Numero);
    this.Property(t => t.Numero).HasColumnName("NUMERO");

    this.Property(t => t.Booleano).HasColumnName("BOOLEANO");

Also, sw added to the application configuration file (for simplicity we have test it without the use of any additional dll):

  <oracle.dataaccess.client>
    <settings>
      <add name="bool" value="edmmapping number(1,0)" />
      <add name="byte" value="edmmapping number(3,0)" />
      <add name="int16" value="edmmapping number(4,0)" />
      <add name="int32" value="edmmapping number(9,0)" />
      <add name="int64" value="edmmapping number(18,0)" />
    </settings>
  </oracle.dataaccess.client>

I am confident that the provider factory read it when creating the model, because it provokes an exception if i add an invalid entry in any of the type names mapped in the file.

As i have find in other forums, this should work. But now i am not sure if should work always, including code first.

I have this exception when i excecute the code (translated from spanish, sorry for any mistake):

Type 'Edm.Boolean[Nullable=False,DefaultValue=]' from 'Booleano' to type'PruebaBooleanos.ZPruebaBooleano' is not compatyble with 'OracleEFProvider.number[Nullable=False,DefaultValue=,Precision=38,Scale=0]'

Its funy that says Precision=38. The database is Number(1), i am sure about that, in fact this is the script:

CREATE TABLE ZPRUEBABOOLEANO
(
  NUMERO    NUMBER(15),
  BOOLEANO  NUMBER(1)
)

Also, i find it interesting that i was having this mistake even when i was not informing the table i was maping, like if the oracle provider didnt even check if the column was number(anything). Right now is well maped, table and schema.

I have test to create an integer property wrapped by the boolean one, as we can see in this code.

namespace PruebaBooleanos
{
    public partial class ZPruebaBooleano
    {
        public int Numero { get; set; }

        public bool Booleano
        {
            get
            {
                return iBooleano == 1 ? true : false;
            }
            set
            {
                iBooleano = value ? 1 : 0;
            }
        }
        public int iBooleano { get; set; }
    }
}

in this last case i ignored the boolean and mapped the integer.

That worked ok in oracle. But added 2 problems:

It fails in SqlServer, because in sql server is bit
Cant query the entity model using the boolean property as it is not maped to Database.

I could change the bit field in SqlServer to Number.

But i would like to keep bit in sqlserver and number(1) in oracle.

Anyone had a similar problem?

I am open to any ideas, thanks !!!

libo

Gustavo Rubio · Accepted Answer · 2013-09-18 03:33:56Z

I'm pretty sure this answer will be useless for you by now to say the least, judging by the posting date, however many, like me, may find this useful.

Oracle data providers for .Net (ODP/ODAC) DO NO SUPPORT EF Code First
The mapping settings on the .config file will not be taken into account for Code First, that is, if you add the <add name="bool" value="edmmapping number(1,0)" /> it'll only work when you have an EDMX file and you use either model-first or db-first
Not all hope is lost, there is a "work around" for your case (and mine, and many others) to actually use your POCO classes with booleans in both SQL Server and Oracle (and probably more db engines)

So the solution for me was pretty simple, I spent quite a bit of time trying to accomplish the same thing to you and I just moved along when someone at Oracle on the forums (an employee it seems) told me that it was not supported, period.

So what you want to do is this:

Have an integer column in SQL Server that will store the booleans
Have a number(1,0) column on Oracle for the same purpose
Have two properties in your model, one that is actually mapped to the field in the DB and one that is not mapped but that casts your mapped value to a boolean

So do this in your code:

[Table("SomeTable")]
public class MyModel  {

    [Column("SOME_BOOLEAN")]
    public int SomeBooleanValue { get; set; }

    [NotMapped]
    public bool SomeBoolean {
        get {
            return Convert.ToBoolean(SomeBooleanValue);
        }
        set {
            SomeBooleanValue = Convert.ToInt32(value);
        }
    }
}

And then you just access SomeBoolean in your code which will reference actually an int which can be mapped to both databases.

I also use this approach when mapping Enum constant values.

Hey, Stackoverlfow almighties, give me power to upvote this answer a million times. Fantastic solution with clean hands.

Collectives™ on Stack Overflow

boolean types with entity framework code first and oracle provider

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related