Say I have the following structure:

Cal TableKey Caption

Link TableKey CalKey (FK: Cal.TableKey) ElementKey (FK: Element.TableKey) LinkType: String LinkSubType: String

Element TableKey Name

Now suppose for a given Cal record, I want to retrieve an ElementCollection of all "Element" records such that "LinkType = 'Member'" and "LinkSubType = 'Auth'"

How should I do this? Should I do it with "Prefetch" paths?

Is there a way to do it with GetMulti and a Filter?

I have spent all afternoon wading into this and all I have is a bunch of exceptions (which I'm sure are my fault... )

I found this post: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1921 and it helped some, but isn't exactly what I need.

BTW: I'm using SelfServicing and v1.x (but updating to v2.0 soon!)

Thanks greatly for any help.

Andrew.

If you don't want to fetch any of the Cal or Link entities (you only need to fetch a collection of Elements)

You should try to build the following query.

Select * from Element E 
Where E.TableKey IN 
(Select ElementKey from Link 
Where LinkType = 'Member' 
and LinkSubType = 'Auth'
and CalKey = xx)

And this can be built using the FieldCompareSetPredicate (refer to the manual)

On the other hand if you want to fetch the Cal entity along with the specific collection of Elements.

You should use a prefetchPath to Link with a subPath to Element, and add th (Where LinkType = 'Member' and LinkSubType = 'Auth') filter to the Link prefetchPath. There is an overload to the prefetchPath.Add() method that accepts a predicateExpression.

Walaa.

I ended up doing the following, which worked quite nicely.

I modified the entityInclude.template so the "GetMulti<MappedFieldNameRelation>" had an "intermediateFilter" as an optional paramter as follows:

Public Overridable Function GetMulti<[MappedFieldNameRelation]>(forceFetch As Boolean, 
entityFactoryToUse As IEntityFactory, 
Optional ByVal intermediateFilter As IPredicateExpression = Nothing) As <[RootNamespace]>.CollectionClasses.<[RelatedEntityName]>Collection

Then in the code I added the following:

' existing code:
        Filter.Add(New FieldCompareValuePredicate(EntityFieldFactory.Create(<[CurrentEntityName]>FieldIndex.<[EntityFieldName]>), ComparisonOperator.Equal, Me.<[EntityFieldName]>))<[NextForeach]>

' added code:
        ' Add Intermediate Filter, if it exists
        If (intermediateFilter IsNot Nothing) Then
            Filter.AddWithAnd(intermediateFilter)
        End If

This allows me to call GetMultiChildList(False, Filter) and filter on the "intermediate" table.

One thing to note: Since the "intermediate table" gets an alias of: "<IntermediateEntityName>_"

You have to specify an "object alias" for any filter Predicate's that are added to the Predicate Expression that is passed to the "filter". I'm not aware that you can specify "object aliases" for overloaded Predicate creation (possible in VB2005 and C#) so I'm not able to use that, but I can handle using the PredicateFactory to create my prediates In a later version, I'll may remove this requirement by "walking" the "intermediateFilter" and setting all the aliases of the "predicates" to the <IntermediateEntityName> in the GetMulti<> routine.

Personally, I would like to see this "intermediateFilter" capability added to the shipping product. I find myself many times needing to "filter" on that intermediate table (especially if it is a "link" or "junction" table) and whereas I can filter on 1:m relationships, I cannot filter on any m:n relationships.

Just my thoughts.

Andrew.

Filtering on intermediate tables was available from the start. As I have described it in my previous post.

I think I must have mis-stated something; I wasn't implying that there wasn't some way to filter on the intermediate table; as you have stated, with preFetch paths, almost anything is possible - I was trying to state that I thought filtering on "intermediate tables" should be added to the GetMulti<refName> routines, that is all.

Thank you very kindly for your explanation which provided another method for filtering on intermediate tables (actually, 2 more). I apologize if I implied I was not appreciative of your effort and input.

Andrew.