Hi guys,

I'm using Adapter Scenario, GenPro v2.0, Feb 2007 libraries.

I've read the documentation regarding the sub-selects that is generated by using Prefetch Paths.

This is what I have: I have 2 entities (in this specific instance) that are related: 1. OrderSubsetEntity 2. CustomerSubsetEntity

So, I am attempting to get all OrderSubsetEntities (and also the customers related to these entities). OrderSubsetEntity is related to CustomerSubsetEntity on a combination of StoreNumber and CustomerNumber (which make up this Entity's PK).

There is no physical FK in the DB for this relation (since we cannot guarantee integrity) - we have created this relation using GenPro Designer.

So, back to my query, I'm trying to get all Orders matching criteria that is given by a user in the GUI, as well as their related Customers. Therefore, consider the code below (I have cut out all the other validation code to simplify this for reading ):

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(OrderSubsetEntity.Relations.CustomerSubsetEntityUsingStoreCustomerNumber, JoinHint.Inner);

IEntityRelation order2customer = OrderSubsetEntity.Relations.CustomerSubsetEntityUsingStoreCustomerNumber;

order2customer.CustomFilter = new PredicateExpression(
                        new FieldCompareValuePredicate(CustomerSubsetFields.CustomerNumber, null, ComparisonOperator.Equal, Criteria.Customer.CustomerId));

bucket.Relations.Add(order2customer, JoinHint.Inner);

IPrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.OrderSubsetEntity);
            prefetch.Add(OrderSubsetEntity.PrefetchPathCustomerSubset).

            EntityCollection<OrderSubsetEntity> entityCollection = new EntityCollection<OrderSubsetEntity>();
            DataAccessAdapter adapter = new DataAccessAdapter();
            adapter.FetchEntityCollection(entityCollection, bucket, 301, new SortExpression(), prefetch);
            adapter.CloseConnection();

The issue I have is with the resultant SQL - TWO Statements for the query above! And all the Inner SELECTS (Sub-Queries)? There must be a better way?

SELECT DISTINCT TOP 301 [SLMS_DEV].[dbo].[OrderSubset].[Store], [SLMS_DEV].[dbo].[OrderSubset].[CustomerNumber], [SLMS_DEV].[dbo].[OrderSubset].[SalesOrderNumber], 
[SLMS_DEV].[dbo].[OrderSubset].[Instalment], [SLMS_DEV].[dbo].[OrderSubset].[OrderType] FROM ( [SLMS_DEV].[dbo].[CustomerSubset]  INNER JOIN [SLMS_DEV].[dbo].[OrderSubset]  ON  
[SLMS_DEV].[dbo].[CustomerSubset].[Store]=[SLMS_DEV].[dbo].[OrderSubset].[Store] AND [SLMS_DEV].[dbo].[CustomerSubset].[CustomerNumber]=[SLMS_DEV].[dbo].[OrderSubset].[CustomerNumber] AND ( 
[SLMS_DEV].[dbo].[CustomerSubset].[Surname] LIKE 'N%'))

SELECT [SLMS_DEV].[dbo].[CustomerSubset].[Store], [SLMS_DEV].[dbo].[CustomerSubset].[CustomerNumber], [SLMS_DEV].[dbo].[CustomerSubset].[IdNumber], [SLMS_DEV].[dbo].[CustomerSubset].[Surname], 
[SLMS_DEV].[dbo].[CustomerSubset].[AddressLine1], [SLMS_DEV].[dbo].[CustomerSubset].[PassportNumber], [SLMS_DEV].[dbo].[CustomerSubset].[Initials] 
FROM [SLMS_DEV].[dbo].[CustomerSubset]  
WHERE ( 
[SLMS_DEV].[dbo].[CustomerSubset].[Store] IN (SELECT [SLMS_DEV].[dbo].[OrderSubset].[Store] FROM ( [SLMS_DEV].[dbo].[CustomerSubset]  INNER JOIN [SLMS_DEV].[dbo].[OrderSubset]  ON  
[SLMS_DEV].[dbo].[CustomerSubset].[Store]=[SLMS_DEV].[dbo].[OrderSubset].[Store] AND [SLMS_DEV].[dbo].[CustomerSubset].[CustomerNumber]=[SLMS_DEV].[dbo].[OrderSubset].[CustomerNumber] AND ( 
[SLMS_DEV].[dbo].[CustomerSubset].[Surname] LIKE 'N%'))) AND [SLMS_DEV].[dbo].[CustomerSubset].[CustomerNumber] IN (SELECT [SLMS_DEV].[dbo].[OrderSubset].[CustomerNumber] FROM ( [SLMS_DEV].[dbo].[CustomerSubset]  INNER 
JOIN [SLMS_DEV].[dbo].[OrderSubset]  ON  [SLMS_DEV].[dbo].[CustomerSubset].[Store]=[SLMS_DEV].[dbo].[OrderSubset].[Store] AND [SLMS_DEV].[dbo].[CustomerSubset].[CustomerNumber]=[SLMS_DEV].[dbo].[OrderSubset].[CustomerNumber] 
AND ( [SLMS_DEV].[dbo].[CustomerSubset].[Surname] LIKE 'N%'))))

What's the 2 Queries about? Did I miss something? Am I doing something wrong???

Is there any way for us to generate SQL that's more efficient? Like the SQL below, for example?

SELECT DISTINCT TOP 301 [SLMS_DEV].[dbo].[CustomerSubset].[Store], [SLMS_DEV].[dbo].[CustomerSubset].[CustomerNumber], [SLMS_DEV].[dbo].[CustomerSubset].[IdNumber], [SLMS_DEV].[dbo].[CustomerSubset].[Surname], 
[SLMS_DEV].[dbo].[CustomerSubset].[AddressLine1], [SLMS_DEV].[dbo].[CustomerSubset].[PassportNumber], [SLMS_DEV].[dbo].[CustomerSubset].[Initials] 
FROM [SLMS_DEV].[dbo].[CustomerSubset]  
INNER JOIN [SLMS_DEV].[dbo].[OrderSubset]
ON [SLMS_DEV].[dbo].[CustomerSubset].[Store] = [SLMS_DEV].[dbo].[OrderSubset].[Store]
AND [SLMS_DEV].[dbo].[CustomerSubset].[CustomerNumber]=[SLMS_DEV].[dbo].[OrderSubset].[CustomerNumber] 
AND [SLMS_DEV].[dbo].[CustomerSubset].[Surname] LIKE 'N%'

Please help My DBAs are going to kill me

Thanks, Chris

the query you get with your current code and query you want (sql example) are 2 different sets of data. The current code example will return the following structure: 301 OrderSubsetEntity entities with related CustomerSubsetEntity

OrderSubsetEntity[1]
    ...(all order subset fields)
    CustomerSubsetEntity
OrderSubsetEntity[2]
    ...(all order subset fields)
    CustomerSubsetEntity
OrderSubsetEntity[3]
    ...(all order subset fields)
    CustomerSubsetEntity

your sql code which you proposed would return a DataTable/TypedList of 301 rows with all data contained within 1 row

[1]OrderSubset.Field1, OrderSubset.Field2...  CustomerSubset.Field1,  CustomerSubset.Field2...
[2]OrderSubset.Field1, OrderSubset.Field2...  CustomerSubset.Field1,  CustomerSubset.Field2...
[3]OrderSubset.Field1, OrderSubset.Field2...  CustomerSubset.Field1,  CustomerSubset.Field2...

To get the results of the later you need to create either a TypedList in the LLBL designer, or create a dynamic list in your application. (A typedlist is a extension of a DataTable, so they are interchangable. the difference is design vs. runtime error checking.)

once you have defined this use the adapter.FetchTypedList(); function. There are many overloads so you have complete control over sorting, filtering, grouping, etc.

jmeckley wrote:

the query you get with your current code and query you want (sql example) are 2 different sets of data. The current code example will return the following structure: 301 OrderSubsetEntity entities with related CustomerSubsetEntity

OrderSubsetEntity[1]
    ...(all order subset fields)
    CustomerSubsetEntity
OrderSubsetEntity[2]
    ...(all order subset fields)
    CustomerSubsetEntity
OrderSubsetEntity[3]
    ...(all order subset fields)
    CustomerSubsetEntity

your sql code which you proposed would return a DataTable/TypedList of 301 rows with all data contained within 1 row

[1]OrderSubset.Field1, OrderSubset.Field2...  CustomerSubset.Field1,  CustomerSubset.Field2...
[2]OrderSubset.Field1, OrderSubset.Field2...  CustomerSubset.Field1,  CustomerSubset.Field2...
[3]OrderSubset.Field1, OrderSubset.Field2...  CustomerSubset.Field1,  CustomerSubset.Field2...

To get the results of the later you need to create either a TypedList in the LLBL designer, or create a dynamic list in your application. (A typedlist is a extension of a DataTable, so they are interchangable. the difference is design vs. runtime error checking.)

once you have defined this use the adapter.FetchTypedList(); function. There are many overloads so you have complete control over sorting, filtering, grouping, etc.

Ok, I understand, but how would I retrieve my entities from a typed list?

Thanks

You can Fetch a Dynamic List as an IDataReader, and then projecvt it to an EntityCollection. refer to LLBLGen Pro manual: "Using the generated code -> Adapter/SelfServicing -> Fetching DataReaders and projections"

But that won't get you the related entities, unless you manully do it (loop on the DataTable and read each row to create entities and their related entities).

So basicly if you want to fetch entities and their related entities, PrefetchPaths are the most efficient way. Otherwise if you want to fetch them in One Query -> i.e. One ResultSet, then you will have to use a TypedList / Dynamic List. But then you can't have them all projected back to an entityCollection and its related entities, unless you manully do it.