Attached a zip with 3 files showing the relationships between my tables, llblgen generated sql and my code.

I dont now why llblgen ignores that last relationship instead of handling it properly. I did create the same diagram in sql server management studio and the generated sql was what i needed.

thanks

Could you please try this?

IRelationCollection relations = new RelationCollection
        {
            StudentEntity.Relations.StudentStatusEntityUsingStudentId,
            StudentEntity.Relations.StudentHistoryEntityUsingStudentId,
            StudentStatusEntity.Relations.AcademicYearEntityUsingAcademicYearId,
            AcademicYearEntity.Relations.AcademicTermEntityUsingAcademicYearId,
            AcademicTermEntity.Relations.StudentHistoryEntityUsingAcademicTermId,           
        };

Also, Is doing the relation something in the resulset? I mean, it's a Student 1:n StudentStatus, no fields of StudentStatus are in the select nor in the filter, so it seems it doesn't matter if it's generated or not, Right?

Tried it and no change at all.

I didnt send all fields and filters. StudentStatus is in the resultset.

I did solve my problem by adding a custom field like below but my question is, isnt llblgen supposed to create the proper sql without this custom filter in the first place.

Why would it ignore the last relationship between StudentStatus and Student?

IPredicateExpression customFilter = new PredicateExpression(StudentStatusFields.StudentId == StudentFields.UserId);

relations.Add(AcademicYearEntity.Relations.StudentStatusEntityUsingAcademicYearId).CustomFilter = customFilter;

daelmo wrote:

Could you please try this?

IRelationCollection relations = new RelationCollection
        {
            StudentEntity.Relations.StudentStatusEntityUsingStudentId,
            StudentEntity.Relations.StudentHistoryEntityUsingStudentId,
            StudentStatusEntity.Relations.AcademicYearEntityUsingAcademicYearId,
            AcademicYearEntity.Relations.AcademicTermEntityUsingAcademicYearId,
            AcademicTermEntity.Relations.StudentHistoryEntityUsingAcademicTermId,           
        };

Also, Is doing the relation something in the resulset? I mean, it's a Student 1:n StudentStatus, no fields of StudentStatus are in the select nor in the filter, so it seems it doesn't matter if it's generated or not, Right?

When the framework ignores one relations is because it's redundant, that means it doesn't make any difference for the results to include it or not. Your workaround is ok if you want to force it to be included.

But when i create the same relationship structure in sql management studio though a view it creates the correct sql for me.

Why cant llblgen do the same?

daelmo wrote:

When the framework ignores one relations is because it's redundant, that means it doesn't make any difference for the results to include it or not. Your workaround is ok if you want to force it to be included.

It skips relations if they're already in the join list. So if you do: Customer INNER JOIN Order Order INNER JOIN OrderDetails Customer INNER JOIN Order

it will skip the second Customer - Order, as that's already in the join list. If you want to have it in there, you have to alias the sides, so the entities joined are different (as they're aliased). Adding the same relationship twice isn't making things joined twice as the entity you're joining is already there.

I understand and it makes sense but in my case i dont think the relationship is already in the list. My relationships go like this:

              StudentEntity.Relations.StudentHistoryEntityUsingStudentId,
            StudentHistoryEntity.Relations.AcademicTermEntityUsingAcademicTermId,
            AcademicTermEntity.Relations.AcademicYearEntityUsingAcademicYearId,
            AcademicYearEntity.Relations.StudentStatusEntityUsingAcademicYearId,
            StudentStatusEntity.Relations.StudentEntityUsingStudentId //(this relation is ignored by LLBLGEN)

Otis wrote:

It skips relations if they're already in the join list. So if you do: Customer INNER JOIN Order Order INNER JOIN OrderDetails Customer INNER JOIN Order

it will skip the second Customer - Order, as that's already in the join list. If you want to have it in there, you have to alias the sides, so the entities joined are different (as they're aliased). Adding the same relationship twice isn't making things joined twice as the entity you're joining is already there.

        StudentEntity.Relations.StudentHistoryEntityUsingStudentId,
        StudentHistoryEntity.Relations.AcademicTermEntityUsingAcademicTermId,
        AcademicTermEntity.Relations.AcademicYearEntityUsingAcademicYearId,
        AcademicYearEntity.Relations.StudentStatusEntityUsingAcademicYearId,
        StudentStatusEntity.Relations.StudentEntityUsingStudentId //(this relation is ignored by LLBLGEN)

I have to disagree.

The point is The last Join, is Joining on the StudentEntity, which is already there from the start. You start the relations by a Student, then you get its History, AcademicTerms and Yeras, then His Status in these Years, Then you try to Join Again with the Student, which is redundant and adds no value.

SQL Management Studio can't figure this out, but LLBLGen Pro can.

Actually SQL Management Studio creates the correct sql instead of ignoring the relationship. llblgen simply ignores the relationship and expects user to create a custom filter so it could create the correct sql statement.

My initial point was, why cant llblgen do the same? here is what sql management studio gives me after adding mentioned tables in the View designer:

SELECT   Core.Student.StudentID
FROM         Core.Student INNER JOIN
                      Core.StudentHistory ON Core.Student.StudentID = Core.StudentHistory.StudentID INNER JOIN
                      Core.AcademicTerm ON Core.StudentHistory.AcademicTermID = Core.AcademicTerm.AcademicTermID INNER JOIN
                      Core.AcademicYear ON Core.AcademicTerm.AcademicYearID = Core.AcademicYear.AcademicYearID INNER JOIN
                      Core.StudentStatus ON Core.Student.StudentID = Core.StudentStatus.StudentID AND Core.AcademicYear.AcademicYearID = Core.StudentStatus.AcademicYearID

Walaa wrote:

        StudentEntity.Relations.StudentHistoryEntityUsingStudentId,
        StudentHistoryEntity.Relations.AcademicTermEntityUsingAcademicTermId,
        AcademicTermEntity.Relations.AcademicYearEntityUsingAcademicYearId,
        AcademicYearEntity.Relations.StudentStatusEntityUsingAcademicYearId,
        StudentStatusEntity.Relations.StudentEntityUsingStudentId //(this relation is ignored by LLBLGEN)

I have to disagree.

The point is The last Join, is Joining on the StudentEntity, which is already there from the start. You start the relations by a Student, then you get its History, AcademicTerms and Yeras, then His Status in these Years, Then you try to Join Again with the Student, which is redundant and adds no value.

SQL Management Studio can't figure this out, but LLBLGen Pro can.

e106199 wrote:

Actually SQL Management Studio creates the correct sql instead of ignoring the relationship. llblgen simply ignores the relationship and expects user to create a custom filter so it could create the correct sql statement.

My initial point was, why cant llblgen do the same?

As the join is redundant and only causes the DB to do more work for no additional gain. If you want 'Student' to be joined again in the last relationship you have to specify an alias, as your current code simply specifies the same entity.

The problem gets more visible when you add a filter for Student: which entity should be filtered?

here is what sql management studio gives me after adding mentioned tables in the View designer:

SELECT   Core.Student.StudentID
FROM         Core.Student INNER JOIN
                      Core.StudentHistory ON Core.Student.StudentID = Core.StudentHistory.StudentID INNER JOIN
                      Core.AcademicTerm ON Core.StudentHistory.AcademicTermID = Core.AcademicTerm.AcademicTermID INNER JOIN
                      Core.AcademicYear ON Core.AcademicTerm.AcademicYearID = Core.AcademicYear.AcademicYearID INNER JOIN
                      Core.StudentStatus ON Core.Student.StudentID = Core.StudentStatus.StudentID AND Core.AcademicYear.AcademicYearID = Core.StudentStatus.AcademicYearID

Ah, but that's not the same. You specified: AcademicYearEntity.Relations.StudentStatusEntityUsingAcademicYearId, which means studentstatus is joined to AcademicYear. So the ON clause for that join will only contain the predicate between academicyear and studentstatus. If you want to add an additional predicate to that particular join, specify a CustomFilter.

Agreed, the code could theoretically try to find the entity you already joined and add the ON clause to the previous one, but that's not always what the user wants: sometimes the developer adds, accidentally, redundant relationships, and it's then a question what to do, we chose to ignore them.

Frans, i get your point, if the same relationship repeats it is logical to ignore the second one, but i dont repeat the same relationship here. It may be the same tables reaching at each other (student reaches at studentstatus through studenthistory, academicterm, academicyear AND student reaches studentstatus directly through the last relationship) but again they are not the same relationships. If i had:

StudentEntity.Relations.StudentStatusEntityUsingStudentId,
StudentEntity.Relations.StudentHistoryEntityUsingStudentId,
StudentHistoryEntity.Relations.AcademicTermEntityUsingAcademicTermId,
AcademicTermEntity.Relations.AcademicYearEntityUsingAcademicYearId
AcademicYearEntity.Relations.StudentStatusEntityUsingAcademicYearId,
StudentStatusEntity.Relations.StudentEntityUsingStudentId

yes ignore the last line which is a repetition of the first line but mine is not like that.

**Can it be like: - ignore it if it is the same relationship! - add the ON clause to the previous one if it is not. **

Otis wrote:

here is what sql management studio gives me after adding mentioned tables in the View designer:

SELECT   Core.Student.StudentID
FROM         Core.Student INNER JOIN
                      Core.StudentHistory ON Core.Student.StudentID = Core.StudentHistory.StudentID INNER JOIN
                      Core.AcademicTerm ON Core.StudentHistory.AcademicTermID = Core.AcademicTerm.AcademicTermID INNER JOIN
                      Core.AcademicYear ON Core.AcademicTerm.AcademicYearID = Core.AcademicYear.AcademicYearID INNER JOIN
                      Core.StudentStatus ON Core.Student.StudentID = Core.StudentStatus.StudentID AND Core.AcademicYear.AcademicYearID = Core.StudentStatus.AcademicYearID

Ah, but that's not the same. You specified: AcademicYearEntity.Relations.StudentStatusEntityUsingAcademicYearId, which means studentstatus is joined to AcademicYear. So the ON clause for that join will only contain the predicate between academicyear and studentstatus. If you want to add an additional predicate to that particular join, specify a CustomFilter.

Agreed, the code could theoretically try to find the entity you already joined and add the ON clause to the previous one, but that's not always what the user wants: sometimes the developer adds, accidentally, redundant relationships, and it's then a question what to do, we chose to ignore them.

Fair point. You know, in 9 years you're the first to bring this up but nevertheless, you do have a point.

I'll add it to the list of things to look at for v4, we can't add this to the current framework as it would cause queries to change and we have to analyze what the impact will be.

Maybe i dont have the best data model but i remember this happening more than once. Thanks for adding it into your list.

-shane

Otis wrote:

Fair point. You know, in 9 years you're the first to bring this up but nevertheless, you do have a point.

I'll add it to the list of things to look at for v4, we can't add this to the current framework as it would cause queries to change and we have to analyze what the impact will be.