Hi, I have the following code to perform a search :

var preFetchPath = new PrefetchPath2(EntityType.OrderEntity); preFetchPath.Add(OrderEntity.PrefetchPathCustomer); preFetchPath.Add(OrderEntity.PrefetchPathOrderRequisition).SubPath.Add(OrderRequisitionEntity.PrefetchPathCustomerAddress);

Then I have code to execute the query to return data from DB.

OrderRequisition is a child collection of OrderEntity. OrderEntity has a 1:many relationship with Customer. OrderRequisitionEntity has a 1:1 relationship with CustomerAddress.

Let's say the query returns 50 records. However, when I inpect the CustomerAddress entities, some of them are returned as NULL. I have verified the DB that the data is there. I use DebugView to see all queries being executed. At the block where it executed the PrefetchPath query to get CustomerAddress data, it only loops through 8 records out of 50 records. For Customer PrefetchPath query, it only loops through 6 records out of 50 records. That explains why some return null. It looks like something is limiting the records. So I am not sure if there is a limit in terms of how many preFetchPath we can add.

• What is your LLBLGen version and runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

• What database are you using?

• Is there some slight difference in the FK data versus de PK data? For instance: spaces, like some order record has customerId "ALFKI " (extra space) and the customer table has "ALFKI"

• Please post the complete prefetch + fetch code, it might be relevant.

We are use 3.5 Final

Oracle DB

Here is the code:

var preFetchPath = new PrefetchPath2(EntityType.OrderEntity);
            preFetchPath.Add(OrderEntity.PrefetchPathCustomer);
            preFetchPath.Add(OrderEntity.PrefetchPathOrderRequisition).SubPath.Add(OrderRequisitionEntity.PrefetchPathCustomerAddress);

            EntityCollection<OrderEntity> orders;
            using (var proxy = new OrderServiceProxy())
            {
                var filters =
                    new FilterMappingList
                        {
                            {OrderFields.Branchid, FilterType.Equals, model.BranchId},
                            {OrderRequisitionFields.JobCode, FilterType.Like, model.JobCode, new RelationCollection{OrderRequisitionEntity.Relations.OrderEntityUsingBusinessUnitOrderId}},
                            {OrderFields.Id, FilterType.Equals, model.OrderId},
                            {CustomerFields.Name, FilterType.Equals, model.CustomerName, new RelationCollection { OrderEntity.Relations.CustomerEntityUsingCustomerIdCustSetid }},
                            {OrderFields.CustomerId, FilterType.Equals, model.CustomerId},
                            {OrderFields.SoStatus, FilterType.Equals, model.Status},
                            {OrderFields.SalesRepId, FilterType.Equals, model.BusinessDevManagerId},
                            {OrderFields.RecruiterId, FilterType.Equals, model.RecruiterId},
                            {OrderFields.ManagerContactId, FilterType.Equals, model.ContactId},
                            {
                                CustomerAddressFields.Country, 
                                FilterType.Equals, 
                                model.CountryCode, 
                                new RelationCollection
                                    {
                                        OrderEntity.Relations.OrderRequisitionEntityUsingBusinessUnitOrderId,
                                        OrderRequisitionEntity.Relations.CustomerAddressEntityUsingAddressSequenceNumberCustomerIdCustomerSetid
                                    }
                            },
                            {
                                CustomerAddressFields.State, 
                                FilterType.Equals, 
                                model.StateCode,
                                new RelationCollection
                                    {
                                        OrderEntity.Relations.OrderRequisitionEntityUsingBusinessUnitOrderId,
                                        OrderRequisitionEntity.Relations.CustomerAddressEntityUsingAddressSequenceNumberCustomerIdCustomerSetid
                                    }
                            },
                            {
                                CustomerAddressFields.City, 
                                FilterType.Equals, 
                                model.CityName, 
                                new RelationCollection
                                    {
                                        OrderEntity.Relations.OrderRequisitionEntityUsingBusinessUnitOrderId,
                                        OrderRequisitionEntity.Relations.CustomerAddressEntityUsingAddressSequenceNumberCustomerIdCustomerSetid
                                    }
                            },
                            {OrderFields.SoDate, FilterType.GreaterThanOrEqualTo, model.OpenDate.DateFrom},
                            {OrderFields.SoDate, FilterType.LessThanOrEqualTo, model.OpenDate.DateFrom},
                            {OrderRequisitionFields.StartDate, FilterType.GreaterThanOrEqualTo, model.StartDate.DateFrom, new RelationCollection{OrderRequisitionEntity.Relations.OrderEntityUsingBusinessUnitOrderId}},
                            {OrderRequisitionFields.StartDate, FilterType.LessThanOrEqualTo, model.StartDate.DateFrom, new RelationCollection{OrderRequisitionEntity.Relations.OrderEntityUsingBusinessUnitOrderId}},
                            {OrderRequisitionFields.EndDate, FilterType.GreaterThanOrEqualTo, model.ActualEndDate.DateFrom, new RelationCollection{OrderRequisitionEntity.Relations.OrderEntityUsingBusinessUnitOrderId}},
                            {OrderRequisitionFields.EndDate, FilterType.LessThanOrEqualTo, model.ActualEndDate.DateFrom, new RelationCollection{OrderRequisitionEntity.Relations.OrderEntityUsingBusinessUnitOrderId}}
                        };

                orders = proxy.Service.FindEntity(
                      filters,
                      null,
                      preFetchPath);
            }

Thanks

We need the runtime library version (i.e. build no.), please refer to the Forum guidelines thread for more details.

Also we need the SQL queries produced for the prefetchPaths.

We are using v3.5 of gen pro against v4.0 of the .net framework the target framework is "LLBLGen Pro Runtime Framework" using the adapter model

I have attached the SQL statements.

• That's not the LLBLGen runtime library version. Please see this http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722 We want to be sure that you are using the latest RTL.

• Is there some slight difference in the FK data versus de PK data? For instance: spaces, like some order record has customerId "ALFKI " (extra space) and the customer table has "ALFKI"

• About the log you posted, how many records are obtained from the first query (OrderEntity)? Does the SQL looks ok? What if you run such SQLs directly on DB?

• About the excluded related objects (Customer for instance). Can you find a pattern on them? like some slight difference between the FK and the PK value on DB?

Hi the Build version is 3.5.12.0601

The PK and FK data is consistent

The First Query returns 20 rows. All SQL runs fine if I run them individually. Just odd to see that if did not loop thru the Cust_Address 20 times which it should. It looks like it's been truncated or something.

I compare the length and datatype on the PK, FK. They are consistentf for the excluded related objects

Just odd to see that if did not loop thru the Cust_Address 20 times which it should

I don't understand what you mean by this phrase, could you please elaborate?

There are 20 records returned from the first query (OrderEntity). From what I understand, any preFetchPath that we set up, GenPro will create those "subquery". and it will loop through the number of records returned from the Parent Query. So if the Parent query returns 20 records. there should be 20 subqueries for the preFetch Entity.

Not true.

And this has been the beauty of PrefetchPaths since the inception.

LLBLGen Pro, executes one query only to get the prefetchPath. Wither using a range filter on the PK/FK according to the returned values of the first query. Or filter using a subQuery (main query).

Please check the docs on PrefetchPaths.

Sorry for the confusion. I did not word it properly. What I meant is if there are 20 records returns from the Parent (OrderEntity), there should also be 20 conditions in the where clause for the Prefetch query. But in my case (refer to the attachment that contains the SQL queries), there are less than 20 conditions in the where clause.

joejo318 wrote:

What I meant is if there are 20 records returns from the Parent (OrderEntity), there should also be 20 conditions in the where clause for the Prefetch query. But in my case (refer to the attachment that contains the SQL queries), there are less than 20 conditions in the where clause.

Not exactly. Your Order query returns 20 rows, and Order has m:1 relationship with Customer. So the CustomerId field on Order table may contain multiple values of the same CustomerId. For instance: OrderId CustomerId 1 Cust1 2 Cust2 3 Cust1 4 Cust3 5 Cust3

The efficient customer query that retrieve all of that customers should be like:

SELECT * 
FROM Customer 
WHERE CustomerId = 'Cust1' OR CustomerId = 'Cust2' OR CustomerId = 'Cust3'

So, the Customer query has three conditions, while the main query (Order) has 5 results.

Lets back to your issue. If you are sure that the for some order.Customer, LLBLGen set them to null, where it shouldn't, and you are not limiting the prefetch results, then it indeed looks weird, however there should be a reasonable explanation. But we don't have enough information to give you a straight answer.

Is it possible for you to prepare a repro solution for us? It should contain: - SQL script with your schema and data (make it as small as possible: the less tables and data that reproduces the issue). - Your .llblgenproj file. - Some .net code (Console App for example) that uses your code to fetch the graph. Do not include .dlls, bin/obj folders, etc. Just code. - All above zipped in one file and attached to your post. If you prefer, you can do this in a HelpDesk thread, which is private, so just you and the support team can see it.

Also, I don't know what all the filters do in your code snippet. You send them to a proxy but the prefetch paths have no filters. Do you augment these with filters?

Also you said:

At the block where it executed the PrefetchPath query to get CustomerAddress data, it only loops through 8 records out of 50 records. For Customer PrefetchPath query, it only loops through 6 records out of 50 records.

WHat do you mean 'it loops' ? The merging of the two sets is done inside the runtime, you used a debug build and stepped into the code?

Hi daelmo/Otis, yes i understand that the same customer can be on different order. But for each order, it should retrieve the customer address info based on the Customer regardless whether the customer ID is the same as the previous order record.

My log file(attached) will illustrate this. If I search on a particular customer name (2nd Query in log file), it returns 2 Order records (4th Query in log file). Currently, there is a CustomerAddressEntity for the first record but not the 2nd record. I expect to see 2 of the same query (last query in log file) generated by Gen Pro to retrieve customer address for both Order Records. But I only see one. I think that's the issue why the CustomerAddressEntity for the 2nd Order Record is null.

I hope this helps clarify the issue a bit more.

I inspected your log file and extracted the formatted generated SQL:

Query#1. Fetch ORDER_VW. This query returns 2 rows.

SELECT * 
FROM 
    (   SELECT a.*, rownum r___ 
        FROM 
        (   SELECT DISTINCT  ...
            FROM 
                (
                    (
                        ( "VTX"."CUSTOMER_VW"  
                            INNER JOIN "VTX"."ORDER_VW"  
                                ON  "VTX"."CUSTOMER_VW"."CUST_ID"="VTX"."ORDER_VW"."CUST_ID" 
                                AND "VTX"."CUSTOMER_VW"."SETID"="VTX"."ORDER_VW"."CUST_SETID"
                        ) 
                        INNER JOIN "VTX"."ORDER_REQUISITION_VW"  
                            ON  "VTX"."ORDER_VW"."BUSINESS_UNIT"="VTX"."ORDER_REQUISITION_VW"."BUSINESS_UNIT" 
                            AND "VTX"."ORDER_VW"."SO_ID"="VTX"."ORDER_REQUISITION_VW"."SO_ID"
                    ) 
                    INNER JOIN "VTX"."CUSTOMER_ADDRESS_VW"  
                        ON  "VTX"."CUSTOMER_ADDRESS_VW"."ADDRESS_SEQ_NUM"="VTX"."ORDER_REQUISITION_VW"."ADDRESS_SEQ_NUM" 
                        AND "VTX"."CUSTOMER_ADDRESS_VW"."CUST_ID"="VTX"."ORDER_REQUISITION_VW"."CUST_ID" 
                        AND "VTX"."CUSTOMER_ADDRESS_VW"."SETID"="VTX"."ORDER_REQUISITION_VW"."CUST_SETID"
                ) 
            WHERE 
                ( UPPER("VTX"."CUSTOMER_VW"."NAME1") = :p1) 
                AND ( UPPER("VTX"."CUSTOMER_ADDRESS_VW"."COUNTRY") = :p2)
            ) a 
        WHERE rownum < :p6
    ) 
WHERE r___ >=:p4    

Parameter: :p1 : AnsiString. Length: 40. Precision: 0. Scale: 0. Direction: Input. Value: "WELLCARE HEALTH PLANS INC".  
Parameter: :p2 : AnsiString. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: "USA".     
Parameter: :p6 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 251.    
Parameter: :p4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.  

Query#2. Get the CUSTOMER_VW for the fetched Orders. As you are filtering the Orders by the Customer name, it's natural that them both have the same Customer. That's why the SQL generated only filters on one Customer value.

SELECT ...
FROM "VTX"."CUSTOMER_VW" 
WHERE ( ( ( 
    "VTX"."CUSTOMER_VW"."CUST_ID" = :p1 
    AND "VTX"."CUSTOMER_VW"."SETID" = :p2)))    
Parameter: :p1 : AnsiString. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "9444896".    
Parameter: :p2 : AnsiString. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "SHARE".   

Query#3. Get the ORDER_REQUISITION_VW for the fetched Orders. Here as expected, it filters the 1:n ORDER_REQUISITION_VW based on the two fetched orders. This query might returns any number of records: 0,1,2,3,etc.

SELECT ...
FROM "VTX"."ORDER_REQUISITION_VW" 
WHERE 
    ( "VTX"."ORDER_REQUISITION_VW"."BUSINESS_UNIT" = :p1 
        AND "VTX"."ORDER_REQUISITION_VW"."SO_ID" = :p2
    ) 
    OR 
    ( "VTX"."ORDER_REQUISITION_VW"."BUSINESS_UNIT" = :p3 
        AND "VTX"."ORDER_REQUISITION_VW"."SO_ID" = :p4
    )   
Parameter: :p1 : AnsiString. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "10116".   
Parameter: :p2 : AnsiString. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "0000000869".     
Parameter: :p3 : AnsiString. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "10116".   
Parameter: :p4 : AnsiString. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "0000000873".     

Query#4. This return the 1:1 CUSTOMER_ADDRESS_VW for each fetched ORDER_REQUISITION_VW. The parameters used depends on the number of ORDER_REQUISITION_VW fetched.

SELECT ...
FROM "VTX"."CUSTOMER_ADDRESS_VW" 
WHERE 
    "VTX"."CUSTOMER_ADDRESS_VW"."ADDRESS_SEQ_NUM" = :p1 
    AND "VTX"."CUSTOMER_ADDRESS_VW"."CUST_ID" = :p2 
    AND "VTX"."CUSTOMER_ADDRESS_VW"."SETID" = :p3   
Parameter: :p1 : Double. Length: 0. Precision: 38. Scale: 38. Direction: Input. Value: 21.  
Parameter: :p2 : AnsiString. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "9444896".    
Parameter: :p3 : AnsiString. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "SHARE".   

For the query 3 and 4, the options are:

A. Query#3 returns 2 or more rows, but they share the same CUSTOMER_ADDRESS_VW. So as they points to the same address, the Query#4 needs to return only one customer.

B. Query#3 return 2 or more rows but for some of them there is no customer address. So the framework inspect that and fetch with Query#4 only the addresses that are present on the fetched ORDER_REQUISITION_VW rows.

With the information I have, I would tend to think that what is happening is B. To be sure, run Query#3 at SQLPlus and see how many records you get and for those records how many satisfy the ORDER_REQUISITION_VW--CUSTOMER_ADDRESS_VW relation.

BTW, Are you sure that ORDER_REQUISITION_VW--CUSTOMER_ADDRESS_VW is 1:1? How does look the DDL script for those two tables?

If you are sure that this is unexpected (i.e. a bug), please provide a repro solution (as I proposed to you at my previous post) so we can take a closer look at it.

Here is the solution file with GenPro project. The Data is inside the "DatabaseTable" folder. They are in excel format so will need to import them to your database server.

HEre is the attachment

Just before trying your repro solution: What are the results for the Query#3 in your DB?

2 rows

I see from your results in your zipped file, that the Query#4 is correct. What is strange is that you get null for the CustomerAddress on the second OrderRequisition.

Could you please give us a DB script with your schema and the test data (as small as possible). Otherwise we will try to recreate it and insert the data. However is this is a data issue, it might help if we work with the real data extracted from DB into a script.

As mentioned in previous post that the Data is stored in the "DatabaseTable" folder in the zip file. You can import that into your database server.

I have to take a look at your data files. Ideally I would want a DB script for all the DB stuff, as for me, this is a data issue, not a LLBLGen one. I will take a look at your solution this this weekend. I have to recreate your schema based on the .llblgenproj file, and then insert the data you sent. I will keep you posted this weekend...

We need your Volt.VTX.GenPro.TypeConverters assemby in order to open you .llblgenproj file and compile your solution. You can send us the project code for that typeConverter so we could compile it here.

The type converter is for datetime conversions I think, which are of no influence of the fetch. Replacing TypeConverterToUse="Volt.VTX.GenPro.TypeConverters.DateTimeESTConverter" with nothing in a text editor makes the project loadable.

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

Anyway, looking at the logs David analyzed in his post above, the code you attached is different from the logs you posted: the code you attached passes a single filter, no joins. Your log however shows you've passed joins to the orders fetch, which join the complete graph to the orders, and you specify paging as well.

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.

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

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 .

David: I have attached the TypeConverter.dll It's a little bit complicated to extract the table schemas as each view has joins on different tables. Let me know if you run into issue importing the data from the excel files.

Otis: Yes the log is different than the code provide. But both will produce the same result where the only the first Req returns a address Entity. the Expected results is both Req should return its own Address Entity. I am interested to know if David can reproduce the issue.