Nested Tables support

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
wcedeno
Posts: 2
Joined: Wed 18 Nov 2009 11:06

Nested Tables support

Post by wcedeno » Mon 25 Jul 2011 11:30

Hi There,

I was unable to find information about EF support for Oracle nested tables in this or dotconnect for oracle forum. I have a schema contaning a few tables with nested table columns.

Is there any support for Oracle nested tables in EF? If not, what is the recommended workaround for having everything else in EF and dealing with nested tables separately. We could convert the nested tables into normal tables, but wanted to avoid schema changes.

Thanks,
Walter

Shalex
Site Admin
Posts: 8240
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 27 Jul 2011 14:48

Please refer to http://www.devart.com/dotconnect/oracle ... html#limit: "User-defined types, including Oracle objects, array types and nested tables are not supported." It is better to "convert the nested tables into normal tables". Entity Framework does not support object types of any form.

There are possible workarounds to avoid schema changes:

1. (not optimal) Create a view for your table expanding nested table as usual columns. Add this view to your model. Set correctly Entity Key that has to include PK of the main table and the column that uniquely identifies a record in the nested table not to lose records while reading (you will get all data in this way).
It is necessary to create 3 stored procedures for the insert|update|delete operations and map these SPs to the corresponding operations of your entity.

2. (read only) Add your table into the model - you will get an entity. Suppose this entity has the ID column which is PK. Exclude the column that corresponds to the nested table from the model. Work with this entity in a usual way.
Create stored procedure/function that returns (by ID) the content of the nested table for the particular record as a cursor. Import the stored procedure into the model to get a method in the model (Entity Developer can build ComplexType basing on stored procedure's result set and make method return collection of ComplexTypes). Use this method when you need to read data from the nested table.

wcedeno
Posts: 2
Joined: Wed 18 Nov 2009 11:06

Post by wcedeno » Wed 27 Jul 2011 15:15

Thanks for the info. We will evaluate the options and decide which is best for our environment.

Post Reply