Oracle - how to pull in associated table data?

[bgoldstein14]

I'm trying to do something very simple with EF6 against Oracle which is to pull in referential data based on a pull from the primary table. The reference is such that only a single record should be available for the given foreign key.

Below works fine:

Code: Select all

CRModel.CREntities e = new CRModel.CREntities();
List<CRModel.TS_ORDER> a = e.TS_ORDER.Where(o => o.ORDER_ID == 391667056).FirstOrDefault().ToList();

However, what I want is the security that is connected to a particular order and the following does not work (where a single CSM_SECURITY record should be connected to the discovered TS_ORDER record):

Code: Select all

CRModel.CREntities e = new CRModel.CREntities();
var a = e.TS_ORDER.Where(o => o.ORDER_ID == 391667056).FirstOrDefault().Select(o => o.CSM_SECURITY).ToList(); //There is NO CSM_SECURITY property or other entity-like property to make this happen even though the model has the referential information set up.

I have other selections around the CSM_SECURITY reference objects, but not the entity itself. If I have a single order object I should be able to get to my CSM_SECURITY object too as it works with Oracle's version of the EF6 driver.

[Shalex]

it works with Oracle's version of the EF6 driver

You mean ODP.NET, don't you?

Code: Select all

CRModel.CREntities e = new CRModel.CREntities();
var a = e.TS_ORDER.Where(o => o.ORDER_ID == 391667056).FirstOrDefault().Select(o => o.CSM_SECURITY).ToList(); //There is NO CSM_SECURITY property or other entity-like property to make this happen even though the model has the referential information set up.

Please send us a small test project with the corresponding DDL/DML script so that we can reproduce the case when it works with ODP.NET, and doesn't work with dotConnect for Oracle.

[bgoldstein14]

Yes, I mean ODP.NET. Basically, I can't get access to the associated tables that should be accessible via the referential constraints that are set up.

[Shalex]

Could you please send us a small test project with the corresponding DDL/DML script so that we can reproduce the case when it works with ODP.NET, and doesn't work with dotConnect for Oracle?

[bgoldstein14]

I sent an example.

[Shalex]

We didn't receive any test project from the email specified in your forum profile.
1. Did you obtain a mail delivery failure?
2. What is the size of your attachment? If it is 2MB max, send via contact form. If it is bigger than 2MB, use our ftp server:
ftp://ftp.devart.com/
user: anonymous
password: your email
3. Specify your email used when submitting request.

[bgoldstein14]

I just resent it.

[bgoldstein14]

Please also note that I have issues trying to set up the model from a database. My default schema (user) is not the one where I need to pull in my entities from. Even though I do not select any entities from the default schema and choose entities from the desired one, the model creator ignores my request and instead tries to pull in every entity from the default schema. This forces me to delete all the entities and then update from database. This then presents a less than optimal wizard which no longer separates tables and views but rather groups them together in a much longer list, however, when I choose the desired entities they are brought into the model.

[Shalex]

I just resent it.

Thank you. We have asked for the additional information about the test project by email.

Please also note that I have issues trying to set up the model from a database. My default schema (user) is not the one where I need to pull in my entities from. Even though I do not select any entities from the default schema and choose entities from the desired one, the model creator ignores my request and instead tries to pull in every entity from the default schema.

We cannot reproduce the problem at the moment. Please run Entity Developer Create Model Wizard again. You are connecting to Oracle via ODP.NET, aren't you? Select the "Show all users" option on the "Select database objects" step of the wizard. Unselect the root node of your default schema (user) in the tree, then select the node of the desired schema, press "Next" and finish the wizard. The result model includes the objects from the desired schema only in our environment. Are you following exactly the same steps?

This forces me to delete all the entities and then update from database. This then presents a less than optimal wizard which no longer separates tables and views but rather groups them together in a much longer list, however, when I choose the desired entities they are brought into the model.

These are two different trees:
a) "create model" (respects only database structure)
b) "update model" which also takes into account the model objects where views and tables correspond to classes. Other ORMs supported by Entity Developer do not have the SSDL part which stores information whether class is represented by table in database or by view. Please submit your suggestion at http://devart.uservoice.com/forums/3867 ... -developer to redesing the "update model" tree for Entity Framework.

[bgoldstein14]

I have been able to reproduce my problem continually and I cannot get around it. If I Create a model and choose entities from a schema different than the default one, I get ALL of the entities from the default one, regardless of what I chose from the desired schema. Updating the model is the only way that I can get the desired entities (at least using the Express edition, since it only allows for 10 and when it grabs the default schema entities it eats up all 10).

[Shalex]

If I Create a model and choose entities from a schema different than the default one, I get ALL of the entities from the default one, regardless of what I chose from the desired schema.

Thank you for your report. We are investigating the issue.

[Shalex]

The problem is that the wizard currently doesn't remember the tree state before pressing "Show all users" and after. So you may unselect default schema, press "Show all users", and focus will be set in the beginning of the tree so you are not notified about setting back the selection of the objects from the default schema. You need to unselect the default schema again. The new 8.4.437 build of dotConnect for Oracle (Entity Developer v5.7.634) includes improvement: after pressing "Show all users", the focus is set to default schema so the user "is notified" that he should reunselect the default schema objects again.

We are planning to redesign this approach to make the wizard remember user choice before pressing "Show all users" and after.