I'm not sure if this would be useful to others or if anyone else has a standard implementation they'd like to share (or point out something obvious I'm missing), but here's what I'd like to be able to do.

Given an esCollection, I'd like to be able to retrieve an associated collection by a collection (internally link via the PKs) instead of only being able to walk the hierarchy from an esEntity.

Heres some code:

' What I can do to return a single entity's related collection:
        collResource = New AgencyCalendarResourceCollection
        collResource.Query.Where(collResource.Query.AgencyCalendarResourceType.Equal(1))
        collResource.Query.Load()

        collEvent = collResource.FindByPrimaryKey(1).AgencyCalendarEventCollectionByAgencyCalendarResourceID


' What I'd like to do is have ES bring back a collection related to a collection, 
' where the WHERE clause can be used to determine the matching entities:
        collResource = New AgencyCalendarResourceCollection
        collResource.Query.Where(collResource.Query.AgencyCalendarResourceType.Equal("AgencyCar"))
        collResource.Query.Load()

        collEvent = collResource.AgencyCalendarEventCollectionByAgencyCalendarResourceCollection

With this implementation, I'd be able to easily bring back all the events for a resource by type (or any other filter) instead of having to use the dynamic query.

Any thoughts?
 

As a comparison, here's the code for the dynamic query:

        Dim qEvent As New AgencyCalendarEventQuery("qEvent")
        Dim qResource As New AgencyCalendarResourceQuery("qResource")
        qEvent.Select()
        qEvent.InnerJoin(qResource).On(qEvent.AgencyCalendarResourceID = qResource.AgencyCalendarResourceID)
        qEvent.Where(qResource.AgencyCalendarResourceType.Equal("AgencyCar"))
        collEvent = New AgencyCalendarEventCollection
        collEvent.Load(qEvent)

This isn't too bad, but the above is much less readable when skimming through in my mind vs the single line in the previous post.

We'll have to think about that one. From the server standpoint, this line is going to have to be broken down into an SQL join and where clause, just as we do with the your DynamicQuery:

collResource.Query.Where(collResource.Query.AgencyCalendarResourceType.Equal("AgencyCar"))

The question that gets raised in my mind is, "What kind of join?" I rarely use RIGHT and FULL joins, but I use about an equal number of LEFT and INNER joins. So, from that point of view, the single line may be more readable, but the DynamicQuery is more understandable, because it spells out the type of join.

I see your point.  I'm thinking however I might have posted this in the wrong section...at least for the goal I'm trying to accomplish. I'm just looking for a quick way to grab a related collection. I was not expecting the secondary collection to be linked to the initial collection in the ES hierarchy, although I can see why you had that thought from my message and this topic area.

My use would entail retrieving the initial collection without a join on the secondary table.  Then, the method to retrieve the related collection would act just as if I looped through the PKs of the first collection and used them in the WHERE clause as an IN([First Collection PKs]) statement.  There would be no join syntax needed. I suppose you could look at it as always being an INNER JOIN, but as I was thinking of using the collections apart from each other and not hierarchically bound, I really don't see the need for a join at all since you have a collection of PKs ready for the WHERE clause of the second collection.

I can see the benefits of keeping the hierarchy intact. Could there be something in the Dynamic Query codebase that could be applied to specify the join type as an option on the method to pull the related collection? IE: collResource.InnerJoin(collResource.AgencyCalendarCollectionByAgencyResourceCollection)?