The PrefetchPathElement you to tried to add is already added to this PrefetchPath

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 16-Nov-2016 12:10:12   

Not an entirely unexpected exception simple_smile But what was trying to achieve is relative simple.

For one specific Path, I have a large list of SubPaths but I would like to be able to use different set of SubPaths depending on the object at the Path level.

I tried the following on the vague hope that Prefetch paths with a filter would be considered different and it would run similar queries at that Path level and merge the results.

                            .WithSubPath(LegalBodyEntity.PrefetchPathSourceAssociations.WithFilter(AssociationFields.RoleID == Roles.Accountant)
                                .WithSubPath(AssociationEntity.PrefetchPathTarget
                                ))

                            .WithSubPath(LegalBodyEntity.PrefetchPathSourceAssociations.WithFilter(AssociationFields.RoleID != Roles.Accountant)
                                .WithSubPath(AssociationEntity.PrefetchPathTarget
                                    .WithSubPath(TrustEntity.PrefetchPathOwnedAssets
                                            .WithSubPath(AssetEntity.PrefetchPathIncome)
                                            .WithSubPath(AssetEntity.PrefetchPathOutgoing)
                                            .WithSubPath(AssetEntity.PrefetchPathValuations)
                                            .WithSubPath(AssetEntity.PrefetchPathSubAssets)
                                            .WithSubPath(PolicyEntity.PrefetchPathPolicyAlterations)
                                            .WithSubPath(PolicyEntity.PrefetchPathPolicyType)
                                            .WithSubPath(PolicyEntity.PrefetchPathComponents
                                                .WithSubPath(ComponentLifeEntity.PrefetchPathLivesAssured)
                                                .WithSubPath(ComponentFundsEntity.PrefetchPathClientFunds
                                                    .WithSubPath(ClientFundEntity.PrefetchPathProviderFund
                                                        .WithSubPath(ProviderFundEntity.PrefetchPathValuations))
                                                    .WithSubPath(ClientFundEntity.PrefetchPathValuations)))
                                            .WithSubPath(AssetEntity.PrefetchPathOwnerships
                                                .WithSubPath(AssetToOwnerEntity.PrefetchPathLegalBody))
                                    )))

The table in question, Association, is very simple but has proved to be very powerful: int SourceLegalBodyID int TargetLegalBodyID short RoleID (for LegalBody, think Person or Company etc) So this table simply defines how one LegalBody is mapped to another.

In the above example, we want just the minimum information about a Client's Accountant for example, but lots of other information about a Company they own. My worry is that deep in the subpath list are some for Valuations - in theory there could be tens of thousands of these. And if the Accountant happens to also be a Client then they would be fetched back too even though we only want his name!

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 16-Nov-2016 16:43:42   

You will need to fetch these branches of similar root, separately. Then you will have to merge them at the client side.

P.S. That's a huge graph to fetch, are you sure it's needed at once? Or would it be better to fetch each branch and level per request (kind of lazy loading).

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 16-Nov-2016 18:52:45   

I really don't have the option to manually merge things as I use overloads to create and fetch single queries.

PS - the full query is much larger:-

                    var q = qf.PrivateClientFile
                            .Where(PrivateClientFileFields.ID == clientFileID)

                            // Need the adviser!
                            .WithPath(ClientFileEntity.PrefetchPathAdviser
                                .WithSubPath(UserEntity.PrefetchPathPerson))

                            .WithPath(ClientFileEntity.PrefetchPathFileAnswers
                                .WithSubPath(FileAnswerEntity.PrefetchPathFileQuestion))

                            // Bring back the file's Clients
                            .WithPath(PrivateClientFileEntity.PrefetchPathClients
                                    .WithOrdering(ClientFields.DisplayOrder.Ascending())
                                    // For each client

                                    // Bring back their home address
                                    .WithSubPath(LegalBodyEntity.PrefetchPathPrimaryAddress)

                                    // And any additional addresses
                                    .WithSubPath(LegalBodyEntity.PrefetchPathAdditionalAddresses
                                            .WithSubPath(AdditionalAddressEntity.PrefetchPathAddress)
                                    )

                                    // Trusts
                                    .WithSubPath(LegalBodyEntity.PrefetchPathSourceAssociations
                                        .WithSubPath(AssociationEntity.PrefetchPathTarget
                                            .WithSubPath(LegalBodyEntity.PrefetchPathOwnedAssets
                                                    .WithSubPath(AssetEntity.PrefetchPathIncome)
                                                    .WithSubPath(AssetEntity.PrefetchPathOutgoing)
                                                    .WithSubPath(AssetEntity.PrefetchPathValuations)
                                                    .WithSubPath(AssetEntity.PrefetchPathSubAssets)
                                                    .WithSubPath(PolicyEntity.PrefetchPathPolicyAlterations)
                                                    .WithSubPath(PolicyEntity.PrefetchPathPolicyType)
                                                    .WithSubPath(PolicyEntity.PrefetchPathComponents
                                                        .WithSubPath(ComponentEntity.PrefetchPathLivesAssured)
                                                        .WithSubPath(ComponentFundsEntity.PrefetchPathClientFunds
                                                            .WithSubPath(ClientFundEntity.PrefetchPathProviderFund
                                                                .WithSubPath(ProviderFundEntity.PrefetchPathValuations))
                                                            .WithSubPath(ClientFundEntity.PrefetchPathValuations)))
                                                    .WithSubPath(AssetEntity.PrefetchPathOwnerships
                                                        .WithSubPath(AssetToOwnerEntity.PrefetchPathLegalBody))
                                            )))

                                    // Incomes
                                    .WithSubPath(LegalBodyEntity.PrefetchPathIncomeOwnerships
                                            .WithSubPath(IncomeToOwnerEntity.PrefetchPathIncome
                                                    .WithSubPath(IncomeEntity.PrefetchPathAsset)
                                                    .WithSubPath(IncomeEntity.PrefetchPathOwnerships)
                                            )
                                    )

                                    // And their assets/asset alterations.ownerships
                                    .WithSubPath(LegalBodyEntity.PrefetchPathOwnedAssets
                                            .WithSubPath(AssetEntity.PrefetchPathIncome)
                                            .WithSubPath(AssetEntity.PrefetchPathOutgoing)
                                            .WithSubPath(AssetEntity.PrefetchPathValuations)
                                            .WithSubPath(AssetEntity.PrefetchPathSubAssets)

                                            .WithSubPath(MemberCorporateBenefitEntity.PrefetchPathCorporateBenefit)

                                            .WithSubPath(PolicyEntity.PrefetchPathPolicyAlterations)
                                            .WithSubPath(PolicyEntity.PrefetchPathPolicyType)
                                            .WithSubPath(PolicyEntity.PrefetchPathComponents
                                                .WithSubPath(ComponentFundsEntity.PrefetchPathClientFunds
                                                    .WithSubPath(ClientFundEntity.PrefetchPathProviderFund
                                                        .WithSubPath(ProviderFundEntity.PrefetchPathValuations))
                                                    .WithSubPath(ClientFundEntity.PrefetchPathValuations))
                                                .WithSubPath(ComponentEntity.PrefetchPathLivesAssured))

                                            .WithSubPath(AssetEntity.PrefetchPathOwnerships
                                                .WithSubPath(AssetToOwnerEntity.PrefetchPathLegalBody))
                                    )

                                    // Liabilities
                                    .WithSubPath(LegalBodyEntity.PrefetchPathOwnedLiabilities
                                        .WithSubPath(LiabilityEntity.PrefetchPathValuations)
                                        .WithSubPath(LiabilityEntity.PrefetchPathOutgoing)
                                        .WithSubPath(LiabilityEntity.PrefetchPathOwnerships
                                            .WithSubPath(LiabilityToOwnerEntity.PrefetchPathLegalBody)))

                                    // Outgoings
                                    .WithSubPath(LegalBodyEntity.PrefetchPathOutgoingOwnerships
                                            .WithSubPath(OutgoingToOwnerEntity.PrefetchPathOutgoing
                                                    .WithSubPath(OutgoingEntity.PrefetchPathLiability)
                                                    .WithSubPath(OutgoingEntity.PrefetchPathAsset)
                                            )
                                    )

                                    // And associations where they are the target (ie Accountant for <client>, Solicitor for <client>
                                    .WithSubPath(LegalBodyEntity.PrefetchPathTargetAssociations
                                    )

                                    .WithSubPath(LegalBodyEntity.PrefetchPathTags)

                                    .WithSubPath(LegalBodyEntity.PrefetchPathAssetOwnerships)

                                    .WithSubPath(ClientEntity.PrefetchPathATRCategories)
                            )
                            .WithPath(PrivateClientFileEntity.PrefetchPathDependents
                                .WithSubPath(DependentEntity.PrefetchPathPerson))

                            .WithPath(ClientFileEntity.PrefetchPathFactFindNotes)
                        ;

It all 'just works' and brilliantly well at that!

Resharper takes care of the formatting (retyping the semicolon indents everything correctly) and I am going to use PrefetchPathSets (mentioned in another ticket) to reuse common sets and reduce the height of the query.

Then LLBLGen fetches it all in just one call - and quickly too! LLCoolJ rocks sunglasses

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Nov-2016 13:09:57   

smile

I must say I'm rather surprised it performs well, not that I think our code is slow but that looks like a lot of queries (one per path node!) which might not always be the best performing setup wink

I see what you want to do, but that's unfortunately not supported: per path node a query is executed and the entities matching that query are added to the parent, and then the nodes below that path node are fetched.

WHat you could do is a trick: instead of filtering on accountant role, use that in a filter with a join in the subpath node, and add the AssociationEntity.PrefetchPathTarget node too, but with a filter so it will result in 0 rows if AssociationFields.RoleID != Roles.Accountant.

So in pseudocode:


.WithSubPath(LegalBodyEntity.PrefetchPathSourceAssociations
    .WithSubPath(AssociationEntity.PrefetchPathTarget
            // add filter here for AssociationFields.RoleID != Roles.Accountant so it will result in 0 rows if not true
        .WithSubPath(TrustEntity.PrefetchPathOwnedAssets
                .WithSubPath(AssetEntity.PrefetchPathIncome)
                .WithSubPath(AssetEntity.PrefetchPathOutgoing)
                .WithSubPath(AssetEntity.PrefetchPathValuations)
                .WithSubPath(AssetEntity.PrefetchPathSubAssets)
                .WithSubPath(PolicyEntity.PrefetchPathPolicyAlterations)
                .WithSubPath(PolicyEntity.PrefetchPathPolicyType)
                .WithSubPath(PolicyEntity.PrefetchPathComponents
                    .WithSubPath(ComponentLifeEntity.PrefetchPathLivesAssured)
                    .WithSubPath(ComponentFundsEntity.PrefetchPathClientFunds
                        .WithSubPath(ClientFundEntity.PrefetchPathProviderFund
                            .WithSubPath(ProviderFundEntity.PrefetchPathValuations))
                        .WithSubPath(ClientFundEntity.PrefetchPathValuations)))
                .WithSubPath(AssetEntity.PrefetchPathOwnerships
                    .WithSubPath(AssetToOwnerEntity.PrefetchPathLegalBody))
        ))
    .WithSubPath(AssociationEntity.PrefetchPathTarget
        // add filter here for AssociationFields.RoleID == Roles.Accountant, so it will result in 0 rows if not true
    ))

I think this will solve the problem simple_smile

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 17-Nov-2016 18:11:29   

I read your post and thought that exactly what I was doing just at one level higher.

Anyway I tried it with the code below and got the same error.

Have I missed something?

                            .WithSubPath(LegalBodyEntity.PrefetchPathSourceAssociations

                                    .WithSubPath(AssociationEntity.PrefetchPathTarget
                                        .WithFilter(AssociationFields.RoleID != Roles.Accountant)
                                        .WithSubPath(LegalBodyEntity.PrefetchPathOwnedAssets
                                                .WithSubPath(AssetEntity.PrefetchPathIncome)
                                                .WithSubPath(AssetEntity.PrefetchPathOutgoing)
                                                .WithSubPath(AssetEntity.PrefetchPathValuations)
                                                .WithSubPath(AssetEntity.PrefetchPathSubAssets)
                                                .WithSubPath(PolicyEntity.PrefetchPathPolicyAlterations)
                                                .WithSubPath(PolicyEntity.PrefetchPathPolicyType)
                                                .WithSubPath(PolicyEntity.PrefetchPathComponents
                                                    .WithSubPath(ComponentEntity.PrefetchPathLivesAssured)
                                                    .WithSubPath(ComponentFundsEntity.PrefetchPathClientFunds
                                                        .WithSubPath(ClientFundEntity.PrefetchPathProviderFund
                                                            .WithSubPath(ProviderFundEntity.PrefetchPathValuations))
                                                        .WithSubPath(ClientFundEntity.PrefetchPathValuations)))
                                                .WithSubPath(AssetEntity.PrefetchPathOwnerships
                                                    .WithSubPath(AssetToOwnerEntity.PrefetchPathLegalBody))
                                        ))

                                    .WithSubPath(AssociationEntity.PrefetchPathTarget
                                            .WithFilter(AssociationFields.RoleID == Roles.Accountant)
                                    )
                            )

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Nov-2016 19:17:39   

Ah my mistake.... I see the subpath node is still added twice.... disappointed Hmmm.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Nov-2016 19:20:18   

I think it's this:


    .WithSubPath(LegalBodyEntity.PrefetchPathSourceAssociations
            .WithSubPath(AssociationEntity.PrefetchPathTarget
                .WithSubPath(LegalBodyEntity.PrefetchPathOwnedAssets
                        // filter on AssociationFields.RoleID != Roles.Accountant
                        .WithSubPath(AssetEntity.PrefetchPathIncome)
                        .WithSubPath(AssetEntity.PrefetchPathOutgoing)
                        .WithSubPath(AssetEntity.PrefetchPathValuations)
                        .WithSubPath(AssetEntity.PrefetchPathSubAssets)
                        .WithSubPath(PolicyEntity.PrefetchPathPolicyAlterations)
                        .WithSubPath(PolicyEntity.PrefetchPathPolicyType)
                        .WithSubPath(PolicyEntity.PrefetchPathComponents
                            .WithSubPath(ComponentEntity.PrefetchPathLivesAssured)
                            .WithSubPath(ComponentFundsEntity.PrefetchPathClientFunds
                                .WithSubPath(ClientFundEntity.PrefetchPathProviderFund
                                    .WithSubPath(ProviderFundEntity.PrefetchPathValuations))
                                .WithSubPath(ClientFundEntity.PrefetchPathValuations)))
                        .WithSubPath(AssetEntity.PrefetchPathOwnerships
                            .WithSubPath(AssetToOwnerEntity.PrefetchPathLegalBody))
                ))
            )
    )

My idea was simply to let the subtree below 'Target' not result in any elements if the role is accountant, which is what you want I guess: the subtree only has child elements if the role isn't accountant this way, which is equal to adding two nodes, one without subtree nodes and one with subtree nodes and two complement filters.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 18-Nov-2016 08:16:48   

Is there a way to assign an Alias somehow so they 'appear' to be different nodes?

My code now looks like this, so WithSubPathSet gives me an opportunity to 'tweak' things if that helps.

                            .WithSubPath(LegalBodyEntity.PrefetchPathSourceAssociations
                                    .WithSubPathSet(AssociationEntity.PrefetchPathTarget, PathSetHelper.FullLegalBodySet())
                            )

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Nov-2016 09:53:45   

No, that's not possible. BUt think about it like this: you have two sets, A and B, which are merged together in the same set of parents. A has subnodes, B doesn't. This is equal to a set C where some elements have subnodes and some don't, based on a filter. C is merged with the same set of parents.

Like fetching Customers and their related orders and for each order of employee 2, the orderrows per order. You can do that by filtering on orderrows and make the filter become false (and thus not result in a row!) if the related order.employeeid != 2 simple_smile

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 18-Nov-2016 11:23:51   

Ah, your last comment makes sense now I read your previous one - should have refreshed before I posted this morning!

I may have mislead slightly by using the Roles.Account example. Your solution would probably work well for that sample scenario but not for what I eventually planned to extend it to. (Sorry about that)

What I ultimately want to achieve is to be able to specify a different sets of subpaths per filter - ie there may be more than one. In addition I want to specify a default set of subpaths for anything not matching any of the supplied filters - the default set as it were. The default set may be null.

Because I am using my WithSubPathSet() extension method, I can fiddle about so I believe it should be achievable as long as the caller makes sure the filters don't bring back the same entity.

I have got around the original issue so I can now add the same PrefetchPathSourceXXX multiple times (I inherited from PrefetchPathElement2 and now return false in the Equals overload).

So where normally, a query at a node only gets executed once, I will execute it multiple times (once per filter plus one which doesn't match any of the filters). Each result will then apply its own Subpath queries and the results from all get added to the parent.

If you can see a problem with any of this, please let me know!

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 18-Nov-2016 16:31:20   

I think I have something that works!

Here is my test code:

        static PrefetchPathSet ClientPathSet()
        {
            return new PrefetchPathSet(EntityType.ClientEntity)
                .WithPath(ClientEntity.PrefetchPathPrivateClientFile);
        }

        static PrefetchPathSet NonClientPathSet()
        {
            return new PrefetchPathSet(EntityType.LegalBodyEntity)
                .WithPath(LegalBodyEntity.PrefetchPathPrimaryAddress);
        }

        static void DoTestOptionalPathSets()
        {
            var query = new QueryFactory().Trust
                .Where(TrustFields.ID == 1299)
                .WithPath(LegalBodyEntity.PrefetchPathTargetAssociations
                        .WithSubPathSets(AssociationEntity.PrefetchPathSource/*.WithFilter(LegalBodyFields.ID != DBNull.Value)*/, NonClientPathSet(),
                            new FilteredPrefetchPathSet { Filter = ClientFields.ID != DBNull.Value, PrefetchPathSet = ClientPathSet() }
                        )
                );

            using (var adapter = new DataAccessAdapter())
            {
                var result = adapter.FetchSingle(query);

                var map = new ReferencedEntityMapAce(result);
            }
        }

This is new bit:

                        .WithSubPathSets(AssociationEntity.PrefetchPathSource/*.WithFilter(LegalBodyFields.ID != DBNull.Value)*/, NonClientPathSet(),
                            new FilteredPrefetchPathSet { Filter = ClientFields.ID != DBNull.Value, PrefetchPathSet = ClientPathSet() }
                        )

which is a AssociationEntity.PrefetchPathSource using NonClientPathSet() for subpaths by default but using ClientPathSet() for subpaths when ClientFiles.ID != DBNull.Value

and here is the code that does this:-

        public static IPrefetchPathElement2 WithSubPathSets(this IPrefetchPathElement2 query, IPrefetchPathElement2 parentNode, PrefetchPath2 defaultPrefetchPathSet,
            params FilteredPrefetchPathSet[] optionalPrefetchPathSets)
        {
            // Go through all optional Filter/PrefetchPathSet pairs
            foreach (var optionalPrefetchPathSet in optionalPrefetchPathSets)
            {
                // Take a copy of the Parent Node (Filter is not copied)
                var optionalParentNode = new NonMatchingPrefetchPathElement2(parentNode);

                // Copy any Filters on parent node into out optional Node (has to be a copy, not the original)
                foreach (var filterElement in parentNode.Filter.OfType<IPredicateExpressionElement>()
                    .Where(el => el.Type == PredicateExpressionElementType.Predicate))
                {
                    optionalParentNode.Filter.Add((IPredicate) filterElement.Contents);
                }

                // Add the option-specific filter
                optionalParentNode.Filter.Add(optionalPrefetchPathSet.Filter);

                // Extract the PrefetchPathSet tree
                foreach (IPrefetchPathElementCore rootElement in optionalPrefetchPathSet.PrefetchPathSet)
                {
                    optionalParentNode.WithSubPath(rootElement);
                }

                // And add it to the query
                query.WithSubPath(optionalParentNode);
            }

            // If we have a default PrefetchPathSet
            if (defaultPrefetchPathSet != null)
            {
                // Add the optional filters but Negated
                foreach (var optionalFilter in optionalPrefetchPathSets.Select(opps => opps.Filter))
                {
                    parentNode.Filter.AddWithAnd(new PredicateExpression(optionalFilter) { Negate = true });
                }

                // Extract the PrefetchPathSet trree
                foreach (IPrefetchPathElementCore rootElement in defaultPrefetchPathSet)
                {
                    parentNode.WithSubPath(rootElement);
                }

                // And add it to the query
                query.WithSubPath(parentNode);
            }

            return query;
        }

    public class FilteredPrefetchPathSet
    {
        public IPredicate Filter;
        public PrefetchPath2 PrefetchPathSet;
    }

    public class NonMatchingPrefetchPathElement2: PrefetchPathElement2
    {
        public NonMatchingPrefetchPathElement2(IPrefetchPathElement2 item): base(
            item.RetrievalCollection, item.Relation, item.DestinationEntityType, item.ToFetchEntityType, item.MaxAmountOfItemsToReturn,
            item.Sorter, null, item.FilterRelations, item.EntityFactoryToUse, item.PropertyName, item.TypeOfRelation
            )
        {}

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }

        // Ensure that this doesn't compare to anything else and so multiple versions can exist in the same list
        public override bool Equals(object obj)
        {
            return false;
        }
    }

I've tried it with and without a default pathset and ensured that any filter on AssociationEntity.PrefetchPathSource also gets applied to all optional sets too.

I'm going to try it with two FilteredPrefetchPathSets and a default but can you see any potential problems with the code so far?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Nov-2016 16:41:00   

I think this:

Because I am using my WithSubPathSet() extension method, I can fiddle about so I believe it should be achievable as long as the caller makes sure the filters don't bring back the same entity.

is indeed what is required. The algorithm is IMHO generic so you can apply it to whatever subnode set you want. WHether your code does that or not is too complex for me to see as I don't know your code/domain, but it's easy to test whether it works or not: first do it with a simple graph where you add the subnnodes based on filters and verify whether you indeed have the algorithm down. It's then relatively straight forward to apply to a complex tree as it is always the same idea.

parent .SubPath .Add(child .SubPath .Add(grandChild .where(child filter)))

The key is to filter grandchild on child's filter (using a join with child!). That's it. This is equal to (which isn't supported!):

parent .SubPath .Add(child .where(!child filter)) .Add(child .where(child filter X) .SubPath .Add(grandChild))

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 21-Nov-2016 11:14:36   

I think my solution is more like a switch rather than an if/else - ie any number of alternate path sets can be included.

If you can't see any problem with how I copy and negate filters, then I think it is done.

        public static IPrefetchPathElement2 WithAlternatePathSets(this IPrefetchPathElement2 query, IPrefetchPathElement2 topNode, PrefetchPath2 defaultPrefetchPathSet,
            params AlternatePrefetchPathSet[] alternatePrefetchPathSets)
        {
            // Go through all alternate Filter/PrefetchPathSet pairs
            foreach (var alternatePrefetchPathSet in alternatePrefetchPathSets)
            {
                // Take a copy of the Top node (Filter is not copied)
                var alternateTopNode = new NonMatchingPrefetchPathElement2(topNode);

                // Copy any Filters on Top node into our alternate Node (has to be a copy, not the original)
                foreach (var filterElement in topNode.Filter.OfType<IPredicateExpressionElement>()
                    .Where(el => el.Type == PredicateExpressionElementType.Predicate))
                {
                    alternateTopNode.Filter.Add((IPredicate) filterElement.Contents);
                }

                // Add the alternate filter
                alternateTopNode.Filter.Add(alternatePrefetchPathSet.Filter);

                // Extract the PrefetchPathSet tree into the query
                query.WithSubPath(ApplyPrefetchPathSet(alternateTopNode, alternatePrefetchPathSet.PrefetchPathSet));
            }

            // If we have a default PrefetchPathSet
            if (defaultPrefetchPathSet != null)
            {
                // Add the alternate filters but Negated
                foreach (var alternateFilter in alternatePrefetchPathSets.Select(opps => opps.Filter))
                {
                    topNode.Filter.AddWithAnd(new PredicateExpression(alternateFilter) { Negate = true });
                }

                // Extract the PrefetchPathSet tree into the query
                query.WithSubPath(ApplyPrefetchPathSet(topNode, defaultPrefetchPathSet));
            }

            return query;
        }

        static IPrefetchPathElement2 ApplyPrefetchPathSet(IPrefetchPathElement2 topNode, PrefetchPath2 prefetchPathSet)
        {
            if (prefetchPathSet == null) return topNode;

            foreach (IPrefetchPathElementCore pathElement in prefetchPathSet)
            {
                topNode.WithSubPath(pathElement);
            }

            return topNode;
        }

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Nov-2016 15:08:19   

As far as I can see it, no I don't see any objections. If this works, then go ahead simple_smile With prefetch paths the general thing to look out for is re-using actual path node instances across paths which are in flight simultaneously as they contain temporary fetch results before they're merged.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 22-Nov-2016 16:21:00   

So close, yet so far! cry

In your message of 17th Nov @ 19:20:18, you suggested "// filter on AssociationFields.RoleID != Roles.Accountant" but this filter refers to AssociationFields which are not in the immediate subpath but the one above that. How could this work? As far as I can see, it would fail because OwnedAssets are fetched and the filter would not be referring to fields in that fetch.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Nov-2016 19:06:23   

simmotech wrote:

So close, yet so far! cry

In your message of 17th Nov @ 19:20:18, you suggested "// filter on AssociationFields.RoleID != Roles.Accountant" but this filter refers to AssociationFields which are not in the immediate subpath but the one above that. How could this work? As far as I can see, it would fail because OwnedAssets are fetched and the filter would not be referring to fields in that fetch.

It could work if you join the association entity to it in that subpath node and then filter on that joined set simple_smile The inner join will filter out any elements in the subpath node due to this simple_smile

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 23-Nov-2016 08:20:54   

I understand what you but I wouldn't know how to do that.

What I finally decided was that the filters can only be applied to the 'top' level - easier to code and makes it clearer when reading. So if I want to filter on Associations then I do this:-

                            .WithAlternatePathSets(LegalBodyEntity.PrefetchPathSourceAssociations, null,
                                new AlternatePathSet(AssociationFields.RoleID == Roles.Beneficiary, AssociationEntity.PrefetchPathTarget, PathSetHelper.ForBeneficiaries()),
                                new AlternatePathSet(AssociationFields.RoleID == Roles.Trustee, AssociationEntity.PrefetchPathTarget, PathSetHelper.ForTrustees())
                            )

(might move the 2 x AssociationEntity.PrefetchPathTarget up to a single parameter in a WithAlternatePathSets overload)

and if I want to filter on the next level down do this:-

                .WithPath(LegalBodyEntity.PrefetchPathTargetAssociations
                        .WithAlternatePathSets(AssociationEntity.PrefetchPathSource, NonClientPathSet(),
                            new AlternatePathSet(ClientFields.ID != DBNull.Value, ClientPathSet())
                        )

Another thing I added for general use is an optional SubPaths extension method:

        public static TPathElement WithSubPathIf<TPathElement>(this TPathElement parentElement, bool condition, TPathElement subPathElement, params TPathElement[] otherSubPathElements)
            where TPathElement: IPrefetchPathElementCore
        {
            return condition
                ? parentElement.WithSubPath(subPathElement, otherSubPathElements)
                : parentElement;
        }

Now I can write common PathSets in a single place but customizable by the caller:-

        public static PrefetchPathSet ForOwnedAssets(bool includeSubAssets = true, bool includeIncomes = true, bool includeOutgoings = true,
            bool includeValuations = true, bool includePolicyAlterations = true, bool includePolicyComponentValuations = true)
        {
            return new PrefetchPathSet(EntityType.LegalBodyEntity)
                .WithSubPath(LegalBodyEntity.PrefetchPathOwnedAssets
                        .WithSubPathIf(includeIncomes, AssetEntity.PrefetchPathIncome)
                        .WithSubPathIf(includeOutgoings, AssetEntity.PrefetchPathOutgoing)
                        .WithSubPathIf(includeValuations, AssetEntity.PrefetchPathValuations)
                        .WithSubPathIf(includeSubAssets, AssetEntity.PrefetchPathSubAssets)
                        .WithSubPathIf(includePolicyAlterations, PolicyEntity.PrefetchPathPolicyAlterations)
                        .WithSubPath(PolicyEntity.PrefetchPathPolicyType)
                        .WithSubPath(PolicyEntity.PrefetchPathComponents
                                .WithSubPath(ComponentLifeEntity.PrefetchPathLivesAssured)
                                .WithSubPath(ComponentFundsEntity.PrefetchPathClientFunds
                                        .WithSubPath(ClientFundEntity.PrefetchPathProviderFund
                                                .WithSubPathIf(includePolicyComponentValuations, ProviderFundEntity.PrefetchPathValuations)
                                        )
                                        .WithSubPathIf(includePolicyComponentValuations, ClientFundEntity.PrefetchPathValuations)
                                )
                        )
                        .WithSubPath(AssetEntity.PrefetchPathOwnerships
                                .WithSubPath(AssetToOwnerEntity.PrefetchPathLegalBody)
                        )
                );
        }

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Nov-2016 10:45:07   

To add a join to a prefetch path node + filter, just use the WithJoins(from clause) method:

WithJoins(from clause). This method is equal to QuerySpec.From**(), where it allows one to specify a series of joins with the prefetch path node to be able to filter/sort on a related entity. The specified from clause likely always starts with QueryTarget. QueryTarget is in this case converted to the type of the prefetch path element's entity. Aliasing the prefetch path node isn't possible so using QueryTarget.As/TargetAs() is not recommended.

Frans Bouma | Lead developer LLBLGen Pro