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
Nested Tables support
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.
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.