Hi all,

I've been using a very old version of llbgen (think it was the free version) that we expanded with some code. We are in the early stages of dropping this and moving over to the new version of llbgen pro. I already have a database in place with all application logic written, I'm trying to see if I can replace the old dal with a new one. One of my biggest concerns is that we use many complex sp's to generate overviews for users that can not be written with predicate expressions.

Can any one have a look at the following code example, it a bit long. I have no clue as to set the HistorieStart and HistorieEnd with predicates. A hint would be great!

Thanks for looking,

Hans

...
SELECT DISTINCT tblForm.int_FormID,
    vch_FormCallerName,
    vch_FormCallerRelationNr,
    vch_PropertyTitle,
    vch_FormStatusTitle,
    COALESCE(tbl_activity.dat_ActivityDate, HistorieStart.dat_FormHistoryDate) as dat_Created,
    int_PropertyConcernedID,
    (vch_UserFirstName + vch_UserMiddleName + vch_UserLastName) as vch_FullName,
    int_PropertyActivityID  
FROM tbl_Form AS tblForm
    INNER JOIN tbl_FormHistory as HistorieStart  with(nolock) ON HistorieStart.int_FormID = tblForm.int_FormID AND (HistorieStart.int_FormHistoryID IN                                      
 (SELECT MIN(int_FormHistoryID) AS int_FormHistoryID
   FROM       tbl_FormHistory
   WHERE bit_Isremarkonly = 0 AND tblform.int_FormID =                      tbl_FormHistory.int_FormID
                           GROUP BY int_FormID))
    INNER JOIN tbl_FormHistory as HistorieEnd  with(nolock) ON HistorieEnd.int_FormID = tblForm.int_FormID AND (HistorieEnd.int_FormHistoryID IN
                         (SELECT     MAX(int_FormHistoryID) AS int_FormHistoryID
                           FROM       tbl_FormHistory
                           WHERE bit_Isremarkonly = 0 AND tblform.int_FormID = tbl_FormHistory.int_FormID
                           GROUP BY int_FormID))
    INNER JOIN tbl_Property  with(nolock) ON HistorieEnd.int_PropertyDepartmentID = tbl_Property.int_PropertyID
    INNER JOIN tbl_FormStatus  with(nolock) On tbl_FormStatus.int_FormStatusID = HistorieEnd.int_PropertyStatusID
    INNER JOIN tbl_User  with(nolock) ON HistorieEnd.int_UserID = tbl_User.int_UserID
    INNER JOIN tbl_Activity  with(nolock) ON tblForm.int_ActivityID = tbl_Activity.int_ActivityID

WHERE HistorieStart.int_UserID = @int_UserID
    AND NOT  HistorieEnd.int_PropertyStatusID IN  (@int_StatusFinishedID,@int_StatusComplaintFinishedID)
    AND tblForm.int_FormTypeID IN (1,2)

...

You can set those predicates using: relationCollection.Add(relation).CustomFilter = yourPredicateExpression;

Please see: "Using the generated code -> Adapter/Selfservicing -> Filtering and sorting -> Custom filters for EntityRelations "

Group by in subqueries requires 1.0.2004.2.

I was getting close but had not found the custom filter though, thanks I try it