Hi Frans,

I've read the PreFetch Paths info in the documentation several times tonight but I just cannot wrap my head around how filtering works here.

I love the way that LLBLGen picks up the M:N relationships and provides the "PrefetchPathProductCollectionViaCustomerOrders" - this is very cool!

I have a Customer, Order, Product type M:N relationship. I want to fetch all the products ordered by a particular customer.

So I have:

_Products = New EntityCollection(New ProductEntityFactory)

Dim prefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.CustomerEntity, Integer))
prefetchPath.Add(TaxSearch.PrefetchPathProductCollectionViaCustomerOrders)

Dim filter As IRelationPredicateBucket = New RelationPredicateBucket
filter.PredicateExpression.Add(PredicateFactory.CompareValue(CustomerFieldIndex.Id, ComparisonOperator.Equal, Me.Id))
filter.Relations.Add(Customer.Relations.CustomerOrdersEntityUsingCustomerId)

adapter = New DataAccessAdapter

adapter.FetchEntityCollection(_Products, filter, prefetchPath)

Here is the stacktrace:

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The column prefix 'web.dbo.Product' does not match with a table name or alias name used in the query.
The column prefix 'paprocessing_web.dbo.Product' does not match with a table name or alias name used in the query.
The column prefix 'paprocessing_web.dbo.Product' does not match with a table name or alias name used in the query.
The column prefix 'paprocessing_web.dbo.Product' does not match with a table name or alias name used in the query.
The column prefix 'paprocessing_web.dbo.Product' does not match with a table name or alias name used in the query.
The column prefix 'paprocessing_web.dbo.Product' does not match with a table name or alias name used in the query.
The column prefix 'paprocessing_web.dbo.Product' does not match with a table name or alias name used in the query.
The column prefix 'paprocessing_web.dbo.Product' does not match with a table name or alias name used in the query.
The column prefix 'paprocessing_web.dbo.Product' does not match with a table name or alias name used in the query.
The column prefix 'paprocessing_web.dbo.Product' does not match with a table name or alias name used in the query.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IValidator validatorToUse)
SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize)
SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, IPrefetchPath2 prefetchPath)

prefetchPath.Add(TaxSearch.PrefetchPathProductCollectionViaCustomerOrders)

should be:

prefetchPath.Add(Customer.PrefetchPathProductCollectionViaCustomerOrders)

As you specify as the root type the CustomerEntity

Sorry that was a typo - I do have the correct entity in my code.

If this isn't the real code, please paste the real code.

Also the relation you're adding isn't necessary, the filter is on the customer entity.

Could you also enable tracing on the DQE so you can see the query being executed? That would help tracking down what's going on.

Frans,

I figured the issue, I needed to do a single entity prefetch. Along these lines:

Dim prefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.CustomerEntity, Integer)) prefetchPath.Add(Me.PrefetchPathProductCollectionViaCustomerCustOrders) Dim arbProduct As New Product(122)

Dim adapter As New DataAccessAdapter

If adapter.FetchEntity(arbProduct, prefetchPath)...

This seems to fetch and populate the Products collection of my Customer entity just fine.

I'm finding the lack of examples and docs on prefetch a real issue. I downloaded the petshop example app and it doesn't have any examples of prefetching a collection. As mentioned earlier I read the Prefetch Paths docs in the help file - is there anywhere else with more info/examples? I'm starting to think my brain ain't as big as some of the folk whose posts I read here.

I've found the learning curve on LLBLGen tough but well worth it in the long run. I have used it for well over a year now and it's very solid (and once you've got it - it really pays dividends in reduced dev time). I've read your responses to these observations in the past and do agree that anything that comes with the flexibility and power of LLBLGen will have a learning curve. But having said that I think prefetch needs some more examples. I only hust got used to using GetRelationInfo and this stuff is throwing me.

I really like the "How do I" section of the help - I am far more visual with my comprehension. I get lost with all the verbose discussion of predicates, factories, graphs etc.

Anywhere else I need to look for help on prefetch?

DrM wrote:

Frans,

I figured the issue, I needed to do a single entity prefetch. Along these lines:

Dim prefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.CustomerEntity, Integer)) prefetchPath.Add(Me.PrefetchPathProductCollectionViaCustomerCustOrders) Dim arbProduct As New Product(122)

Dim adapter As New DataAccessAdapter

If adapter.FetchEntity(arbProduct, prefetchPath)...

This seems to fetch and populate the Products collection of my Customer entity just fine.

Ok, sorry I didn't spot the error. As your code was pretty slim I couldn't hammer down what the cause would be why the prefetch path failed.

I'm finding the lack of examples and docs on prefetch a real issue. I downloaded the petshop example app and it doesn't have any examples of prefetching a collection.

Yes it does, please check the BL project classes, like the ItemManager and the accountmanager.

Also the northwind example has prefetch paths (the adapter version).

As mentioned earlier I read the Prefetch Paths docs in the help file - is there anywhere else with more info/examples? I'm starting to think my brain ain't as big as some of the folk whose posts I read here.

Naah, don't belittle yourself, you probably thought in the wrong direction. Prefetch paths aren't rocketscience.

Take a step back and look at the relational model: the ENTITY customer has a relation with the ENTITY order. The Customer entity has a field mapped onto that relation, for example Orders. Order has a relation with Product, and has a field mapped on that relation, 'Product'.

So when you're fetching 'Customer', 'Order' and 'Product', you're in fact fetching a graph: 1 or more customer entities, and for each of them, their orders, and for each order the product belonging to the order.

With prefetch paths, you define 'paths' in such a graph. So instead of fetching 'per customer its orders and per order its product', you simply define 'customer - Order - Product' using a prefetch path. When building the prefetch path in code, where you use code like prefetchPath.Add(CustomerEntity.PrefetchPathOrders); you add 'nodes' to the path.

So with a prefetch path, you tell LLBLGen Pro which sets to fetch when the root entity/entities of the graph are fetched, in your case 'customers' as root and as sets: orders and products (in the case of the graph customer - order - product).

The runtime will then fetch per node all entities and will merge them with their parent, so it will fetch all orders for the customers fetched and will merge these entities in the customer.Orders collection of the customer they belong to. That's done for you

That's basicly it.

Also, try for example 'Prefetch path' in the search engine, or click the link below: Direct search on prefetch path

which will search on 'prefetch path' on the forum. We'll be adding a list of handy shortcuts like these to the site shortly.

I've found the learning curve on LLBLGen tough but well worth it in the long run. I have used it for well over a year now and it's very solid (and once you've got it - it really pays dividends in reduced dev time). I've read your responses to these observations in the past and do agree that anything that comes with the flexibility and power of LLBLGen will have a learning curve. But having said that I think prefetch needs some more examples. I only hust got used to using GetRelationInfo and this stuff is throwing me.

I thought I provided enough examples, but your feedback tells me otherwise. I'll add more to the howdoI section.

As always your support is fantastic. Thanks for the explanation - I will check out those examples again. I did seem some prefetch examples but not anything specifically for fetching entitycollections using the prefetchpath2 element created by the root node.

Thanks again. P.S. If you want some exposure on my Pocket PC development site for your NETcf version drop me an email at derek atyouknowwhatgoeshere devbuzz.com.

DrM wrote:

As always your support is fantastic. Thanks for the explanation - I will check out those examples again. I did seem some prefetch examples but not anything specifically for fetching entitycollections using the prefetchpath2 element created by the root node.

Thanks again. P.S. If you want some exposure on my Pocket PC development site for your NETcf version drop me an email at derek atyouknowwhatgoeshere devbuzz.com.

thanks! Will do!

Btw, have you kicked the tires of the CF.NET port of the code yet? I don't have a lot of experience with the CF.NET framework so I am eager to know what CF.NET die-hards think of it

Otis wrote:

Btw, have you kicked the tires of the CF.NET port of the code yet? I don't have a lot of experience with the CF.NET framework so I am eager to know what CF.NET die-hards think of it

Not yet Frans but it's on my list. In fact there's a portion of a data project that's just crying out for some attention and I know what I'm going to use. As soon as I get there I'll be sure to drop you a line

DrM wrote:

Otis wrote:

Btw, have you kicked the tires of the CF.NET port of the code yet? I don't have a lot of experience with the CF.NET framework so I am eager to know what CF.NET die-hards think of it

Not yet Frans but it's on my list. In fact there's a portion of a data project that's just crying out for some attention and I know what I'm going to use. As soon as I get there I'll be sure to drop you a line

cool! Let me know if you run into any problems with it , we're still some time away from release anyway.