Set-up I have a collection of entityA, each entityA has a 1-n relation with entityB.

If I fetch a collection of entityAs without prefetching entityB, then in another collection I fetch entityBs without prefetching entityAs.

Question Is there a way to connect the "proper" (related by fk) entityBs to their corresponding entityA without having to manually search or filter the second collection for each occurance of entityA in the first collection?

I think I'm looking for a method probably on collection that would do this for me, perhaps, needing to be given a relation object if there is more than one possibility for the join.

If this doesn't currently exists, could this message be moved to feature request?

The reason I "need" this is often prefetch paths perform poorly and I can often do much better by fetching the "prefetch" entities in a seperate fetch, however I then need a way to sort the entities to their proper parent entity.

arschr wrote:

Set-up The reason I "need" this is often prefetch paths perform poorly and I can often do much better by fetching the "prefetch" entities in a seperate fetch, however I then need a way to sort the entities to their proper parent entity.

Could you elaborate more on this? Have you seen LLBLGenPro Help - Using generated code - Adapter - PrefetchPaths - Optimizing Prefetch Paths?

Have you seen LLBLGenPro Help - Using generated code - Adapter - PrefetchPaths - Optimizing Prefetch Paths?

Yes. It gives two options.

I'm still testing the difference in performance, so let me say, I think I can beat the built in performance in some cases.

For example: As you get into deep prefetch paths say 5 levels deep, where the 5th level also has constraints base on previously retrieved data, my theory is I can improve performance by basing the 5th level query off of the 4th level pks extracted client side with the constraints being performed using in memory filtering.

But no matter how much retrieval performance I gain, I still need a way to sort the entities to their proper parents. So regardless of the retrieval stategy, say I have two collections of relateable entities. Are there LlblGen methods available that would: for each entity in collection a, identify the entities in collection b that are related (based on their pk/fk values) and append those entities into entity A's entityB collection?

I am using beta 2.5 here but don't think the question is specific beta functionality

Your general question is about (I think, correct me if I'm wrong): "I want the entities related to A fetched, but I have them already in memory somewhere else, so use these instead" ?

In theory that's solved by a cache, the big question then is: how do you know which entities to obtain from the cache for A ?

In very specific situations you might know this, e.g. you have loaded a customer FOO, and you later on load an order 12345, which happens to be FOO's order. So without further ado, the system should connect these two. This comes down to this: http://www.matshelander.com/wordpress/?p=55

where Mats, a good friend of mine btw, discusses a challenge which mimics I think what you describe.

If so, it's not supported. The reason is that it has drawbacks as well and to make it work, you have to know up front which entities to load from the cache and which ones from the db based on a query. That's only possible if you load everything in the cache first and query the cache instead and all queries performed go through the cache.

That's very hard (read: impossible, see my cache myth article here ) and with a loosely coupled system as LLBLGen Pro, it's not advisable either.

So there's no real central cache to read from, so a system can't hook them up automatically.

What could be done eventually is having some kind of merge facility as prefetch paths use already. Though before I go deeper into that, I think it would be better if the description I gave above is indeed what you're after

Your general question is about (I think, correct me if I'm wrong): "I want the entities related to A fetched, but I have them already in memory somewhere else, so use these instead" ?

No I don't think so.

What could be done eventually is having some kind of merge facility as prefetch paths use already. Though before I go deeper into that, I think it would be better if the description I gave above is indeed what you're after

Yes, I think what I want is:

having some kind of merge facility as prefetch paths use already

In fact if my experimentation works out, I would (like to) see the additional optimizations added to vNext.x prefetch path logic. In other words, I only want this because sometimes prefetch paths don't seem (to me) to perform as well as they could with additional optimization strategies.

I can

RelationPredicateBucket bucket = new RelationPredicateBucket();

                        IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.InvoiceActivityLog01aEntity);

                        ISortExpression sorterProccessingLog = new SortExpression();
                        sorterProccessingLog.Add(ProcessingLogFields.DateAdded | SortOperator.Descending);

                        IPredicateExpression predicateExpressionProcessingLog = new PredicateExpression( );

                        predicateExpressionProcessingLog.Add( InvoiceActivityLog01aFields.CurrentActivityId == 13);

                        IRelationCollection relationCollectionProcessingLog = new RelationCollection( );

                        relationCollectionProcessingLog.Add(
                                ProcessingLogEntity.Relations.InvoiceActivityLog01aEntityUsingInvoiceId );

                        prefetchPath.Add(InvoiceActivityLog01aEntity.PrefetchPathProcessingLog, 0, null, null, sorterProccessingLog);
                        prefetchPath.Add(InvoiceActivityLog01aEntity.PrefetchPathInvoiceActivityLog02b, 0);
                        prefetchPath.Add(InvoiceActivityLog01aEntity.PrefetchPathInvoiceComponent01a, 0);
                        

                        bucket.PredicateExpression.Add( BuildInvoiceActivityPredicateExpression( activityConfiguration, invoicesIds, fileIds ));

                        ISortExpression sorter = new SortExpression(InvoiceActivityLog01aFields.CurrentActivityDate | SortOperator.Descending);
                        logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);
                    
                        adapter.FetchEntityCollection(collection, bucket, 0, sorter, prefetchPath);

or i could

adapter.CommandTimeOut = _commandTimeOut;
                    adapter.KeepConnectionOpen = true;
                    DateTime start = DateTime.Now;

                    logger.Trace("Perf:{0} {1}",++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);
                    try
                    {
                        RelationPredicateBucket bucket = new RelationPredicateBucket();

                        ISortExpression sorterProccessingLog = new SortExpression(ProcessingLogFields.DateAdded | SortOperator.Descending);

                        IPredicateExpression predicateExpressionProcessingLog = new PredicateExpression(InvoiceActivityLog01aFields.CurrentActivityId == 13);
                        
                        bucket.PredicateExpression.Add(BuildInvoiceActivityPredicateExpression(activityConfiguration, invoicesIds, fileIds));

                        ISortExpression sorter = new SortExpression(InvoiceActivityLog01aFields.CurrentActivityDate | SortOperator.Descending);
                        logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);
                    
                        adapter.FetchEntityCollection(collectionInvoiceActivityLog, bucket, 0, sorter);
                        logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);
                        collectionInvoiceActivityLog.CreateHierarchicalProjection(ds);
                        logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);
                                                

                        InvoiceIdList invoiceList = GetInvoiceList(collectionInvoiceActivityLog, null);                     
                        logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);

                        InvoiceIdList invoiceCompletedList = GetInvoiceList( collectionInvoiceActivityLog,new PredicateExpression(InvoiceActivityLog01aFields.CurrentActivityId == 7) );
                        logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);

                        InvoiceIdList invoiceBuildingList = GetInvoiceList(collectionInvoiceActivityLog, new PredicateExpression(InvoiceActivityLog01aFields.CurrentActivityId == 13));
                        logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);
                    
                        TimeSpan fetchDuration = DateTime.Now - start;
                        
                        int recordsReturned = invoiceList.Count;

                        logger.Trace(string.Format("Fetched {0} invoice list in {1}", recordsReturned, fetchDuration.TotalSeconds));

                        if (recordsReturned > 0)
                        
                        {

                            DataSet work = new DataSet();
                            if( recordsReturned <= 1800)
                            {
                                logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);
                                collectionProcessingLog = GetProcessingLogCollection(invoiceBuildingList.CopyTo( ), adapter);

                                //Extract invoiceId, spid, max(dateAdded)

                                collectionProcessingLog.CreateHierarchicalProjection(work);
                                ds.Merge(work, true, MissingSchemaAction.AddWithKey);

                                logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);
                                collectionPerformanceLog = GetInvoiceActivityLog02Collection(invoiceCompletedList.CopyTo(), adapter);
                                collectionPerformanceLog.CreateHierarchicalProjection(work);
                                ds.Merge(work, true, MissingSchemaAction.AddWithKey);
                            
                                logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);
                                collectionInvoiceComponent = GetInvoiceComponentsCollection(invoiceCompletedList.CopyTo(), adapter);
                                collectionInvoiceComponent.CreateHierarchicalProjection(work);
                                ds.Merge(work, true, MissingSchemaAction.AddWithKey);
                            
                                logger.Trace("Perf:{0} {1}", ++j, (DateTime.Now - startRefreshDateTime).TotalMilliseconds);
                            }
                            else
                            {
                                //TODO: if invoice list count is too high >= 1800(?) switch to alternate method
                        
                                Debug.Assert(false );
                            }

But after I've fetched this parent collection and these child collections I need a way to merge them together so that the child entites are in the entity collections of the appropriate parent entities.

Hope I am making sense.

I'll add a research item to the v3 todo list for this. Thanks for the suggestion

So in the mean time, I have to merge them "by hand"?

arschr wrote:

So in the mean time, I have to merge them "by hand"?

Or use a prefetch path.

You COULD try with a derived class of DataAccessAdapter to utilize the MergeNormal and MergeManyToMany routines in the DataAccessAdapter class (the base class actually) which are protected virtual.

I'll look at those methods. Thanks