2

I am using EF with an oracle database. One of our columns in the database has a data type of Number(1,0). This field stores data that translates to Int16 in C#. Entity Data Model (EDM) builder in visual studio maps this column as Bool default. This causing data type errors. What I want to do is configure the model generator to set number(1, 0) as a Int16 field all the time instead of bool.

How would I do this.

I also want to mention that when a fellow developer builds this model on his machine it will set it as Int16 (short). When I do it on my machine it does bool. We cant figure out why one of us see's different results then the other.

Thanks

danmanallen
  • 175
  • 3
  • 14

3 Answers3

2

Assuming you are using Oracle's provider (aka ODAC, ODP.NET, ODT)...

You can add a custom mapping section to the app.config files of both the assembly containing the EDMX (for design time) and the executable assembly (for run time) to change the default mappings.
See more details here.

E.g.

<oracle.dataaccess.client>
    <settings>
        <add name="int16" value="edmmapping number(1,0)" />
    </settings>
</oracle.dataaccess.client>

Take into account that the range of number(1,0) is -9..9 so it is not really suitable for int16 or even bytes (both have a larger range).

You can find a list of recommended mappings here.

Edit:

For the newer managed provider's settings see my answer here

Community
  • 1
  • 1
Danny Varod
  • 17,324
  • 5
  • 69
  • 111
  • 1
    I found this while searching. It didn't make any difference. I even read that the exceptions code block should go after the connection string block so I did that and it didn't work either. I cant confirm this but I think the newest version of EF doesn't look for these custom exceptions. There use to be a "Number Mappings" option in the advanced settings for the database connection setup. This is where you could set mappings but it is no longer an option. Something must have changed. Thank you for taking the time to investigate – danmanallen Jun 05 '12 at 20:46
  • What version of Oracle provdier are you using? Try asking in the Oracle forum. (They answer pretty quickly - the link I provided is to a question I asked there.) – Danny Varod Jun 05 '12 at 20:58
  • I tested this with EF 4.0. What version of EF are you using? – Danny Varod Jun 05 '12 at 21:07
  • @danmanallen I have tried almost everything now and I keep getting the error 2019 at runtime. It looks like my custom mappings are read properly at design time (my entities are generated according to my custom mappings) but they are not observed at runtime. Did you find any solution? – Raciel R. Oct 14 '16 at 18:44
  • @RacielR. for design time the mappings should be in the EDMX project's config file. For run time they should be in the main project's config file (Web project or Application project). Also, the mapping I gave was for the older unmanaged client, for the newer managed client the mappings are different. – Danny Varod Oct 21 '16 at 22:53
  • Why these are mapped to intX? Why not to Decimal which seems equivalent in C#? Decimal(X,0) – Hiren Desai Feb 20 '17 at 04:56
  • @HirenDesai Decimal is useful for storing financial amounts as it has an extremely high range (128bit). Using decimal where you don't need it results in larger storage, larger memory usage and more CPU operations (most processors today are only 64bit, some are only 32bit). For small enums often an 8bit unsigned integer (byte) is enough and for large IDs, a 64bit integer is usually enough and very often 16bit or 32bit will do. (continued...) – Danny Varod Feb 21 '17 at 12:50
  • @HirenDesai (...continuation) Decimal is equivalent to an unlimited number in Oracle. When you want to use native-compact CPU types (8bit, 16bit, 32bit, 64bit), then you want to store them in the minimum Oracle type for storing that variable - for details on which types to use and the formula for covertion see [my answer here](http://stackoverflow.com/a/8298684/38368) – Danny Varod Feb 21 '17 at 12:53
  • @DannyVarod I'm currently facing this issue in which number(5,0) is mapped to int16. Now number(5,0) can store 99999 in database which will throw error for int16 (32,767). So the range between 32768 to 99999 is from oracle is actually in compatible in .NET code. This turn out to be an issue at run-time which wouldn't be highlighted during development. – Hiren Desai Feb 22 '17 at 08:33
  • @DannyVarod (....continuation), if you compare oracle EF with Ms SQL equvivalent, the MS SQL version actually maps number(5,0) to Decimal(5,0) in C# and that's why I asked this question.... – Hiren Desai Feb 22 '17 at 08:34
  • @HirenDesai MSSQL actual has native types bit, tinyint, smallint, int, bigint, Oracle does not (at least not fully) - hence the need for this. – Danny Varod Feb 22 '17 at 18:21
  • @DannyVarod is there any way by which I configure edm mapping such that number(5,0) will be mapped to Decimal(5,0) instead of int16? – Hiren Desai Mar 02 '17 at 08:30
  • @HirenDesai Ask this in a separate question. Mention which type of Oracle provider you are using in the question. – Danny Varod Mar 02 '17 at 12:37
2

I have encounter a similar problem. (tried to map the number(1) from the default bool to int16)

Im using EF6 with VS2013 on oracle11 using ODP.net 12.1.24.

Eventually, I resolved it by adding the following section to my app.config:

<oracle.manageddataaccess.client>
  <version number="*">
    <edmMappings>
      <edmNumberMapping>
        <add NETType="int16" MinPrecision="1" MaxPrecision="4" DBType="Number"/>
      </edmNumberMapping>
    </edmMappings>
  </version>
</oracle.manageddataaccess.client>
Nir
  • 601
  • 7
  • 21
  • 1
    Do you have your edmx in a different project than your application? I'm not having any luck with this. I added it to both the project holding the edmx and my webapi project, which is my final app. – Raciel R. Oct 14 '16 at 18:58
  • I have added this part to the web.config in my WCF project (in the server side) – Nir Oct 25 '16 at 06:25
  • mmm... Looked at it again, and this section was added to the App.config (in the same project where your edmx file is located) – Nir Dec 01 '16 at 08:30
  • WARNING: Make sure you **DO NOT** have a section declaration like:
    anywhere in your app.config/web.config. Also make sure that the declaration is all *lower-case* (don't use camel-case there - it won't work).     – XDS Mar 08 '18 at 16:14
-1

According to this table:

http://docs.oracle.com/cd/E11882_01/win.112/e18754/featLINQ.htm

Nmber(1,0) in Oracle is Int16

However as per the above document: "You can configure a custom mapping in the .NET configuration file to override the default mapping for the Number(p,0) Oracle data type. So, for example, Number(1,0), which is mapped to Int16 by default, can be custom mapped to the .NET Bool or .NET Byte type."

Take a look at the document it shows how the config should look like.

Pawel
  • 31,342
  • 4
  • 73
  • 104
  • Most documentation for new versions of EF are showing that by default Number(1, 0) is in fact Bool. Number(4,0) default is Int16 I believe. – danmanallen Jun 05 '12 at 20:42
  • The correct mapping is bool. In the Oracle provider, it is not the default (only works with custom mappings). – Danny Varod Jun 05 '12 at 21:06