Hi,

I have a query that I would like to write that looks like this:

select * 
from 
    lms_internalcoursebookinghistory h
    join lms_internalcoursebooking b on h.internalcoursebookingid = b.internalcoursebookingid
where
    b.personid = 1
    and
    h.actiondate in
    (select max(h2.actiondate)
    from
        lms_internalcoursebookinghistory h2
        join lms_internalcoursebooking b2 on b2.internalcoursebookingid = h2.internalcoursebookingid
    where
        b2.personid = 1
    group by
        h2.internalcoursebookingid 
    )

after reading through other forum posts and the documentation I've come up with code :

IPredicateExpression subQuery = new PredicateExpression();
                subQuery.Add(PredicateFactory.CompareValue(InternalCourseBookingFieldIndex.PersonId, ComparisonOperator.Equal, personID, "h2"));
                IRelationCollection relations = new RelationCollection();
                relations.Add(InternalCourseBookingEntity.Relations.InternalCourseBookingHistoryEntityUsingInternalCourseBookingId, "h2");

                IEntityField2 actionDateInField = EntityFieldFactory.Create(InternalCourseBookingHistoryFieldIndex.ActionDate);

                IEntityField2 actionDateMaxField = EntityFieldFactory.Create(InternalCourseBookingHistoryFieldIndex.ActionDate);
                actionDateMaxField.Alias = "h2";

                IGroupByCollection groupBy = new GroupByCollection();
                groupBy.Add(InternalCourseBookingFields.InternalCourseBookingId);

                RelationPredicateBucket filter = new RelationPredicateBucket();
                filter.PredicateExpression.Add(
                    new FieldCompareSetPredicate(
                        actionDateInField,
                        null,
                        actionDateMaxField,
                        null,
                        SetOperator.In,
                        subQuery,
                        relations,
                        "h2",
                        100,
                        null,
                        false,
                        groupBy));
                filter.PredicateExpression.AddWithAnd(InternalCourseBookingFields.PersonId == personID);

Obviously there are joins on the outer query that I haven't yet written as I haven't been able to get the subquery right yet.

Where have I gone wrong?

Thanks, Matt

Hi,

Would you please post the generated SQL query from LLBLGen Pro traces to see what join is not generated? (In the manual : "Using the generated code -> Troubleshooting and debugging")

Hi,

The SQL is (is there a way to get it to format to a more readable version?):

SELECT [fjdata].[dbo].[LMS_InternalCourseBookingHistory].[BookingHistoryID] AS [BookingHistoryId], [fjdata].[dbo].[LMS_InternalCourseBookingHistory].[InternalCourseBookingID] AS [InternalCourseBookingId], [fjdata].[dbo].[LMS_InternalCourseBookingHistory].[ActioningPersonID] AS [ActioningPersonId], [fjdata].[dbo].[LMS_InternalCourseBookingHistory].[StatusID] AS [StatusId], [fjdata].[dbo].[LMS_InternalCourseBookingHistory].[ActionComment], [fjdata].[dbo].[LMS_InternalCourseBookingHistory].[ActionDate] FROM [fjdata].[dbo].[LMS_InternalCourseBookingHistory] WHERE ( ( [h2].[ActionDate] IN (SELECT DISTINCT TOP 100 [fjdata].[dbo].[LMS_InternalCourseBookingHistory].[ActionDate] AS [h2] FROM ( [fjdata].[dbo].[LMS_InternalCourseBooking] INNER JOIN [fjdata].[dbo].[LMS_InternalCourseBookingHistory] [LPA_h1] ON [fjdata].[dbo].[LMS_InternalCourseBooking].[InternalCourseBookingID]=[LPA_h1].[InternalCourseBookingID]) WHERE ( [h2].[PersonID] = @PersonId1) GROUP BY [fjdata].[dbo].[LMS_InternalCourseBooking].[InternalCourseBookingID]) AND [fjdata].[dbo].[LMS_InternalCourseBooking].[PersonID] = @PersonId2))

It's not so much a missing relationship as misnamed aliases I think.

Thanks again, Matt

First of all you don't need aliases.

select * 
from 
    lms_internalcoursebookinghistory h
    join lms_internalcoursebooking b on h.internalcoursebookingid = b.internalcoursebookingid
where
    b.personid = 1
    and
    h.actiondate in
    (select max(h2.actiondate)
    from
        lms_internalcoursebookinghistory h2
        join lms_internalcoursebooking b2 on b2.internalcoursebookingid = h2.internalcoursebookingid
    where
        b2.personid = 1
    group by
        h2.internalcoursebookingid 
    )

For the above query, please try the following code:

                IPredicateExpression subQuery = new PredicateExpression();
                subQuery.Add(InternalCourseBookingFields.PersonId == personID);

                IRelationCollection relations = new RelationCollection();
                relations.Add(InternalCourseBookingEntity.Relations.InternalCourseBookingHistoryEntityUsingInternalCourseBookingId);

                IGroupByCollection groupBy = new GroupByCollection();
                groupBy.Add(InternalCourseBookingFields.InternalCourseBookingId);

                RelationPredicateBucket filter = new RelationPredicateBucket();
                filter.PredicateExpression.Add(
                    new FieldCompareSetPredicate(
                        InternalCourseBookingFields.actionDate,
                        null,
                        InternalCourseBookingFields.actionDate.SetAggregateFunction(AggregateFuntion.Max),
                        null,
                        SetOperator.In,
                        subQuery,
                        relations,
                        "",
                        100,
                        null,
                        false,
                        groupBy));
                filter.PredicateExpression.AddWithAnd(InternalCourseBookingFields.PersonId == personID);