Hi,

I have the following query to collect all the latest versions of all library items:

SELECT
*
FROM
LibraryItem LI1
JOIN
(
SELECT
MAX(Version) AS MaxVersion, RootLibraryItemId
FROM
LibraryItem
GROUP BY RootLibraryItemId
)
LI2
ON LI1.RootLibraryItemId = LI2.RootLibraryItemId
WHERE
LI1.Version = LI2.MaxVersion

Now I am trying to create a function that returns the records, preferably as an EntityCollection<LibraryEntity> and have been searching the forum and the manual on how to do this. So, now that I have grown a nice headache after an hour I decided to make a thread.

Could anyone tell me if A) this is possible B) give some hints on how to do this? Thanks in advance for the help.

I am using LLBLGen Pro 2.5 final (Adapter, C#) with SQL 2000.

Hi Marco,

There is no possible generate the predicate/relations in that way, however, I think the query could be rewritten like this:

SELECT *
FROM LibraryItem
WHERE 
    EXISTS 
    (   SELECT MAX(Version)
        FROM LibraryItem LI2
        GROUP BY RootLibraryItemId
        HAVING LibraryItem.RootLibraryItemId = LI2.RootLibraryItemId AND LibraryItem.Version = MAX(Version)
    )

Could you confirm that it retrieves the expected results? If so, you can use **FieldCompareSetPredicate **(please see LLBLGenPro Help - Using generated code - Adapter - Filtering and sorting - The predicate system - The predicate classes - FieldCompareSetPredicate).

Kindly regards,

Thanks David, that query returns exactly what I want, but looks a lot easier to do in LLBLGen Pro. I'll check the LLBLGen Pro help file how to create this query. Thanks again.

Marco

I've got it working now, so I'll post what my RelationPredicateBucket looks like if anyone else needs it.

relationPredicateBucket.Relations.Add(new EntityRelation(LibraryItemFields.LibraryItemId, LibraryItemFields.LibraryItemId, RelationType.OneToOne), "LI1", JoinHint.None);

GroupByCollection groupByCollection = new GroupByCollection(LibraryItemFields.RootLibraryItemId);
groupByCollection.HavingClause = new PredicateExpression();
groupByCollection.HavingClause.Add(LibraryItemFields.Version.SetObjectAlias("LI1") == LibraryItemFields.Version.SetAggregateFunction(AggregateFunction.Max));
groupByCollection.HavingClause.AddWithAnd(LibraryItemFields.RootLibraryItemId.SetObjectAlias("LI1") == LibraryItemFields.RootLibraryItemId);

relationPredicateBucket.PredicateExpression.Add(
    new FieldCompareSetPredicate(
        null, null, LibraryItemFields.RootLibraryItemId, null,
        SetOperator.Exist, null, null, "", 0, null, false, groupByCollection)
    );

Only thing I don't really like is the 1:1 relation I had to create to the same table, but I guess that's unavoidable. If in any way it can be avoided, please let me know.