I am building a prefetch path using a relations collection (so I can sort on another table's column). I am specifying the relation in the relationcollection and am trying to use a left join, but there are only inner joins in the generated SQL.

Does the prefetch path objects ignore the join hints? Here is the code i am using:

'-- Create Prefetch and relation object Dim PrefetchPath As IPrefetchPath = New PrefetchPath(CType(EntityType.SbjctEntity, Integer)) Dim CCGCCGHeaderRelation As IRelationCollection = New RelationCollection

 '-- Create relation between CCG and CCGHeader for use in sorting CCG's by CCGHdr.hdrsortid
 CCGCCGHeaderRelation.Add(CcgEntity.Relations.CcghdrEntityUsingCcghdrId, JoinHint.Left)

 '-- First prefetch element will traverse the graph from subject to strand to ccg
    Dim pfElement As PrefetchPathElement = PrefetchPath.Add(SbjctEntity.PrefetchPathStrand, 0, StrandFilter, Nothing, StrandSortClause). _
         SubPath.Add(StrandEntity.PrefetchPathCcg, 0, CCGFilter, CCGCCGHeaderRelation, CCGSortClause)

    '-- Now we are at the ccg node in the graph, we add the ccgheader prefetch path
    pfElement.SubPath.Add(CcgEntity.PrefetchPathCcghdr, 0, Nothing, CCGCCGHeaderRelation, CCGHeaderSortClause)

    '-- Next we create the 2nd prefetch element that goes from ccg to standard
    Dim pfElement2 As PrefetchPathElement = pfElement.SubPath.Add(CcgEntity.PrefetchPathStndrd, 0, StandardFilter, Nothing, StandardSortClause)

    '-- Now we are at the standard node in the graph, so add prefetch paths for grade level and IO urls
 pfElement2.SubPath.Add(StndrdEntity.PrefetchPathMstrCd, 0, Nothing, Nothing)
    pfElement2.SubPath.Add(StndrdEntity.PrefetchPathInstrctObjectUrl, 0, InstructionalObjectFilter, Nothing, Nothing)

    '-- Fetch the data
    Subjects.GetMulti(SubjectFilter, 0, SubjectSortClause, Nothing, PrefetchPath)

You specify the relation collection twice, is that correct?

Anyway, the joinhint is set into the EntityRelation object which is then used to produce the joins, so the joinhint is always used. I'll create a test to see what happens exactly.

Otis wrote:

You specify the relation collection twice, is that correct?

I use the relation collection twice to insure that the same sort occurs on both entity collections (CCG and CCGHeader), but define it once. Is that what you mean?

For what its worth, the relationship between CCG and CCGHeader is a weak m:1. Is there anything that I can do to expidite the resolution on this, I (as usual) am under a tight deadline .

Found it. Will upload a fix for this later today.

A fix for this is now available. Please download the HOTFIX for the runtime libraries from the customer area -> runtime libraries section (4th item in the list, the hotfix contains the fix). Please let me know if you run into problems with this.

Note: don't be fooled by right joins if you expect left joins. The code sometimes uses right joins if that's required because the entities are switched. So A LEFT JOIN B becomes B RIGHT JOIN A, which is the same.

Thanks, it works great and the problem is solved