Having a problem with the following code:

    Public Shared Function GetAssignmentScoresByStudentIdent(ByVal studentIdent As Integer, ByVal startDate As Date, ByVal endDate As Date) As EntityCollection

        Dim Sorter As ISortExpression = New SortExpression

        Sorter.Add(SortClauseFactory.Create(AssignmentFieldIndex.SubjectAreaIdent, SortOperator.Ascending))
        Sorter.Add(SortClauseFactory.Create(AssignmentFieldIndex.DueDate, SortOperator.Descending))

        Dim Filter As IRelationPredicateBucket = New RelationPredicateBucket

        Filter.Relations.Add(AssignmentEntity.Relations.AssignmentSubSkillEntityUsingAssignmentIdent)
        Filter.Relations.Add(AssignmentSubSkillEntity.Relations.ScoreEntityUsingAssignmentSubSkillIdent)

        Filter.PredicateExpression.Add(PredicateFactory.CompareValue( MedfordSchoolDistrict.Elementary.GradeBook.LLBL.ScoreFieldIndex.StudentIdent, ComparisonOperator.Equal, studentIdent))

        Filter.PredicateExpression.AddWithAnd(PredicateFactory.Between( MedfordSchoolDistrict.Elementary.GradeBook.LLBL.AssignmentFieldIndex.DueDate, startDate, endDate))

        Dim PrefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.AssignmentEntity, Integer))

        PrefetchPath.Add(AssignmentEntity.PrefetchPathAssignmentSubSkill).SubPath.Add( _
                AssignmentSubSkillEntity.PrefetchPathScore, Nothing, Filter.PredicateExpression)

        GetAssignmentScoresByStudentIdent = New EntityCollection(New AssignmentEntityFactory)

        Using Adapter As New ElementaryDataAccessAdapter
            Adapter.FetchEntityCollection(GetAssignmentScoresByStudentIdent, Filter, Nothing, Sorter, PrefetchPath)
        End Using

        Return GetAssignmentScoresByStudentIdent

    End Function

I am getting the following error:

An exception was caught during the execution of a retrieval query: The column prefix 'ElementaryGradeBook.dbo.Assignment' does not match with a table name or alias name used in the query.
The column prefix 'ElementaryGradeBook.dbo.Assignment' does not match with a table name or alias name used in the query.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

If I removed this line

        Filter.PredicateExpression.AddWithAnd(PredicateFactory.Between( MedfordSchoolDistrict.Elementary.GradeBook.LLBL.AssignmentFieldIndex.DueDate, startDate, endDate))

It works fine. Any ideas would be helpfull.

Thanks, Fishy

Filter.PredicateExpression.Add(PredicateFactory.CompareValue( MedfordSchoolDistrict.Elementary.GradeBook.LLBL.ScoreFieldIndex.StudentIdent, ComparisonOperator.Equal, studentIdent))

        Filter.PredicateExpression.AddWithAnd(PredicateFactory.Between( MedfordSchoolDistrict.Elementary.GradeBook.LLBL.AssignmentFieldIndex.DueDate, startDate, endDate))

        Dim PrefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.AssignmentEntity, Integer))

        PrefetchPath.Add(AssignmentEntity.PrefetchPathAssignmentSubSkill).SubPath.Add( _
                AssignmentSubSkillEntity.PrefetchPathScore, Nothing, Filter.PredicateExpression)

It looks as though this may occur because you are using it in the context of the Score Prefetch. You should include a relation for this fetch that joins the AssignmentEntity with the ScoreEntity for the prefetch query.

Since I can't link Assignment and Score directly I have this relationship

Filter.Relations.Add(AssignmentEntity.Relations.AssignmentSubSkillEntityUsingAssignmentIdent)
Filter.Relations.Add(AssignmentSubSkillEntity.Relations.ScoreEntityUsingAssignmentSubSkillIdent)

Thinking that should link everything up.

So, I'm still not sure what I'm missing.

Thanks,

Fishy

Figured it out. I needed a separate filter for the subpath. I would have thought that I would get an sql error

New Code:

    Public Shared Function GetAssignmentScoresByStudentIdent(ByVal studentIdent As Integer, ByVal startDate As Date, ByVal endDate As Date) As EntityCollection

        Dim Sorter As ISortExpression = New SortExpression

        Sorter.Add(SortClauseFactory.Create(AssignmentFieldIndex.SubjectAreaIdent, SortOperator.Ascending))
        Sorter.Add(SortClauseFactory.Create(AssignmentFieldIndex.DueDate, SortOperator.Descending))

        Dim Filter As IRelationPredicateBucket = New RelationPredicateBucket

        Filter.Relations.Add(AssignmentEntity.Relations.AssignmentSubSkillEntityUsingAssignmentIdent)
        Filter.Relations.Add(AssignmentSubSkillEntity.Relations.ScoreEntityUsingAssignmentSubSkillIdent)

        Filter.PredicateExpression.Add(PredicateFactory.CompareValue(ScoreFieldIndex.StudentIdent, ComparisonOperator.Equal, studentIdent))

        Filter.PredicateExpression.AddWithAnd(PredicateFactory.Between(MedfordSchoolDistrict.Elementary.GradeBook.LLBL.AssignmentFieldIndex.DueDate, startDate, endDate))

        Dim FilterStudent As New PredicateExpression(PredicateFactory.CompareValue(ScoreFieldIndex.StudentIdent, ComparisonOperator.Equal, studentIdent))

        Dim PrefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.AssignmentEntity, Integer))

        PrefetchPath.Add(AssignmentEntity.PrefetchPathAssignmentSubSkill).SubPath.Add( _
                AssignmentSubSkillEntity.PrefetchPathScore, Nothing, FilterStudent)

        Dim Filter2 As New PredicateExpression(PredicateFactory.CompareValue(StudentAssignmentFieldIndex.StudentIdent, ComparisonOperator.Equal, studentIdent))
        Filter2.AddWithAnd(PredicateFactory.CompareValue(StudentAssignmentFieldIndex.PublishPalNote, ComparisonOperator.Equal, True))

        PrefetchPath.Add(AssignmentEntity.PrefetchPathStudentAssignment, Nothing, Filter2)

        GetAssignmentScoresByStudentIdent = New EntityCollection(New AssignmentEntityFactory)

        Using Adapter As New ElementaryDataAccessAdapter
            Adapter.FetchEntityCollection(GetAssignmentScoresByStudentIdent, Filter, Nothing, Sorter, PrefetchPath)
        End Using

        Return GetAssignmentScoresByStudentIdent

    End Function

thanks, fishy