Hi Joe,

I really want to help you, but as Frans said: we don't have the views, and your entities are based on views, so we can't repro with the info you provided. Before we go further asking you to attach more files (full script with relevant data and views), please review and answer the Frans comments, like:

Otis wrote:

Data in excelsheets is cumbersome to import. You also map onto views, so we can't re-generate those from the project.

Otis wrote:

Though what I think happens is that ADDRESS_SEQ_NUMBER is a FLOAT type, so a floating point number. Although the excel sheet says it's 21, this has no real meaning, as 0 doesn't necessary have to be 0 and a value in an excelsheet is not the value from the DB.

Otis wrote:

The values are produced by views, perhaps they do processing of the values?

Otis wrote:

Also, it should be reproducible by fetching OrderRequisition entities with a single path node to CustomerAddress. According to the logs, the rest of the entities in your original path are fetched properly .

This last one is very important for you to answer.

Thanks

If it helps: please create a small repro case, using tables in a new schema, and you fill them by using insert ... SELECT * FROM <your view>, so you can create a small repro with live data, no views necessary. The import of data from the excelsheets is cumbersome, I'm sorry, but you have to provide a simpler repro case for us to look into.

Otis wrote:

Data in excelsheets is cumbersome to import. You also map onto views, so we can't re-generate those from the project.

Answer: I have attached the Table Schema as well as data for that particular customer

Otis wrote:

Though what I think happens is that ADDRESS_SEQ_NUMBER is a FLOAT type, so a floating point number. Although the excel sheet says it's 21, this has no real meaning, as 0 doesn't necessary have to be 0 and a value in an excelsheet is not the value from the DB.

Answer: in the schema, the type is NUMBER(*,0) in the Oracle table

Otis wrote:

The values are produced by views, perhaps they do processing of the values?

Answer: No

Otis wrote:

Also, it should be reproducible by fetching OrderRequisition entities with a single path node to CustomerAddress. According to the logs, the rest of the entities in your original path are fetched properly .

Answer: yes. the rest of the Entities are fetched properly except for the customer_address for each requisition.

Since more data was added on our end, I have included an updated log file in the zip file. Let me know if you need more info. thanks!

What about a simple dummy repro solution, as Frans has asked?

Otis wrote:

please create a small repro case, using tables in a new schema, and you fill them by using insert ... SELECT * FROM <your view>, so you can create a small repro with live data, no views necessary. The import of data from the excelsheets is cumbersome, I'm sorry, but you have to provide a simpler repro case for us to look into.

Usually this helps a lot.

You didn't attach an attachment to your last post.

hmm. I thought I did. anyways, let me attach again.

Hi there,

I had to tackle your sql scripts, finally get the schema in my DB.

Then I tried to insert your .csv's using sqlldr. It took time but I finally got it.

Then I created the LLBLGen Project. I notice that your SQL scripts don't have the relations defined. So I tried to defined them using model-only relationships. However I fail defining OrderRequisition->CustomerAddress (PS_RS_SO_LINE -> PS_CUST_ADDRESS) because PS_RS_SO_LINE doesn't have any SET_ID or CUST_SET_ID column.

Please help us to reproduce your issue. Make a full script with all needed fields, data, relations, .net code, that reproduces the problem.

Hi David, I missed sending you the scripts to create the views. Here it is. I had sent the Gen Pro project file as well as the .NET solution file in previous threads. Let me know if you need anything else.

Thanks!

I finally got your stuff running on my machine. Now I will try to reproduce your issue.

When I refresh the model from the database, I had to remove some fields because they weren't on the views you gave me, but I think those fields are not relevant for now.

One question: How did you create the relationships? (They are not in the database and neither they are model-only)

I have run your code, and yes, I reproduced your issue. Then I tried different situations, like:

A. (original test) Return all orders from the same customer. Only one of the order.OrderRequisition gets the CustomerAddress attached (always the last one in the list).

B. Return only one Order (one of those orders that its OrderRequisition didn't get the CustomerAddress at Test A). Amazingly this worked.

C. Return all OrderRequisitions (so just two prefetchPath levels). The behavior is the same as Test A.

D. Return only one OrderRequisition (one of those requisitions that didn't get the CustomerAddress attached). This worked.

For all tests, the generated SQL is correct. Then I found the pattern: when you get two or more orderRequisitions with the same CustomerAddress, only one of them is used to set the CustomerAddress. And that is because your OrderRequisition->CustomerAddress relationship is set as one-to-one (1:1), but obviously this is not the case, as you can have the same CustomerAddress for many OrderRequisition (1:n). In fact all your 7 order requisition records are pointing to just two different customer addresses. When you have 1:1 relationship, the fetched PK object (CustomerAddress) can only reference one FK object (OrderRequisition) and viceversa. To understand this, please read FK-PK synchronization

So, to fix that, modify the OrderRequisition->CustomerAddress relationship in your LLBLGen Project and set that as m:1 (OrderRequisition is the 'many' side, CustomerAddress is the '1' side).

A 1:1 relationship also requires a UC, at least the designer will create one. Did you skip this UC and simply ignore it? (otherwise the data would conflict with it)

Hi David/Frans, thanks for your help! it's working now. it totally makes sense that each address can be on many Reqs.

Frans, what's UC?

UC is 'unique constraint' and if you have it in your table (i.e. OrderRequisition. CustId/SetId/SeqNum) then it should be unique for that table and it is a candidate for 1:1 relationship, ant that is detected by LLBLGen. I think this doesn't apply to your case because you are using Views and you are making a field on your view (CustId) based on some logic.