This discussion is archived
8 Replies Latest reply: Nov 21, 2012 2:53 PM by 703094 RSS

boolean types with entity framework code first and oracle provider

975273 Newbie
Currently Being Moderated
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
  • 1. Re: boolean types with entity framework code first and oracle provider
    Tridus Journeyer
    Currently Being Moderated
    Code First is not supported by Oracle. So that's your first problem. To use the Oracle client's EF support you'll need to create an EDMX model. I tend to do that by creating an empty one and telling it to load tables from the database. In that case using a NUMBER(1,0) as a Boolean does work.
  • 2. Re: boolean types with entity framework code first and oracle provider
    703094 Newbie
    Currently Being Moderated
    That's about the most miserable answer I can imagine.

    Code-first as a whole is not supported, and that makes sense as it involves a LOT of functionality, predominantly deriving, instantiating, and populating DB schema on the fly.

    In contrast, they offer, document, and support "model first" approach which means one builds a magical EDMX file that is used to build the Oracle Schema. The mapping of bool to NUMBER(1,0) is possible here (i.e. the custom mappings in *.config*), which means the OracleEFProvider can accomplish it... and thus means it is possible programmatically. the problem is no one is helping to enlighten us, but returning "i don't know, so it must be because it can't work"

    http://stackoverflow.com/questions/13480021/using-existing-c-sharp-model-classes-to-build-an-entity-diagram-edmx

    that example shows how someone can programmatically create the EDMX file by parsing their DbContext derived object. The problem is when the writer tries to parse the C# and thus map the boolean value, it chokes... what we are trying it understand is how to tell an EDMX writer about the custom mappings normally described in the .config file(s).

    one.beat.consumer
  • 3. Re: boolean types with entity framework code first and oracle provider
    975273 Newbie
    Currently Being Moderated
    Thanks for the answer!

    That opens an interesting way to acomplish the task that i didnt think about :)

    I will try first to walk around the boolean problem and if i cant, probably will try that thing, programmatically creating the edmx schema
  • 4. Re: boolean types with entity framework code first and oracle provider
    Tridus Journeyer
    Currently Being Moderated
    user10937155 wrote:
    That's about the most miserable answer I can imagine.

    Code-first as a whole is not supported, and that makes sense as it involves a LOT of functionality, predominantly deriving, instantiating, and populating DB schema on the fly.

    In contrast, they offer, document, and support "model first" approach which means one builds a magical EDMX file that is used to build the Oracle Schema. The mapping of bool to NUMBER(1,0) is possible here (i.e. the custom mappings in *.config*), which means the OracleEFProvider can accomplish it... and thus means it is possible programmatically. the problem is no one is helping to enlighten us, but returning "i don't know, so it must be because it can't work"
    That seems unnecessarily harsh, I'm just a guy trying to help out. :(
  • 5. Re: boolean types with entity framework code first and oracle provider
    703094 Newbie
    Currently Being Moderated
    Tridus, can you show us an example of how the EDMX approach actually does the mapping from bool to number? Once I see a working example I can probably deduce how to replicate the function via code.
  • 6. Re: boolean types with entity framework code first and oracle provider
    703094 Newbie
    Currently Being Moderated
    too harsh. i was just furious after two days of digging around and finding only cop-out answers from everyone. i apologize for offending. i'm a baby sometimes.
  • 7. Re: boolean types with entity framework code first and oracle provider
    Tridus Journeyer
    Currently Being Moderated
    It's cool. :)

    Sure. I can upload an EDMX file that works against my database if you want to look at it. The relevant parts are probably much like you'd expect. I've removed some columns from this version to make it easier to read.

    From the SSDL:
            <EntityType Name="PARAMETER">
              <Key>
                <PropertyRef Name="PARAMETER_CD" />
              </Key>
              <Property Name="PARAMETER_CD" Type="number" Nullable="false" Precision="4" />
              <Property Name="PARAMETER_ACTIVE_INDR" Type="number" Nullable="false" Precision="1" />
            </EntityType>
    From the CSDL:
     <EntityType Name="PARAMETER">
              <Key>
                <PropertyRef Name="PARAMETER_CD" />
              </Key>
              <Property Type="Int16" Name="PARAMETER_CD" Nullable="false" />
              <Property Type="Boolean" Name="PARAMETER_ACTIVE_INDR" Nullable="false" />
            </EntityType>
    From the C-S mapping:
    <EntitySetMapping Name="PARAMETER">
                <EntityTypeMapping TypeName="Model.PARAMETER">
                  <MappingFragment StoreEntitySet="PARAMETER">
                    <ScalarProperty Name="PARAMETER_ACTIVE_INDR" ColumnName="PARAMETER_ACTIVE_INDR" />
                    <ScalarProperty Name="PARAMETER_CD" ColumnName="PARAMETER_CD" />
                  </MappingFragment>
                </EntityTypeMapping>
              </EntitySetMapping>
    Also worth noting that the EDMX mapper probably won't set the provider and token correctly:
       
    <Schema Namespace="Model.Store" Alias="Self" Provider="Oracle.DataAccess.Client" ProviderManifestToken="11.2" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
    Full version is available at http://www.hiredgoons.ca/DataImport.edmx . This is an early development one so I make no apologies for not renaming columns to something readable. ;) It does work correctly as I've had it in testing (taking data from a 170MB excel file and putting it into Oracle using the model). It was created using DB First.

    When you have it load at runtime, you need the application configuration settings to map bool to number(1). If that's in app.config or web.config it'll pick it up. Probably worth noting that if you don't have the configuration setting and you load this, the model doesn't validate correctly. As soon as you try to create a DbContext from it, you get an immediate exception that number(1) doesn't map to boolean.

    Edited by: Tridus on Nov 21, 2012 3:37 PM
  • 8. Re: boolean types with entity framework code first and oracle provider
    703094 Newbie
    Currently Being Moderated
    Thank you Tridus. I have a code-first approach already happening - a full set of annotated models that need to be preserved, along with a fully built DbContext. I've come pretty far in bridging the code-first and model-first approaches. It's tricky because Oracle does use code-first behind the scenes in some places but not others... here's what I've done to preserve my code-first work and build my Oracle back end.

    0. Have my DBA grant me a schema and password.

    2. Use the EdmxWriter spit out the EDMX file, only possible because Oracle does in fact use some code-first function (OracleEFProvider)
    -- the writer parses my classes as well as any annotation attributes, as well as fluent-API mappings.
    -- the bad news is that the bool fails as we've been discussing, and even though it acknowledges all my other C# code, it will not honor the programatic type mapping .HasColumnType("NUMBER")

    3. Comment the bools out my model classes (this is a bitch because much code is already written using these fields, but it is a one time deal) and this time the EdmxWriter succeeds flawlessly. Awesome!

    3. Add the EDMX file to my project and open it up.
    -- Add the bools to my EDMX models
    -- Set the script generation properties according to Oracle's instructions (ex. "Generate Oracle Via T4 (TPT)" and "SSDLToOracle")
    -- Customize a few last properties (unnecessary but I turn on pluralization, lazy loading, validate, and rebuild model on save, etc.)

    4. Generate the SQL scripts and run them against my oracle schema.
    -- Add the standard sequence and trigger database stuff to ensure my entity IDs are auto incremented on insertion.
    -- The mappings from bool to NUMBER(1,0) are already made without any alterations to app/web.config at this point.

    5. Uncomment the bools!

    6. Run the application and insert a record
    -- Here is where the error about incompatible mappings arises on SaveChanges(). Altering the app/web.config and everything works smoothly.


    So everything is groovy really, but it is hard to maintain and a pain in the ass to comment out implemented code. Very kluge. So what I was/am looking for is the magic behind Oracle's app/web.config settings...

    A. What in the world is devouring the "edmmapping number(1,0)" value... and when in the JIT/run-time is it occuring?

    If that is solvable, and it does not require manipulation of "internal" classes, then it could likely be done within "OnModelCreating" and remove the need for the EDMX bullshit in the first place... i say this because again, the OracleEFProvider doing the EDMX parsing works like a charm except for these custom config mappings.

    one.beat.consumer

    Edited by: user10937155 on Nov 21, 2012 2:53 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points