Hi,

I have this query:

SELECT DISTINCT Course.CourseId, Course.Title, Person_Course.DateTaken AS DateTaken
FROM         Role INNER JOIN
                      Person_Role ON Role.RoleId = Person_Role.RoleId INNER JOIN
                      Course INNER JOIN
                      Role_Course ON Course.CourseId = Role_Course.CourseId ON Role.RoleId = Role_Course.RoleId LEFT OUTER JOIN
                      Person_Course ON Course.CourseId = Person_Course.CourseId AND Person_Role.PersonId = Person_Course.PersonId
WHERE    (Person_Role.PersonId = 1)

Since there does not exist a real relation between Person_Course and Person_Role which is possible to make in the SQL, in LLBLGen I added the Person Entity. A Person must have a role (Inner Join) and might have taken course (Left Join).

So here is my code that is not correct:

public static DataTable FetchCoursesAndDateTakenByPersonId(int personID)

{
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(CourseFields.Id, 0, "CourseID");
fields.DefineField(CourseFields.Title, 1, "CourseTitle");
fields.DefineField(PersonCourseFields.DateTaken, 2, "DateTaken");

IRelationPredicateBucket bucket = new RelationPredicateBucket();

bucket.Relations.Add(PersonEntity.Relations.PersonCourseEntityUsingPersonId, JoinHint.Left);
bucket.Relations.Add(PersonEntity.Relations.PersonRoleEntityUsingPersonId, JoinHint.Inner);
bucket.Relations.Add(PersonRoleEntity.Relations.RoleEntityUsingRoleId, JoinHint.Inner);
bucket.Relations.Add(RoleEntity.Relations.RoleCourseEntityUsingRoleId, JoinHint.Inner);
bucket.Relations.Add(RoleCourseEntity.Relations.CourseEntityUsingCourseId, JoinHint.Inner);
bucket.Relations.Add(CourseEntity.Relations.PersonCourseEntityUsingCourseId, JoinHint.Left);

DataTable dynamicList = new DataTable();

bucket.PredicateExpression.Add(PersonFields.Id == personID);

bucket.PredicateExpression.Add();

ISortExpression sorter = new SortExpression(CourseFields.Title | SortOperator.Ascending);

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(fields, dynamicList, bucket, 0, sorter, false); 
}

return dynamicList;
}

I am missing the AND in the join in the query and thereby the results are not correct ...

Can anyone please help me construct the correct query?

Thanks in advance,

• G.I.

Edit: Can't edit the title anymore of this thread ... see now that it is a wrong title ...

Hi G.I.,

I just rewrote your SQL for better understanding:

SELECT DISTINCT 
     Course.CourseId, 
     Course.Title, 
     Person_Course.DateTaken AS DateTaken

FROM        
     Role 

     INNER JOIN Person_Role 
          ON Role.RoleId = Person_Role.RoleId 

     INNER JOIN Role_Course 
          ON Role.RoleId = Role_Course.RoleId 

     INNER JOIN Course 
          ON Role_Course.CourseId = Course.CourseId

     LEFT OUTER JOIN  Person_Course 
          ON Course.CourseId = Person_Course.CourseId 
          AND Person_Role.PersonId = Person_Course.PersonId

WHERE   
     (Person_Role.PersonId = 1)

So, I suggest this:

IRelationPredicateBucket bucket = new RelationPredicateBucket();

// INNER JOIN Person_Role ON Role.RoleId = Person_Role.RoleId 
bucket.Relations.Add(PersonRoleEntity.Relations.RoleEntityUsingRoleId, JoinHint.Inner);

// INNER JOIN Role_Course ON Role.RoleId = Role_Course.RoleId 
bucket.Relations.Add(RoleEntity.Relations.RoleCourseEntityUsingRoleId, JoinHint.Inner);

// INNER JOIN Course ON Role_Course.CourseId = Course.CourseId
bucket.Relations.Add(RoleCourseEntity.Relations.CourseEntityUsingCourseId, JoinHint.Inner);

// ----- THE CUSTOM RELATION FILTER-----

// LEFT OUTER JOIN  Person_Course ON Course.CourseId = Person_Course.CourseId 
EntityRelation customRelation = CourseEntity.Relations.PersonCourseEntityUsingCourseId;

// AND Person_Role.PersonId = Person_Course.PersonId
// specifying a custom filter will add the expression with an AND operand... 
// if you want to replace the original relation expression with this one, set  
// EntityRelation.CustomFilterReplacesOnClause flag to true.
customRelation.CustomFilter = new PredicateExpression(
     PersonRoleFields.PersonId== PersonCourseFields.PersonId);

// add the new relation to your bucket with a LEFT hint
bucket.Relations.Add(customRelation, JoinHint.Left);

// ... the rest of your code...

A short version of the custom filter code:

bucket.Relations.Add(CourseEntity.Relations.PersonCourseEntityUsingCourseId, JoinHint.Left)
     .CustomFilter = new PredicateExpression(PersonRoleFields.PersonId== PersonCourseFields.PersonId);

Ref: LLBLGenPro Help - Using generated code - Adapter - Filtering and Sorting - Advance filter usage - Custom filters for EntityRelations

Hope helpful.

Thank you very much! This was a very clear answer that provided the solution needed.