Number mapping problems between Oracle and MSSQL

brunger · Post by **brunger** » Fri 23 Oct 2009 15:14

Hi,

I'm trying to use Entity Framework to replace our current ORM, but I'm stuck when it comes to supporting MSSQL and Oracle number types.

Microsofts MSSQL provider will map decimal SQL types to decimal .Net types.

The Oracle NUMBER type is getting mapped to double. This makes the use of Entity Framework for multiple database types impossible.

Am I missing something though, is it possible to change the mapping so my objects can use .Net decimal types on the Entity objects and map these to NUMBER types on the Oracle db?

The intention is that I will load up the correct StorageModel at run time dependant on the connection string to use either a MSSQL model or an Oracle model, but if they can't be made to map to a common .Net type, this makes it plain impossible.

Regards,

Colin

AndreyR · Post by **AndreyR** » Mon 26 Oct 2009 12:01

The detailed information concerning default Oracle NUMBER mapping is available in this post:
http://www.devart.com/forums/viewtopic.php?p=48088
There also was a problem with mapping NUMBER parameters of Oracle stored procedures to double.
This problem is fixed in the upcoming Beta build of Entity Developer.
Anyway, you have an opportunity to manually change the type of the column or parameter
in the design time using Entity Developer.

brunger · Post by **brunger** » Mon 26 Oct 2009 14:11

Hi,
The problem I have is that I need to make the mappings work for me. We have a large code base already in existance in which our current ORM maps Oracle NUMBERS, of almost all types, to .Net decimal values. This means we need a replacement ORM to be flexible enough to match this.

This means I can't change the column type, and I can't change the class property type. Changing the mappings in the edmx file to match my needs causes validation errors saying the NUMBER's with precision (8,0) can't map to decimal.

Is there a way round this?

Regards,

Colin

AndreyR · Post by **AndreyR** » Tue 27 Oct 2009 13:56

Please make sure that you have changed the store type to decimal in the SSDL part of the model also.
You can use either Entity Developer or XML Editor, MS Entity Designer does not offer editable storage model.
If you change both types to decimal everything should work ok.

abhinay_agrawal · Post by **abhinay_agrawal** » Wed 28 Oct 2009 13:44

I am also having same problem for number format in oracle so I tried with changing number(7) to number(37) and it's given me decimal which one is working fine.

Thanks,
Abhinay.

brunger · Post by **brunger** » Wed 28 Oct 2009 15:04

Thanks,

I've got the numbers problem sovled now, and also have the very annoying upper case to lower case table and column name mapping problem sorted as well.

Manually changing and maintaining multiple ssdl files, and msl files (as the case change appears in this as well) has proved a nightmare as there is no tool which can syncronise these with the database in any easy manner for both MSSQL and Oracle.

My solution has been to just stick with an MSSQL edmx file created by Visual Studio 2008, then at runtime I'm preprocessing the ssdl and msl resources to map types (varchar -> varchar2, datetime -> date) and convert EntitySet and Property names to upper case. This preprocessing is triggered by the connection strings provider type.

I then manually complie the three parts into a MetadataWorkspace and create my connection from it.

This also allows me to sort the other problem of EntitySet Schema being hard coded in the ssdl file, since this varies with the user that is logging in.

I've also added a bit of caching as well so we can connect to multiple platform types at the same time without going through this mapping procress too many times.

This all means I only ever maintain a single edmx file, making life far easier.

Colin

AndreyR · Post by **AndreyR** » Thu 29 Oct 2009 10:19

Thank you for sharing your knowledge.
However, I have some comments.
First, you could use the Naming Rules mechanism of Entity Developer to generate the Oracle model
in accordance with the SQL Server naming style.
The Schema attribute also can be cleared out using Entity Developer design time store model editing.