Hi,

I'm new to LLBLGenPro and trying to learn prefetch paths. I have a situation with these tables:

Parent ( ID, ... )

Child ( ID, ParentID references Parent(ID), ...)

GrandChild (ID, ParentID references Child(ID), ...)

With self-servicing code, I want to fetch all Child and GrandChild objects given a Parent.ID and sort clauses. I did this:

// our sorts are provided
ISortExpression childSort = // sort
ISortExpression grandChildSort = // sort

ChildCollection children = new ChildCollection();

IPredicate filter = PredicateFactory.CompareValue  // filter based on given Parent.ID

// create a prefetch path
IPrefetchPath path = new PrefetchPath( (int)EntityType.Child);
path.Add (ChildEntity.PrefetchPathGrandChild, -1, null, null, grandChildSort);

// add the right relations for Parent
IRelationCollection relations = new RelationCollection();
relations.Add (Parent.Relations.ChildEntityUsingID);

children.GetMulti (filter, -1, childSort, relations, path);

But when I profile the database, the GetMulti call generates a query to the Parent table to retrieve the parent object for each child returned. It's the same query, run once per child. I don't need the parent object for the children. How do I stop this from happening?

The parent object is not fetched in the prefetch path fetch, but can be fetched when you have bound the children collection to a grid AND mapped a field on a related field in the child entity, and the related field is in the parent entity. The grid will then read the data for the field, for each child, which triggers lazy loading per row.

Is this the scenario you're in? If not, if you, in the debugger, step over the getmulti call, are all queries, including the one per child for each parent done in the getmulti call or are the calls triggered later on? You can see what's happening by enabling tracing in your config file (see 'troubleshooting and debugging' in the documentation. It's copying the example settings into your application's app/web.config file and enabling DQE tracing for the Database type you're using. Then each query generated is shown in the vs.net output window, if the application is ran in debug mode.

You do specify a relation, which means that the GetMulti call will produce a join with the parent table: Parent INNER JOIN Child.

This is unnecessary I think, as you filter on Parent.ID, which is the same value as the FK field in child: ParentID. so If you construct a filter on child.ParentID and specify that, you can leave out the relations in the GetMulti call, which saves you a join with the parent table altogether.

This is unnecessary I think, as you filter on Parent.ID, which is the same value as the FK field in child: ParentID. so If you construct a filter on child.ParentID and specify that, you can leave out the relations in the GetMulti call, which saves you a join with the parent table altogether.

You're absolutely right. I removed the relation and filtered on child.ParentID instead. The problem occurs when stepping over the GetMulti call, but it didn't go away. Here's my actual code (with some parts filled in for testing), where Hotels is the parent table, Tables is the child table, and TableColumns is the grandchild table. It is run from a method within the Hotel entity.

TablesCollection allTables = new TablesCollection();
IPredicate hotelIdFilter = PredicateFactory.CompareValue (TablesFieldIndex.HotelId, ComparisonOperator.Equal, HotelId);
ISortExpression tablesSort = new SimpleSortClause(TablesFieldIndex.DisplayName);
IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.TablesEntity);
ISortExpression columnsSort = new SimpleSortClause (TableColumnsFieldIndex.DisplayName);
prefetchPath.Add (TablesEntity.PrefetchPathTableColumns, -1, null, null, columnsSort);
allTables.GetMulti (hotelIdFilter, -1, tablesSort, null, prefetchPath);

And here's the output from tracing when I step over the GetMulti call, with the select clauses truncated for clarity and table schema removed for business reasons. One oddity with this database is that the Tables primary key is (HotelID, TableName) and the TableColumns primary key is (HotelID, TableName, ColumnName).

Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [schema].[Tables].[TableName] AS [TableName], ... FROM [schema].[Tables]  WHERE [schema].[Tables].[HotelID] = @HotelId1 ORDER BY [schema].[Tables].[DisplayName] ASC
    Parameter: @HotelId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [schema].[Hotels].[HotelID] AS [HotelId], ... FROM [schema].[Hotels]  WHERE ( [schema].[Hotels].[HotelID] = @HotelId1)
    Parameter: @HotelId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.
Method Exit: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [schema].[Tables].[TableName] AS [TableName], ... FROM [schema].[Tables]  WHERE ( [schema].[Tables].[TableName] = @TableName1 And [schema].[Tables].[HotelID] = @HotelId2)
    Parameter: @TableName1 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: Banquet.
    Parameter: @HotelId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.
Method Exit: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [schema].[Hotels].[HotelID] AS [HotelId], ... FROM [schema].[Hotels]  WHERE ( [schema].[Hotels].[HotelID] = @HotelId1)
    Parameter: @HotelId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.
Method Exit: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [schema].[Tables].[TableName] AS [TableName], ... FROM [schema].[Tables]  WHERE ( [schema].[Tables].[TableName] = @TableName1 And [schema].[Tables].[HotelID] = @HotelId2)
    Parameter: @TableName1 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: BanquetMeal.
    Parameter: @HotelId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.
Method Exit: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [schema].[Hotels].[HotelID] AS [HotelId], ... FROM [schema].[Hotels]  WHERE ( [schema].[Hotels].[HotelID] = @HotelId1)
    Parameter: @HotelId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.
Method Exit: CreateSelectDQ

...

Method Enter: CreateSelectDQ (Paging)
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [schema].[Tables].[TableName] AS [TableName] FROM [schema].[Tables]  WHERE ( [schema].[Tables].[HotelID] = @HotelId1)
    Parameter: @HotelId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.
Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [schema].[Tables].[HotelID] AS [HotelId] FROM [schema].[Tables]  WHERE ( [schema].[Tables].[HotelID] = @HotelId2)
    Parameter: @HotelId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.
Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [schema].[TableColumns].[TableName] AS [TableName], ... FROM [schema].[TableColumns]  WHERE ( [schema].[TableColumns].[TableName] IN (SELECT [schema].[Tables].[TableName] AS [TableName] FROM [schema].[Tables]  WHERE ( [schema].[Tables].[HotelID] = @HotelId1)) And [schema].[TableColumns].[HotelID] IN (SELECT [schema].[Tables].[HotelID] AS [HotelId] FROM [schema].[Tables]  WHERE ( [schema].[Tables].[HotelID] = @HotelId2))) ORDER BY [schema].[TableColumns].[DisplayName] ASC
    Parameter: @HotelId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.
    Parameter: @HotelId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 12.
Method Exit: CreateSelectDQ
Method Exit: CreateSelectDQ (Paging): no paging.

When I do this:

[Test]
public void PrefetchPathTestMultiLevelSimpleWithSorting()
{
    OrderCollection orders = new OrderCollection();
    IPredicateExpression filter = new PredicateExpression();
    filter.Add(PredicateFactory.CompareValue(OrderFieldIndex.CustomerId, ComparisonOperator.Equal, "CHOPS"));
    IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.OrderEntity);
    SortExpression orderSorter = new SortExpression(SortClauseFactory.Create(OrderFieldIndex.OrderDate, SortOperator.Ascending));
    SortExpression orderDetailSorter = new SortExpression(SortClauseFactory.Create(OrderDetailsFieldIndex.ProductId, SortOperator.Ascending));

    prefetchPath.Add(OrderEntity.PrefetchPathOrderDetails, -1, null, null, orderDetailSorter);
    orders.GetMulti(filter, -1, orderSorter, null, prefetchPath);
    for (int i = 0; i < orders.Count; i++)
    {
        Assert.IsTrue( (orders[i].OrderDetails.Count>0));
    }
}

Which is similar to your code, also child - grandchild fetch based on the parent PK filter, I get 2 queries: one for orders and one for orderdetails. Lazy loading isn't triggered, so no Customer entities (parent) are fetched for the order entities (child). So I'm a little confused where the query comes from, or better: what triggers it.

Could you set a breakpoint in: TablesEntityBase.GetSingleHotel(bool forceFetch) first line? It should get a hit when you step over the GetMulti call. When the breakpoint is hit, could you examine the call stack in the debugger where the call originates exactly? Thanks.

I found the problem, but it isn't with LLBLGenPro's code. I was also poking around, trying to figure out how to map multiple TableEntity subclasses to the Tables table using the OnListChanged event, and I recreated the HotelEntity to pass around the PK (this was before I realized calling an entity's constructor with PK values triggered a database call). This approach doesn't seem like such a good idea now. Is there a way to easily subclass entity types that I'm missing?

paulshealy wrote:

I found the problem, but it isn't with LLBLGenPro's code. I was also poking around, trying to figure out how to map multiple TableEntity subclasses to the Tables table using the OnListChanged event, and I recreated the HotelEntity to pass around the PK (this was before I realized calling an entity's constructor with PK values triggered a database call). This approach doesn't seem like such a good idea now. Is there a way to easily subclass entity types that I'm missing?

You can instantiate a new instance by using the empty constructor (with no parameters) and then use entity.FetchUsingPK(...) to fetch the data to work around this.

At the moment, LLBLGen Pro generates one entity per target, this will change in 1.0.2005.1, where multiple entities can be mapped onto the same target.