Oracle - how to pull in associated table data?

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
bgoldstein14
Posts: 6
Joined: Thu 04 Jun 2015 19:32

Oracle - how to pull in associated table data?

Post by bgoldstein14 » Thu 04 Jun 2015 19:46

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Oracle - how to pull in associated table data?

Post by Shalex » Fri 05 Jun 2015 11:12

bgoldstein14 wrote:it works with Oracle's version of the EF6 driver
You mean ODP.NET, don't you?
bgoldstein14 wrote:

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
Posts: 6
Joined: Thu 04 Jun 2015 19:32

Re: Oracle - how to pull in associated table data?

Post by bgoldstein14 » Fri 05 Jun 2015 13:19

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Oracle - how to pull in associated table data?

Post by Shalex » Fri 05 Jun 2015 16:10

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
Posts: 6
Joined: Thu 04 Jun 2015 19:32

Re: Oracle - how to pull in associated table data?

Post by bgoldstein14 » Fri 05 Jun 2015 17:08

I sent an example.

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

Re: Oracle - how to pull in associated table data?

Post by Shalex » Mon 08 Jun 2015 11:11

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
Posts: 6
Joined: Thu 04 Jun 2015 19:32

Re: Oracle - how to pull in associated table data?

Post by bgoldstein14 » Mon 08 Jun 2015 17:52

I just resent it.

bgoldstein14
Posts: 6
Joined: Thu 04 Jun 2015 19:32

Re: Oracle - how to pull in associated table data?

Post by bgoldstein14 » Tue 09 Jun 2015 14:30

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Oracle - how to pull in associated table data?

Post by Shalex » Thu 11 Jun 2015 13:46

bgoldstein14 wrote:I just resent it.
Thank you. We have asked for the additional information about the test project by email.
bgoldstein14 wrote: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?
bgoldstein14 wrote: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
Posts: 6
Joined: Thu 04 Jun 2015 19:32

Re: Oracle - how to pull in associated table data?

Post by bgoldstein14 » Thu 11 Jun 2015 15:26

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Oracle - how to pull in associated table data?

Post by Shalex » Mon 15 Jun 2015 14:35

bgoldstein14 wrote: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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Oracle - how to pull in associated table data?

Post by Shalex » Fri 19 Jun 2015 14:36

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.

Post Reply