Supporting both Oracle & SQL Server in Entity Framework

I have generated an Entity Framework model on a system that can be deployed to either an Oracl开发者_C百科e or SQL Server database. I have managed to get the EF Model working with both DB's (I have a common CSDL, the Oracle SSDL and SQL Server SSDL). I am having a problem with integers. In SQL Server fields can be defined as an integer but in Oracle they are defined as number (10,0) in order to ensure the max integer can held.

EF generates an Int32 for the SQL Server model but Int64 for Oracle. I've tried changing the CSDL to make all these fields Int64, this works fine for Oracle but not for SQL Server. I've also changed the SQL Server ssdl to make the fields in question bigints.

I get the following error

The 'propName' property on 'table name' could not be set to a 'Int32' value. You must set this property to a non-null value of type 'Int64'

I would have thought that in int field on the SQL Server DB could be assigned to a bigint/Int64 as there is no chance of a loss of data.

Without changing the DB structure (which is outside of my control) has anybody and ideas as to how to get around this issue?

Thanks.

---

The correct Int32 mapping to Oracle would be: NUMBER(9,0).

---

This probably isn't the type of answer you're looking for, but if you use one of the POCO object generation templates you can get around this (available as an addon for EF 4.0, and is included in the 4.1 RC). That generates lightweight partial classes for your data.

Because they're partial classes, you could get around this by creating your own partial class to add to the one that has this problem. Make a new property (call it "PropName2" for the sake of this example, though thats obviously not a great name) that is an int. In the SQL Server version of the data layer, just have it directly get/set the normal propName property when someone uses the new one.

In the Oracle version, you can hide the Int64 stuff inside the new property, so that people using it just use the new property and get an Int32 no matter what.

I know that's not ideal since you'll need a version of the class for each database, but it would effectively hide the problem from the rest of your code.