Problems with Entity Framework and Oracle sequence ID

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Posts: 2
Joined: Thu 18 Sep 2008 07:26

Problems with Entity Framework and Oracle sequence ID

Post by VfrJm » Thu 18 Sep 2008 14:47

The following applies to my problem:
OraDirect.NET ver.
Oracle 10
My EDM is exposed to client via ADO.NET Data Services. Reading from EDM works well; also INSERT when specifying an ID (for MyTableID below) on the client, works. But we need to adhere to our database standards which uses a trigger to assign sequence value to primary keys.

sample code:
MyObjectType newObject = new MyObjectType();
IEdmDataService myContext = new EdmDataService();

newObject.SomeProps = .....
myContext.AddObject("MyObjectType", newObject);

.SaveChanges throws:
System.Data.Services.Client.DataServiceRequestException: An error occurred while processing this request. ---> System.Data.Services.Client.DataServiceClientException: System.InvalidOperationException: The serialized resource has a null value in key member 'MyTableID'. Null values are not supported in key members.

I HAVE set (in EDM model .edmx SSDL section) the following

...but still no success; I get the same ex for alle possible vals of StoreGeneratedPattern.

-Is it a problem that my ID is varchar2 and not an integer type? (Nope not...)
-To MS; why do I have to edit the EDM in XML editor to set props like StoreGeneratedPattern....? :x

any ideas...?

Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 19 Sep 2008 09:08

OraDirect .NET supports Entity Framework and doesn't contain any special ADO.NET Data Services support, because they work independently over EF and don't need any additional support. Therefore, if you experience ADO.NET Data Services problems (but there are no these problems when you use our provider directly with Entity Framework), probably, this is a service bug or you are using it incorrectly. When the error arises, please try to make a small test Windows Application for the same model.
As for the MS EDM Designer functionality, it is limited in the version 1 - there are not actual MS SQL Server capabilities for users, because the StoreGeneratedPattern="Identity" attribute is set automatically in EDM Wizard for identity columns.

Posts: 2
Joined: Thu 18 Sep 2008 07:26

Found a soultion

Post by VfrJm » Fri 19 Sep 2008 10:58

Turns out if I DO set a dummy value for MyTableId in the client code, it works out the way I want. I guess what happens then is something like this:
I set a dummy value for the ID of the entity to be inserted on client side. When this entity arrives to the EDM the EntityKey "constraint" will be happy with its NOT NULL requirement bacause of the provided dummy value. However, in the SSDL content section of the .edmx we set StoreGeneratedPattern="Identity" on the property. This throws away the dummy value provided from the client and sends NULL further to Oracle. At database level my trigger recognizes the NULL and thus fetches a new value from the sequence and assigns it to the ID filed in the table. Voila! 8)

Happy for now, but I feel it's not the way it's supposed to be done.

Posts: 13
Joined: Fri 12 Sep 2008 14:47

Post by jpl » Sat 20 Sep 2008 09:11

Ok, but what if you want to get the new ID value at the same time you create the new item ? Let us imagine you create many new items in memory, and then call SaveChanges(), you'll have to request the database once again to get the IDs of the inserted items. Now, if you declare a function at the object context level that returns a new sequence ID, you can have the new ID in memory but this means one database call for each new entity inserted in memory. I don't know what is the best... may be I would prefer the latter solution wich lets the object context in a consistent state.

Post Reply