Hi all... I hope someone's awake that can help me with this... I've spent far too mcuh time trying to figure it out with not much to show...

Imagine the following table heirarchy: Districts -> Schools -> Teachers -> Classes -> Lesson Plans -> Standards

I want all districts, schools, teachers, classes, lesson plans, and standards where the lesson plans have a specified range of standards. That part isn't the problem. The problem is, I only want to return the standards from the specified range instead of ALL standards associated to the lesson plans returned.

I'll try to copy/paste the related code below.

        Dim districts As New EntityCollection(New DistrictEntityFactory())

        Dim districtsPath As New PrefetchPath2(EntityType.DistrictEntity)
        Dim schoolPath As SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPathElement2 = districtsPath.Add(DistrictEntity.PrefetchPathSchools)

        Dim userPath As SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPathElement2 = schoolPath.SubPath.Add(SchoolEntity.PrefetchPathUsers)

        Dim classPath As IPrefetchPathElement2 = userPath.SubPath.Add(UserEntity.PrefetchPathClasses)
        Dim coursePath As PrefetchPathElement2 = classPath.SubPath.Add(ClassEntity.PrefetchPathCourse)
        Dim gradePath As PrefetchPathElement2 = classPath.SubPath.Add(ClassEntity.PrefetchPathGrades)

        Dim lessonPath As SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPathElement2 = classPath.SubPath.Add(ClassEntity.PrefetchPathLessonPlans)

Dim standardsPath As PrefetchPathElement2 = lessonPath.SubPath.Add(LessonPlanEntity.PrefetchPathStandardsCollectionViaLessonStandard)


        Dim districtsBucket As New RelationPredicateBucket()

Dim standardsPredicate As New PredicateExpression()

' GetStandardCategoryPredicate returns a Range predicate full standardIds
standardsPredicate = GetStandardCategoryPredicate(categoryIds) 

            lessonPath.FilterRelations.Add(LessonPlanEntity.Relations.LessonStandardEntityUsingLessonPlanId)
            lessonPath.FilterRelations.Add(LessonStandardEntity.Relations.StandardEntityUsingStandardId)

lessonPath.Filter.Add(standardsPredicate)

standardsPath.Filter.Add(standardsPredicate)

The query that filters out the standards looks like this (cleaned up from Profile Analyzer)

 SELECT DISTINCT 
                      Standard.StandardId, Standard.StandardCategoryId, Standard.StandardTypeVersionId, Standard.Id, Standard.Objective, Standard.Subject, 
                      Standard.Deleted, Standard.Active, Standard.CreatedByUser, Standard.CreatedDate, Standard.ModifiedByUser, Standard.ModifiedDate
FROM         LessonPlan INNER JOIN
                      LessonStandard AS LPA_L1 ON LessonPlan.LessonPlanId = LPA_L1.LessonPlanId INNER JOIN
                      Standard ON Standard.StandardId = LPA_L1.StandardId
WHERE    (LPA_L1.LessonPlanId = 60) OR
                      (LPA_L1.LessonPlanId = 71) OR
                      (LPA_L1.LessonPlanId = 72) AND (Standard.StandardId IN (20821, 20822, 20823, 20824, 20825, 20826, 2........

If I run that query in Enterprise Manager without this..

  (LPA_L1.LessonPlanId = 60) OR
                      (LPA_L1.LessonPlanId = 71) OR
                      (LPA_L1.LessonPlanId = 72) AND

the correct standards returned... UNFORTUNATELY, I can't figure out how to make that happen with my LLBLGen objects...

Please help... I'm pretty much completely out of solutions...

The following manual query also works

 Standard ON Standard.StandardId = LPA_L1.StandardId
WHERE (LPA_L1.LessonPlanId IN ( 60, 71, 72)) AND (Standard.StandardId IN (20821, 20822, 20823, 20824, 20825, 20826, 2........

Notice the new IN clause instead of OR'ing 60, 71, and 72 together with the standard range as in the first post of this thread..

Again, my problem is that I can't get LLBLGen to change that part of the where clause.. it probably has something to do with where I'm placing the standardsPredicate the second time..

What in the world can I do to make this work?

Thanks again!

Bah.. I don't think what I want to do is possible with LLBLGen.. I'll wait 30 minutes for a reply before I rip the guts out of this part of my project and start over.

The IN range query is from the prefetch path fetch. I'm pretty sure of that. I don't see exactly why it fails though...

Otis wrote:

The IN range query is from the prefetch path fetch. I'm pretty sure of that. I don't see exactly why it fails though...

Here is a complete example of my problem. You should definitely be able to tell what I'm trying to do from this.

Module Module1

    Sub Main()
        Dim lessons As New SBLP.DAL.HelperClasses.EntityCollection(New SBLP.DAL.FactoryClasses.LessonPlanEntityFactory())
        Dim lessonsBucket As New SD.LLBLGen.Pro.ORMSupportClasses.RelationPredicateBucket()
        Dim lessonsPath As New SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPath2(SBLP.DAL.EntityType.LessonPlanEntity)
        Dim standardPath As SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPathElement2 _
            = lessonsPath.Add(SBLP.DAL.EntityClasses.LessonPlanEntity.PrefetchPathStandardsCollectionViaLessonStandard)

        Dim standardIds As New Generic.List(Of Integer)
        With standardIds
            .Add(20896)
            .Add(21335)
            .Add(22406)
            .Add(22924)
        End With

        Dim standardPredicate As New SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression()
        standardPredicate.Add(SBLP.DAL.FactoryClasses.PredicateFactory.CompareRange(SBLP.DAL.StandardFieldIndex.StandardId, standardIds))

        lessonsBucket.Relations.Add(SBLP.DAL.EntityClasses.LessonPlanEntity.Relations.LessonStandardEntityUsingLessonPlanId)
        lessonsBucket.Relations.Add(SBLP.DAL.EntityClasses.LessonStandardEntity.Relations.StandardEntityUsingStandardId)

        lessonsBucket.PredicateExpression.Add(standardPredicate)

        standardPath.Filter.Add(standardPredicate)

        Dim adapter As New SBLP.DAL.DatabaseSpecific.DataAccessAdapter()
        adapter.FetchEntityCollection(lessons, lessonsBucket, lessonsPath)

        For Each lesson As SBLP.DAL.EntityClasses.LessonPlanEntity In lessons
            Console.WriteLine("LessonID = " & lesson.LessonPlanId.ToString())
            For Each standard As SBLP.DAL.EntityClasses.StandardEntity In lesson.StandardsCollectionViaLessonStandard
                Console.WriteLine("StandardID = " & standard.StandardId.ToString())
            Next
            Console.WriteLine("")
        Next



    End Sub

End Module

The unexpected output is as follows:

LessonID = 60
StandardID = 21335

LessonID = 71
StandardID = 20896
StandardID = 21047
StandardID = 21434
StandardID = 22406

LessonID = 72
StandardID = 22924

Those LessonIds are correct.. but notice the extra StandardIds.. I only want the standards returned from the list above (20896, 21335, 22406, 22924).

I'm giong to go home now.. with any luck I'll have a solution waiting for me when I get to work in the morning.

ok here you go:

SELECT DISTINCT Standard.StandardId, Standard.StandardCategoryId, Standard.StandardTypeVersionId, Standard.Id, Standard.Objective, Standard.Subject, Standard.Deleted, Standard.Active, Standard.CreatedByUser, Standard.CreatedDate, Standard.ModifiedByUser, Standard.ModifiedDate FROM LessonPlan INNER JOIN LessonStandard AS LPA_L1 ON LessonPlan.LessonPlanId = LPA_L1.LessonPlanId INNER JOIN Standard ON Standard.StandardId = LPA_L1.StandardId WHERE (LPA_L1.LessonPlanId = 60) OR (LPA_L1.LessonPlanId = 71) OR (LPA_L1.LessonPlanId = 72) AND (Standard.StandardId IN (20821, 20822, 20823, 20824, 20825, 20826, 2........

I wonder where the 60, 71 & 72 conditions came from, nothing in code showed this !!!

Note:

Where Exp1 OR Exp2 OR Exp3 AND Exp4

is equal to

Where Exp1 OR Exp2 OR (Exp3 AND Exp4)

that's why the results were unexpected, and when you removed them or replaced them with the Anded IN expression it worked.

Walaa wrote:

I wonder where the 60, 71 & 72 conditions came from, nothing in code showed this !!!

Note:

Where Exp1 OR Exp2 OR Exp3 AND Exp4

is equal to

Where Exp1 OR Exp2 OR (Exp3 AND Exp4)

that's why the results were unexpected, and when you removed them or replaced them with the Anded IN expression it worked.

My guess is the DQE is putting that in there... which is why I think it's not possible to what I want directly with LLBLGen.