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).
Have check other threads in this forum, but didnt find any that fix my problem, sorry if this may have been discused before.
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:
1. It fails in SqlServer, because in sql server is bit
2. 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