Hi, we are using LLBLGen Pro 5.10 and trying to use the Rank window function functionality. The aim is to create a filter which retrieves only the highest versions of all available versions within a specific entity and security context.

For example, the database contains the following records in the Documenten table:

The DocID and DocRootDocID fields are of type GUID in our database, but for sake of simplicity I've changed them here to simple INTs.

In Project with ID 1 we only want to see documents with DocID 2 and 4, because these are the highest versions within there versionset (DocRootDocID).

In Project with ID 2 we only want to see document with DocID 5.

We borrowed the idea to use the Rank() and window function from your blog site (https://www.llblgen.com/blog/post.aspx?Id=4) to construct the following filter:

        private static void applyHighestVersionAndSecurityFilter(EntityType entityType, RelationPredicateBucket filter, RelationPredicateBucket contextFilter, Gebruiker gebruikerInstance)
        {
            // https://www.llblgen.com/Documentation/5.10/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_windowfunctions.htm
            // https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_derivedtabledynamicrelation.htm
            // https://www.llblgen.com/blog/post.aspx?Id=4
            var sorter = new SortExpression(DocumentFields.VersieNr | SortOperator.Descending) & (DocumentFields.InvoerDatum | SortOperator.Descending);

            var qf = new QueryFactory();
            var q = qf.Document
                        .Where(contextFilter.PredicateExpression).AndWhere(getSecurityFilter(entityType, gebruikerInstance, ""))
                        .Select(() => new
                        {
                            MaxVersieDocID = DocumentFields.ID.As("MaxVersieDocID").ToValue<Guid>(),
                            MaxVersie = WindowFunctions.Rank().Over(WindowPartition.By(DocumentFields.RootDocID), sorter).As("MaxVersie").ToValue<int>()
                        })
                        .From(contextFilter.Relations);

            var q2 = qf.Create().Select(new EntityField("MaxVersieDocID", "", typeof(Guid))).From(q).Where(new EntityField("MaxVersie", "", typeof(int)).Equal(1));

            var newFilter = new RelationPredicateBucket();

            if (entityType == EntityType.DocumentEntity)
            {
                newFilter.PredicateExpression.Add(DocumentFields.ID.In(q2));
            }
            else if (entityType == EntityType.StandaardprojectdocumentEntity)
            {
                newFilter.PredicateExpression.Add(StandaardprojectdocumentFields.DocID.In(q2));
            }
            filter.Add(newFilter);
        }

This generates the following SQL statement:

SELECT DISTINCT TOP(100) [dbo].[Documenten].[DocID] AS [ID], [dbo].[Documenten].[DocRootDocID] AS [RootDocID], [dbo].[Documenten].[DocNieuwsteVersie] AS [NieuwsteVersie], [dbo].[Documenten].[DocDocTypeID] AS [DocTypeID], [dbo].[Documenten].[DocDocStatusID] AS [DocStatusID], [dbo].[Documenten].[DocNumModID] AS [NumModID], [dbo].[Documenten].[DocRefModID] AS [RefModID], [dbo].[Documenten].[DocNr] AS [Nr], [dbo].[Documenten].[DocVersieNr] AS [VersieNr], [dbo].[Documenten].[DocExternNr] AS [ExternNr], [dbo].[Documenten].[DocExternVersieNr] AS [ExternVersieNr], [dbo].[Documenten].[DocOnderwerp] AS [Onderwerp], [dbo].[Documenten].[DocDatum] AS [Datum], [dbo].[Documenten].[DocDatumBinnenkomst] AS [DatumBinnenkomst], [dbo].[Documenten].[DocRelID] AS [RelID], [dbo].[Documenten].[DocRelatieTekst] AS [RelatieTekst], [dbo].[Documenten].[DocPersID] AS [PersID], [dbo].[Documenten].[DocPersoonTekst] AS [PersoonTekst], [dbo].[Documenten].[DocGebrID] AS [GebrID], [dbo].[Documenten].[DocGebruikerTekst] AS [GebruikerTekst], [dbo].[Documenten].[DocProgID] AS [ProgID], [dbo].[Documenten].[DocProgrammaTekst] AS [ProgrammaTekst], [dbo].[Documenten].[DocBestandID] AS [BestandID], [dbo].[Documenten].[DocProjID] AS [ProjID], [dbo].[Documenten].[DocProjectTekst] AS [ProjectTekst], [dbo].[Documenten].[DocIntBedrID] AS [IntBedrID], [dbo].[Documenten].[DocInternBedrijfTekst] AS [InternBedrijfTekst], [dbo].[Documenten].[DocIntAfdID] AS [IntAfdID], [dbo].[Documenten].[DocInterneAfdelingTekst] AS [InterneAfdelingTekst], [dbo].[Documenten].[DocIntVestID] AS [IntVestID], [dbo].[Documenten].[DocInterneVestigingTekst] AS [InterneVestigingTekst], [dbo].[Documenten].[DocRegProfID] AS [RegProfID], [dbo].[Documenten].[DocRegistratieProfielTekst] AS [RegistratieProfielTekst], [dbo].[Documenten].[DocOpslagProfID] AS [OpslagProfID], [dbo].[Documenten].[DocBouwBouwnrID] AS [BouwBouwnrID], [dbo].[Documenten].[DocBouwBouwnummerTekst] AS [BouwBouwnummerTekst], [dbo].[Documenten].[DocBouwKlachtID] AS [BouwKlachtID], [dbo].[Documenten].[DocBouwKlachtTekst] AS [BouwKlachtTekst], [dbo].[Documenten].[DocBouwProdID] AS [BouwProdID], [dbo].[Documenten].[DocBouwProductTekst] AS [BouwProductTekst], [dbo].[Documenten].[DocBouwInkoopContrID] AS [BouwInkoopContrID], [dbo].[Documenten].[DocBouwRContrID] AS [BouwRContrID], [dbo].[Documenten].[DocBouwOfferteAanvrID] AS [BouwOfferteAanvrID], [dbo].[Documenten].[DocBouwWerkorder] AS [BouwWerkorder], [dbo].[Documenten].[DocInternFactuurNr] AS [InternFactuurNr], [dbo].[Documenten].[DocAuteurs] AS [Auteurs], [dbo].[Documenten].[DocPlaatsArchief] AS [PlaatsArchief], [dbo].[Documenten].[DocAutomatischeBetaling] AS [AutomatischeBetaling], [dbo].[Documenten].[DocFactuurSoort] AS [FactuurSoort], [dbo].[Documenten].[DocMemo] AS [Memo], [dbo].[Documenten].[DocTag] AS [Tag], [dbo].[Documenten].[DocOpslagSysteemBijwerken] AS [OpslagSysteemBijwerken], [dbo].[Documenten].[DocInvoerDatum] AS [InvoerDatum], [dbo].[Documenten].[DocInvoerNaam] AS [InvoerNaam], [dbo].[Documenten].[DocWijzigDatum] AS [WijzigDatum], [dbo].[Documenten].[DocWijzigNaam] AS [WijzigNaam], [dbo].[Documenten].[DocBouwOfferteAanvrTekst] AS [BouwOfferteAanvrTekst], [dbo].[Documenten].[DocBouwInkoopContrTekst] AS [BouwInkoopContrTekst], [dbo].[Documenten].[DocBouwRContrTekst] AS [BouwRContrTekst], [dbo].[Documenten].[DocProjfaseID] AS [ProjfaseID], [dbo].[Documenten].[DocProjfaseTekst] AS [ProjfaseTekst], [dbo].[Documenten].[DocIsStandaardDoc] AS [IsStandaardDoc], [dbo].[Documenten].[DocStandaardDocID] AS [StandaardDocID], [dbo].[Documenten].[DocStandaardDocTekst] AS [StandaardDocTekst], [dbo].[Documenten].[DocVergrendeldDoorGebrID] AS [VergrendeldDoorGebrID], [dbo].[Documenten].[DocVergrendeldDoorGebruikerTekst] AS [VergrendeldDoorGebruikerTekst], [dbo].[Documenten].[DocVersiePrimair] AS [VersiePrimair], [dbo].[Documenten].[DocVersieSecundair] AS [VersieSecundair], [dbo].[Documenten].[DocDatumVergrendeld] AS [DatumVergrendeld], [dbo].[Documenten].[DocDatumVerwijderd] AS [DatumVerwijderd], [dbo].[Documenten].[DocRegProfCatID] AS [RegProfCatID], [dbo].[Documenten].[DocRegProfCatTekst] AS [RegProfCatTekst], [dbo].[Documenten].[DocFaseID] AS [FaseID], [dbo].[Documenten].[DocBouwBouwblokID] AS [BouwBouwblokID], [dbo].[Documenten].[DocBouwBouwblokTekst] AS [BouwBouwblokTekst], [dbo].[Documenten].[DocPaginas] AS [Paginas], [dbo].[Documenten].[DocLaatsteWijziging] AS [LaatsteWijziging], [dbo].[Documenten].[DocPositie] AS [Positie], [dbo].[Documenten].[DocVanPRKeyID] AS [VanPRKeyID], [dbo].[Documenten].[DocVanContrID] AS [VanContrID], [dbo].[Documenten].[DocVanRCPKeyID] AS [VanRCPKeyID], [dbo].[Documenten].[DocVanOmschr] AS [VanOmschr], [dbo].[Documenten].[DocDatumDefinitief] AS [DatumDefinitief], [dbo].[Documenten].[DocFormaat] AS [Formaat], [dbo].[Documenten].[DocInternNummer] AS [InternNummer], [dbo].[Documenten].[DocDatumIngediend] AS [DatumIngediend], [dbo].[Documenten].[DocDatumGoedgekeurd] AS [DatumGoedgekeurd], [dbo].[Documenten].[DocDatumGoedgekeurdConstructeur] AS [DatumGoedgekeurdConstructeur], [dbo].[Documenten].[DocDatumGoedgekeurdGemeente] AS [DatumGoedgekeurdGemeente], [dbo].[Documenten].[DocDatumGoedgekeurdBrandweer] AS [DatumGoedgekeurdBrandweer], [dbo].[Documenten].[DocDatumGoedgekeurdBouwDirectie] AS [DatumGoedgekeurdBouwDirectie], [dbo].[Documenten].[DocDatumGoedgekeurdNutsbedrijven] AS [DatumGoedgekeurdNutsbedrijven], [dbo].[Documenten].[DocDatumUiterlijkIndienen] AS [DatumUiterlijkIndienen], [dbo].[Documenten].[DocOceFinishing] AS [OceFinishing], [dbo].[Documenten].[DocIsStandaardDocInvulbaar] AS [IsStandaardDocInvulbaar], [dbo].[Documenten].[DocBeveiligingsniveauID] AS [BeveiligingsniveauID], [dbo].[Documenten].[DocHeeftUniekeMachtigingen] AS [HeeftUniekeMachtigingen], [dbo].[Documenten].[DocDocumentsoortID] AS [DocumentsoortID], [dbo].[Documenten].[DocDocumentsoortTekst] AS [DocumentsoortTekst], [dbo].[Documenten].[DocMachtigingcombinatieCombinatieID] AS [MachtigingcombinatieCombinatieID], [dbo].[Documenten].[DocBouwPPKeyID] AS [BouwPPKeyID], [dbo].[Documenten].[DocVanAfzenderID] AS [VanAfzenderID], [dbo].[Documenten].[DocClusteredIndex] AS [ClusteredIndex], [dbo].[Documenten].[DocTaalID] AS [TaalID], [dbo].[Documenten].[DocBoekingsperiode] AS [Boekingsperiode], [dbo].[Documenten].[DocExternID] AS [ExternID], [dbo].[Documenten].[DocSyncDatum] AS [SyncDatum], [dbo].[Documenten].[DocSyncNaam] AS [SyncNaam], [dbo].[Documenten].[DocSyncStatus] AS [SyncStatus], [dbo].[Documenten].[DocSyncOntkoppel] AS [SyncOntkoppel], [dbo].[Documenten].[DocSync] AS [Sync], [dbo].[Documenten].[DocVersieBevroren] AS [VersieBevroren], [dbo].[Documenten].[DocNaamVoluit] AS [NaamVoluit], [dbo].[Documenten].[DocArchiefStatus] AS [ArchiefStatus], [dbo].[Documenten].[DocDatumGearchiveerd] AS [DatumGearchiveerd], [dbo].[Documenten].[DocGearchiveerdReden] AS [GearchiveerdReden], [dbo].[Documenten].[DocMergetype] AS [Mergetype], [dbo].[Documenten].[DocIsExterneLink] AS [IsExterneLink], [dbo].[Documenten].[DocRetentieStartDatum] AS [RetentieStartDatum], [dbo].[Documenten].[DocRetentieactieVervalDatum] AS [RetentieactieVervalDatum], [dbo].[Documenten].[DocRetentieactieBevestigingsVervalDatum] AS [RetentieactieBevestigingsVervalDatum], [dbo].[Documenten].[DocRetentieactieVervallenAlarmDatum] AS [RetentieactieVervallenAlarmDatum], [dbo].[Documenten].[DocRetentieLaatsteReviewCommentaar] AS [RetentieLaatsteReviewCommentaar], [dbo].[Documenten].[DocRetentieLaatsteReviewDatum] AS [RetentieLaatsteReviewDatum], [dbo].[Documenten].[DocRetentieOverdrachtsDatum] AS [RetentieOverdrachtsDatum], [dbo].[Documenten].[DocRetentieactieID] AS [RetentieactieID], [dbo].[Documenten].[DocRetentieschemaID] AS [RetentieschemaID], [dbo].[Documenten].[DocRetentieWerkstroomID] AS [RetentieWerkstroomID], [dbo].[Documenten].[DocInitieleInvoerDatum] AS [InitieleInvoerDatum], [dbo].[Documenten].[DocDatumOverleg] AS [DatumOverleg], [dbo].[Documenten].[DocDatumAfgifte] AS [DatumAfgifte], [dbo].[Documenten].[DocDatumGeldigTot] AS [DatumGeldigTot], [dbo].[Documenten].[DocDatumLaatsteControle] AS [DatumLaatsteControle], [dbo].[Documenten].[DocCertGroepID] AS [CertGroepID], [dbo].[Documenten].[DocCertTypeID] AS [CertTypeID], [dbo].[Documenten].[DocVerwijderd] AS [Verwijderd], [dbo].[Documenten].[DocGearchiveerd] AS [Gearchiveerd], [dbo].[Documenten].[DocBouwWoningtypeTekst] AS [BouwWoningtypeTekst], [dbo].[Documenten].[DocBouwWoningtypeID] AS [BouwWoningtypeID], [dbo].[Documenten].[DocGlobalID] AS [GlobalID], [dbo].[Documenten].[DocVersieNrTekst] AS [VersieNrTekst], [dbo].[Documenten].[DocBouwVerdieping] AS [BouwVerdieping], [dbo].[Documenten].[DocGeolocatie] AS [Geolocatie], [dbo].[Documenten].[DocEMailMessageID] AS [EMailMessageID], [dbo].[Documenten].[DocAantalBijlagenInMail] AS [AantalBijlagenInMail], [dbo].[Documenten].[DocDossierID] AS [DossierID], [dbo].[Documenten].[DocDossierTekst] AS [DossierTekst], [dbo].[Documenten].[DocHash] AS [Hash], [dbo].[Documenten].[DocHashTypeTekst] AS [HashTypeTekst], [dbo].[Documenten].[DocIsStandaardDocUploadbaar] AS [IsStandaardDocUploadbaar], [dbo].[Documenten].[DocWijzigDatumGebruiker] AS [WijzigDatumGebruiker], [dbo].[Documenten].[DocWijzigNaamGebruiker] AS [WijzigNaamGebruiker], [dbo].[Documenten].[DocWijzigDatumSysteem] AS [WijzigDatumSysteem], [dbo].[Documenten].[DocWijzigNaamSysteem] AS [WijzigNaamSysteem], [dbo].[Documenten].[DocVerwijderdReden] AS [VerwijderdReden], [dbo].[Documenten].[DocClassificatieID] AS [ClassificatieID], [dbo].[Documenten].[DocDocGenDocumentID] AS [DocGenDocumentID], [dbo].[Documenten].[DocExportQueueID] AS [ExportQueueID], [dbo].[Documenten].[DocInitieleStampIDs] AS [InitieleStampIDs], [dbo].[Documenten].[DocEMailConversationID] AS [EMailConversationID], [dbo].[Documenten].[DocVrijVeldVervaldatum] AS [VrijVeldVervaldatum], [dbo].[Documenten].[DocVrijVeldTBIXchangeFilter] AS [VrijVeldTBIXchangeFilter], [dbo].[Documenten].[DocVrijVeldSHAHash] AS [VrijVeldSHAHash], [dbo].[Documenten].[DocVrijVeldValuta] AS [VrijVeldValuta], [dbo].[Documenten].[DocVrijVeldBTWVerlegd] AS [VrijVeldBTWVerlegd], [dbo].[Documenten].[DocVrijVeldZFactuurTotaalBedrag] AS [VrijVeldZFactuurTotaalBedrag], [dbo].[Documenten].[DocVrijVeldStatusExportEasy] AS [VrijVeldStatusExportEasy], [dbo].[Documenten].[DocVrijVeldDossiersoort] AS [VrijVeldDossiersoort], [dbo].[Documenten].[DocVrijVeldDossierDeel] AS [VrijVeldDossierDeel], [dbo].[Documenten].[DocVrijVeldExternID12Build] AS [VrijVeldExternID12Build], [dbo].[Documenten].[DocVrijVeldOnderwerpOpHuisstijlDoc] AS [VrijVeldOnderwerpOpHuisstijlDoc], [dbo].[Documenten].[DocVrijVeldGeboorteDatum] AS [VrijVeldGeboorteDatum], [dbo].[Documenten].[DocVrijveldTestRvdD] AS [VrijVeldTestRvdD], [dbo].[Documenten].[DocVrijVeldDigiBlokID] AS [VrijVeldDigiBlokID], [dbo].[Documenten].[DocVrijVeldAutomerkID] AS [VrijVeldAutomerkID], [dbo].[Documenten].[DocVrijVeldExterneSystemen] AS [VrijVeldExterneSystemen], [dbo].[Documenten].[DocVrijVeldVerzendwijze] AS [VrijVeldVerzendwijze], [dbo].[Documenten].[DocVrijVeldZFactuurBTWBedrag] AS [VrijVeldZFactuurBTWBedrag], [dbo].[Documenten].[DocVrijVeldZFactuurBTWCode] AS [VrijVeldZFactuurBTWCode], [dbo].[Documenten].[DocVrijVeldzFactuurBTWpercentage] AS [VrijVeldzFactuurBTWpercentage], [dbo].[Documenten].[DocVrijVeldRouteType] AS [VrijVeldRouteType], [dbo].[Documenten].[DocVrijVeldZijstapProcesID] AS [VrijVeldZijstapProcesID], [dbo].[Documenten].[DocVrijVeldExtraWerkstromen] AS [VrijVeldExtraWerkstromen] 
FROM (((((((((((((((((((((((([dbo].[CertificaatGroepen] 
INNER JOIN [dbo].[Documenten] ON [dbo].[CertificaatGroepen].[CertGroepID]=[dbo].[Documenten].[DocCertGroepID]) 
INNER JOIN [dbo].[CertificaatTypen] ON [dbo].[CertificaatTypen].[CertTypeID]=[dbo].[Documenten].[DocCertTypeID]) 
INNER JOIN [dbo].[DocTypen] ON [dbo].[DocTypen].[DocTypeID]=[dbo].[Documenten].[DocDocTypeID]) 
INNER JOIN [dbo].[DocStatus] ON [dbo].[DocStatus].[DocStatusID]=[dbo].[Documenten].[DocDocStatusID]) 
INNER JOIN [dbo].[RegistratieProfielen] ON [dbo].[RegistratieProfielen].[RegProfID]=[dbo].[Documenten].[DocRegProfID]) 
INNER JOIN [dbo].[Registratieprofielcategorieen] ON [dbo].[Registratieprofielcategorieen].[RegProfCatID]=[dbo].[Documenten].[DocRegProfCatID]) 
INNER JOIN [dbo].[Documentsoorten] ON [dbo].[Documentsoorten].[DocumentsoortID]=[dbo].[Documenten].[DocDocumentsoortID]) 
LEFT JOIN [dbo].[private_vw_DocumentenAggr] ON [dbo].[private_vw_DocumentenAggr].[ID]=[dbo].[Documenten].[DocID]) 
LEFT JOIN [dbo].[Gebruikers] ON [dbo].[Gebruikers].[GebrID]=[dbo].[Documenten].[DocGebrID]) 
INNER JOIN [dbo].[Classificaties] ON [dbo].[Classificaties].[ClassificatieID]=[dbo].[Documenten].[DocClassificatieID]) 
INNER JOIN [dbo].[InterneAfdelingen] ON [dbo].[InterneAfdelingen].[IntAfdID]=[dbo].[Documenten].[DocIntAfdID]) 
LEFT JOIN [dbo].[Projecten] ON [dbo].[Projecten].[ProjID]=[dbo].[Documenten].[DocProjID]) 
LEFT JOIN [dbo].[Projectfasen] ON [dbo].[Projectfasen].[ProjFaseID]=[dbo].[Documenten].[DocProjfaseID]) 
LEFT JOIN [dbo].[Relaties] ON [dbo].[Relaties].[RelID]=[dbo].[Documenten].[DocRelID]) 
LEFT JOIN [dbo].[Personen] ON [dbo].[Personen].[PersID]=[dbo].[Documenten].[DocPersID]) 
LEFT JOIN [dbo].[Bestanden] ON [dbo].[Bestanden].[BestandID]=[dbo].[Documenten].[DocBestandID]) 
INNER JOIN [dbo].[IMS_DocFases] ON [dbo].[IMS_DocFases].[DocFaseID]=[dbo].[Documenten].[DocFaseID]) 
INNER JOIN [dbo].[Beveiligingsniveaus] ON [dbo].[Beveiligingsniveaus].[BeveiligingsniveauID]=[dbo].[Documenten].[DocBeveiligingsniveauID]) 
LEFT JOIN [dbo].[Bouwnummers] ON [dbo].[Bouwnummers].[BouwnrID]=[dbo].[Documenten].[DocBouwBouwnrID]) 
LEFT JOIN [dbo].[Bouwblokken] ON [dbo].[Bouwblokken].[BouwblokID]=[dbo].[Documenten].[DocBouwBouwblokID]) 
LEFT JOIN [dbo].[Woningtypen] ON [dbo].[Woningtypen].[WoningtypeID]=[dbo].[Documenten].[DocBouwWoningtypeID]) 
LEFT JOIN [dbo].[Retentieacties] ON [dbo].[Retentieacties].[RetentieactieID]=[dbo].[Documenten].[DocRetentieactieID]) 
LEFT JOIN [dbo].[Retentieschemas] ON [dbo].[Retentieschemas].[RetentieschemaID]=[dbo].[Documenten].[DocRetentieschemaID]) 
LEFT JOIN [dbo].[Dossiers] ON [dbo].[Dossiers].[DossierID]=[dbo].[Documenten].[DocDossierID]) 
WHERE ( ( ( ( ( [dbo].[Documenten].[DocVerwijderd] = 0
AND ( [dbo].[Documenten].[DocID]
IN (
SELECT [MaxVersieDocID] 
FROM (
SELECT [dbo].[Documenten].[DocID] AS [MaxVersieDocID], OVER(PARTITION BY [dbo].[Documenten].[DocRootDocID] 
ORDER BY [dbo].[Documenten].[DocVersieNr] DESC, [dbo].[Documenten].[DocInvoerDatum] DESC) AS [MaxVersie] 
FROM [dbo].[Documenten] 
WHERE ( ( ( ( [dbo].[Documenten].[DocID]
IN (
SELECT [dbo].[private_vw_DocumentGekoppeldeProjecten].[DocID] 
FROM [dbo].[private_vw_DocumentGekoppeldeProjecten] 
WHERE ( [dbo].[private_vw_DocumentGekoppeldeProjecten].[ProjID] = 13639))))
AND [dbo].[Documenten].[DocMachtigingcombinatieCombinatieID]
IN (
SELECT [dbo].[private_vw_ObjectMachtigingen].[MachtigingcombinatieCombinatieID] 
FROM [dbo].[private_vw_ObjectMachtigingen] 
WHERE ( [dbo].[private_vw_ObjectMachtigingen].[GebrID] = 410
AND [dbo].[private_vw_ObjectMachtigingen].[SiteMapID] = 'f97e3fe9-9e54-401d-b4c1-4e2e486297e0'))))) [LPA_L1] 
WHERE ( ( [MaxVersie] = 1))))))))) 
ORDER BY [dbo].[Documenten].[DocDatum] DESC, [dbo].[Documenten].[DocClusteredIndex] DESC

FYI: All the unused joins in the main query are there for the case a user sorts or filters on one of the joined tables.

This is almost correct, but the RANK function is missing:

SELECT [dbo].[Documenten].[DocID] AS [MaxVersieDocID], OVER(PARTITION BY [dbo].[Documenten].[DocRootDocID] 

Should be:

SELECT [dbo].[Documenten].[DocID] AS [MaxVersieDocID], RANK() OVER(PARTITION BY [dbo].[Documenten].[DocRootDocID] 

As a workaround we now have the following solution by mixing low-level APIs and QuerySpec:

        private static void applyHighestVersionAndSecurityFilter(EntityType entityType, RelationPredicateBucket filter, RelationPredicateBucket contextFilter, Gebruiker gebruikerInstance)
        {
            // https://www.llblgen.com/Documentation/5.10/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_windowfunctions.htm
            // https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_derivedtabledynamicrelation.htm
            // https://www.llblgen.com/blog/post.aspx?Id=4
            var rankCall = new DbFunctionCall("RANK", null);
            var sorter = new SortExpression(DocumentFields.VersieNr | SortOperator.Descending) & (DocumentFields.InvoerDatum | SortOperator.Descending);

            var window = new WindowSpecification(WindowPartition.By(DocumentFields.RootDocID), sorter, null);

            var maxRank = DocumentFields.RootDocID;
            maxRank.ExpressionToApply = rankCall;
            maxRank.OverClauseToApply = window;

            var qf = new QueryFactory();
            var q = qf.Document
                        .Select(() => new
                        {
                            DocID = DocumentFields.ID.As("MaxVersieDocID").ToValue<Guid>(),
                            MaxVersie = maxRank.As("MaxVersie").ToValue<int>() // Voorbeeld code van LLBLGen Pro werkte niet: WindowFunctions.Rank().Over(WindowPartition.By(ProductFields.CategoryId), ProductFields.UnitPrice.Ascending()).As("ProductRankInCategory"));
                        })
                        .From(contextFilter.Relations)
                        .Where(contextFilter.PredicateExpression).AndWhere(getSecurityFilter(entityType, gebruikerInstance, ""));

            var q2 = qf.Create().Select(new EntityField("MaxVersieDocID", "", typeof(Guid))).From(q).Where(new EntityField("MaxVersie", "", typeof(int)).Equal(1));

            var newFilter = new RelationPredicateBucket();

            if (entityType == EntityType.DocumentEntity)
            {
                newFilter.PredicateExpression.Add(DocumentFields.ID.In(q2));
            }
            else if (entityType == EntityType.StandaardprojectdocumentEntity)
            {
                newFilter.PredicateExpression.Add(StandaardprojectdocumentFields.DocID.In(q2));

            }
            filter.Add(newFilter);
        }

Although the workaround is working fine, it is more complicated than using WindowFunctions.Rank() function.

Why is the Rank function missing and do you have suggestions to improve the code which builds the filter?

Which database type and version are you targeting?

The SQL server is: Microsoft SQL Server 2019 (RTM-CU22-GDR) (KB5029378 ) - 15.0.4326.1 (X64) Aug 18 2023 14:05:15 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

We'll look into it

Whatever I try, it always works here in our test.

[Test]
public void RankTest()
{
    var qf = new QueryFactory();
    var q = qf.ProductInventory
              .From(QueryTarget.InnerJoin(qf.Product)
                               .On(ProductInventoryFields.ProductId.Equal(ProductFields.ProductId)))
              .Select(() => new
                            {
                                ProductId = ProductFields.ProductId.ToValue<int>(),
                                Name = ProductFields.Name.ToValue<string>(),
                                LocationID = ProductInventoryFields.LocationId.ToValue<int>(),
                                Quantity = ProductInventoryFields.Qty.ToValue<short>(),
                                Rank = WindowFunctions.Rank().Over(WindowPartition.By(ProductInventoryFields.LocationId), ProductInventoryFields.Qty.Descending()).As("Rank").ToValue<int>()
                            })
              .Where(ProductInventoryFields.LocationId.Between(3, 4))
              .OrderBy(ProductInventoryFields.LocationId.Ascending(), qf.Field("Rank").Ascending());
    using(var adapter = new DataAccessAdapter())
    {
        var results = adapter.FetchQuery(q);
        Assert.AreEqual(10, results.Count);
        Assert.AreEqual(4, results[5].LocationID);
        Assert.AreEqual(35, results[5].Quantity);
        Assert.AreEqual(1, results[5].Rank);
    }
}

So looking into what WindowFunctions.Rank() does, it produces a FunctionMappingExpression. So my question is: do you use custom function mappings perhaps? Or otherwise override the default function mappings?

Whatever I try, I don't get the Rank function in the generated SQL. I've also tested it with RowNumber(), but this is not added to the generated SQL as well.

I notice the ToQueryText() methods in FunctionMappingExpression.cs. They return an empty string. Could this be the issue?

    //
    // Summary:
    //     Converts the expression to query text
    string IExpression.ToQueryText()
    {
        return string.Empty;
    }

    //
    // Summary:
    //     Converts the expression to query text
    //
    // Parameters:
    //   inHavingClause:
    //     if set to true [in having clause].
    string IExpression.ToQueryText(bool inHavingClause)
    {
        return string.Empty;
    }

FYI: This is the result of q2.ToString()

SELECT
    With projection: 
        Field: .MaxVersieDocID

FROM
    RelationCollection:
        DerivedTableDefinition: LPAA_1
            Field count: 2
            PredicateExpression:
                PredicateExpression:
                    PredicateExpression:
                        PredicateExpression:
                            Predicate:
                                FieldCompareSetPredicate:
                                    Field:
                                        Field: DocumentEntity.ID
                                    In
                                    Set field:
                                        Field: DocumentGekoppeldProjectEntity.DocID
                                        Predicate:
                                            FieldCompareValuePredicate:
                                                Field:
                                                    Field: DocumentGekoppeldProjectEntity.ProjID
                                                Equal
                                                    Constant: 195
                    And
                    Predicate:
                        FieldCompareSetPredicate:
                            Field:
                                Field: DocumentEntity.MachtigingcombinatieCombinatieID
                            In
                            Set field:
                                Field: ObjectMachtigingEntity.MachtigingcombinatieCombinatieID
                                PredicateExpression:
                                    Predicate:
                                        FieldCompareValuePredicate:
                                            Field:
                                                Field: ObjectMachtigingEntity.GebrID
                                            Equal
                                                Constant: 410
                                    And
                                    Predicate:
                                        FieldCompareValuePredicate:
                                            Field:
                                                Field: ObjectMachtigingEntity.SiteMapID
                                            Equal
                                                Constant: f97e3fe9-9e54-401d-b4c1-4e2e486297e0
            Field: DocumentEntity.ID As MaxVersieDocID
            Field: .ExF As Rank
                Expression: (FunctionMappingExpression)
                    Function mapping:
                        SD.LLBLGen.Pro.QuerySpec.WindowFunctions.Rank(0)
                Field: DocumentEntity.RootDocID
                SortExpression:
                    Field: DocumentEntity.VersieNr
                        Descending

WHERE
    PredicateExpression:
        PredicateExpression:
            Predicate:
                FieldCompareValuePredicate:
                    Field:
                        Field: .MaxVersie
                    Equal
                        Constant: 1

Additional information:
    Offset: 0
    Alias: ''
    WithTies: False
    CacheResultset: False

So my question is: do you use custom function mappings perhaps? Or otherwise override the default function mappings?

By my knowledge we didn't override or create custommappings. Can I search for some specific classes?

I've searched for 'FunctionMappingStore' and it appears only as a constructor in the generated LinqMetaData.cs:

public partial class LinqMetaData : ILinqMetaData
{
    /// <summary>CTor. Using this ctor will leave the transaction object to use empty. This is ok if you're not executing queries created with this
    /// meta data inside a transaction. If you're executing the queries created with this meta-data inside a transaction, either set the Transaction property
    /// on the IQueryable.Provider instance of the created LLBLGenProQuery object prior to execution or use the ctor which accepts a transaction object.</summary>
    public LinqMetaData() : this(null, null) { }
    
    /// <summary>CTor. If you're executing the queries created with this meta-data inside a transaction, pass a live ITransaction object to this ctor.</summary>
    /// <param name="transactionToUse">the transaction to use in queries created with this meta-data</param>
    /// <remarks> Be aware that the ITransaction object set via this property is kept alive by the LLBLGenProQuery objects created with this meta data
    /// till they go out of scope.</remarks>
    public LinqMetaData(ITransaction transactionToUse) : this(transactionToUse, null) { }
    
    /// <summary>CTor. If you're executing the queries created with this meta-data inside a transaction, pass a live ITransaction object to this ctor.</summary>
    /// <param name="transactionToUse">the transaction to use in queries created with this meta-data</param>
    /// <param name="customFunctionMappings">The custom function mappings to use. These take higher precedence than the ones in the DQE to use.</param>
    /// <remarks> Be aware that the ITransaction object set via this property is kept alive by the LLBLGenProQuery objects created with this meta data
    /// till they go out of scope.</remarks>
    public LinqMetaData(ITransaction transactionToUse, FunctionMappingStore customFunctionMappings)
    {
        this.TransactionToUse = transactionToUse;
        this.CustomFunctionMappings = customFunctionMappings;
    }

QuerySpec doesn't use LinqMetaData

The FunctionMappingExpression is removed using a visitor before the query is converted to sql: adapter.FetchQuery() calls query.PrepareForExecution, which does:

protected internal override void PrepareForExecution(FunctionMappingStore dqeMappings)
{
    base.PrepareForExecution(dqeMappings);
    this.Preprocess(dqeMappings);
    this.Prepare();
    new FunctionMappingExpressionReplacer(this.CustomFunctionMappingStore, dqeMappings).Traverse(this);
    // check if there are unioned queries. If so, these can't contain any nested queries. 
    if(this.UnionedQueries != null && this.UnionedQueries.Count > 0)
    {
        foreach(var e in this.UnionedQueries)
        {
            e.RepresentedElement.ValidateForUnion();
        }
    }
    this.OnPreparedForExecution();
}

So the FunctionMappingExpressionReplacer replaces the FunctionMappingExpressions with a real DbFunctionCall.

But as you showed a method which produces a filter the rest of the method is unknown. What we need is a minimal reproducible query, so a piece of code that reproduces the problem. As of now I can't see what it is caused by... the tree you show looks correct, it has the right fieldmappingexpression in place, so I'm not sure why it fails to be honest. However it can be because it's nested deeper than in our tests, there's perhaps a bug which stops the visitor from reaching the inner field to replace the FunctionMappingExpression... Without a reprocase it's not really doable to find it, I'm afraid.

I've removed the relations and context filters, but the issue remains.

Note that we use SelfServicing and you use Adapter, maybe that is the difference?

        [TestCategory(TestCategories.Integrationtest), TestMethod, Owner(TestOwners.RobinBouwmeester)]
        public void RankOverTest()
        {
            var filter = new RelationPredicateBucket();

            var qf = new QueryFactory();
            var q = qf.Document
                        .Select(() => new
                        {
                            MaxVersieDocID = DocumentFields.ID.As("MaxVersieDocID").ToValue<Guid>(),
                            MaxVersie = WindowFunctions.Rank().Over(WindowPartition.By(DocumentFields.RootDocID), DocumentFields.VersieNr.Descending()).As("Rank").ToValue<int>()
                        });

            var q2 = qf.Create().Select(new EntityField("MaxVersieDocID", "", typeof(Guid))).From(q);

            filter.PredicateExpression.Add(DocumentFields.ID.In(q2));

            var documents = new DocumentCollection();
            documents.GetMulti(filter);
        }

The resulting query is:

SELECT  [dbo].[Documenten].* 
FROM [dbo].[Documenten] 
WHERE ( [dbo].[Documenten].[DocID] IN 
(
  SELECT [MaxVersieDocID] 
  FROM (
    SELECT [dbo].[Documenten].[DocID] AS [MaxVersieDocID],  OVER(PARTITION BY [dbo].[Documenten].[DocRootDocID] ORDER BY [dbo].[Documenten].[DocVersieNr] 
    DESC) AS [Rank] 
    FROM [dbo].[Documenten]) [LPA_L1]
)
)

The RANK() function is still missing.

Alright, we'll look into it.

SanderF wrote:

        [TestCategory(TestCategories.Integrationtest), TestMethod, Owner(TestOwners.RobinBouwmeester)]
        public void RankOverTest()
        {
            var filter = new RelationPredicateBucket();

            var qf = new QueryFactory();
            var q = qf.Document
                        .Select(() => new
                        {
                            MaxVersieDocID = DocumentFields.ID.As("MaxVersieDocID").ToValue<Guid>(),
                            MaxVersie = WindowFunctions.Rank().Over(WindowPartition.By(DocumentFields.RootDocID), DocumentFields.VersieNr.Descending()).As("Rank").ToValue<int>()
                        });

            var q2 = qf.Create().Select(new EntityField("MaxVersieDocID", "", typeof(Guid))).From(q);

            filter.PredicateExpression.Add(DocumentFields.ID.In(q2));

            var documents = new DocumentCollection();
            documents.GetMulti(filter);
        }

This code doesn't compile for me as the GetMulti() call either receives a predicate expression (the GetMulti() method defined in the entity collection) or it receives an entity query (in case of the extension method defined by queryspec).

if I pass the predicate expression to the GetMulti() call, then the query crashes but indeed also shows there's no Rank().

This is understandable however. The issue is that the QuerySpec query has to be fetched with a queryspec fetch method: it's then prepared and handled so constructs like the FunctionMappingExpression is properly replaced by the right element. If you pass the element to a normal low-level API call like collection.GetMulti(predicate) then it will fail as there won't be any conversion taking place of queryspec elements.

I think the issue you run into is in that area.

This code doesn't compile for me as the GetMulti() call either receives a predicate expression (the GetMulti() method defined in the entity collection) or it receives an entity query (in case of the extension method defined by queryspec).

We have some overload GetMulti() methods in our generated CommonCollectionBase.cs

        public bool GetMulti(IRelationPredicateBucket filter, ISortExpression sort, IPrefetchPath prefetchPath)
        {
            if (filter == null)
                return GetMulti(null, 0, sort, null, prefetchPath);
            else
                return GetMulti(filter.PredicateExpression, 0, sort, filter.Relations, prefetchPath);
        }

        public bool GetMulti(IRelationPredicateBucket filter) => GetMulti(filter, null, null);

This is understandable however. The issue is that the QuerySpec query has to be fetched with a queryspec fetch method: it's then prepared and handled so constructs like the FunctionMappingExpression is properly replaced by the right element. If you pass the element to a normal low-level API call like collection.GetMulti(predicate) then it will fail as there won't be any conversion taking place of queryspec elements.

We use the low-level API GetMulti() everywhere in our application. Is there a method to prepare the QuerySpec construction? So we can use QuerySpec functionality as part of our filtering as shown in the original question.

All code needed for that is internal, you need to run:

protected internal override void PrepareForExecution(FunctionMappingStore dqeMappings)
{
    base.PrepareForExecution(dqeMappings);
    this.Preprocess(dqeMappings);
    this.Prepare();
    new FunctionMappingExpressionReplacer(this.CustomFunctionMappingStore, dqeMappings).Traverse(this);
    // check if there are unioned queries. If so, these can't contain any nested queries. 
    if(this.UnionedQueries != null && this.UnionedQueries.Count > 0)
    {
        foreach(var e in this.UnionedQueries)
        {
            e.RepresentedElement.ValidateForUnion();
        }
    }
    this.OnPreparedForExecution();
}

basically the FunctionMappingExpressionReplacer() in your case. The ToValue<T>() calls are also meaningless for you, as these are only used in the outer projection to produce a typed object. They're ignored in your query (as they're wrapped into a subquery).

Using QuerySpec constructs to produce predicates can work but you have to understand the implications of more complex constructs: they have to be handled. The FunctionMappingExpressionReplacer has an internal constructor so people won't use the type in their own code. So I'm afraid you have to either use reflection or use the lower-level api all the way. See the sourcecode archive from the website (My account -> downloads -> version -> Extras section).

I've tried to use the GetMulti() extension method which accepts the EntityQuery. The RANK is then visible in the generated SQL statement.

The function which calls the GetMulti() is used for all kinds of entitytypes, but the Queryspec needs a specific type.

And the line "q.WithPath(rpbp.PrefetchPath);" is not compatible, because rpbp.PrefetchPath is of type IPrefetchPath and the WithPath() wants an IPrefetchPathElementCore.

I can solve it for this specific case, but I would like a more generic approach.

                    var entityCollection = IDB.DigiOffice.DAO.Tools.GetEntityCollectionFromEntity(entityBase);
                    var rpbp = GetRelationPredicateBucketPrefetchPath(gridObject);
                    var sortExpression = GetSortExpression(entityBase, gridObject, rpbp.Bucket);

                    using (var trans = new Transaction(IsolationLevel.ReadUncommitted, "setDataSource"))
                    {
                        trans.Add(entityCollection);

                        if (entityCollection is DocumentCollection)
                        {
                            var qf = new QueryFactory();

                            var q = qf.Document.Where(rpbp.PredicateExpression).From(rpbp.Relations);

                            if (usePaging)
                            {
                                q.Page(gridObject.Page + 1, gridObject.PageSize);
                            }

                            q.OrderBy(sortExpression);
                            q.WithPath(rpbp.PrefetchPath);
                            entityCollection.GetMulti(q);
                        }
                        else
                        {
                            if (usePaging)
                                entityCollection.GetMulti(rpbp.Bucket.PredicateExpression, 0, sortExpression, rpbp.Relations, rpbp.PrefetchPath, gridObject.Page + 1, gridObject.PageSize);
                            else
                                entityCollection.GetMulti(rpbp.PredicateExpression, 0, sortExpression, rpbp.Relations, rpbp.PrefetchPath);
                        }
                        trans.Commit();
                    }

You're calling a method on a query with the wrong object, as in: it accepts prefetch path elements, because that's how it is intended to be used. If you want to add the elements in the path to the query, do:

foreach(var e in rpbp.PrefetchPath)
{
    q.WithPath(e);
}

You can also of course grab the first element and store the rest in an array and pass that as 2nd argument. You can do this in an extension method on EntityQuery<T>, like: public EntityQuery<TEntity> WithPath<TEntity>(this EntityQuery<TEntity> q, IPrefetchPath path) and in that method add the elements one by one. If you add that extension method I think all usages where you pass a prefetch path to WithPath() are then covered.

It's not that you have to go all-in when using queryspec, but you do have to make a choice what API you're going to use when fetching the data: a higher level API (linq/queryspec) or the low-level API. If it's the latter, any advanced features are likely not going to work as they're handled by linq/queryspec at their abstraction level, producing lower-level API elements.

Thanks for all the help.

I'll need to keep it with my initial workaround to make it work for us without getting me in too much trouble.

The ToValue<>() methods are required, otherwise, I get an ArgumentException with the message "No projection elements specified.".

The PrefetchPath needs to be cast before this can be added with WithPath():

foreach (var e in rpbp.PrefetchPath.Cast<IPrefetchPathElement>())
{
  q.WithPath(e);
}

SanderF wrote:

Thanks for all the help.

I'll need to keep it with my initial workaround to make it work for us without getting me in too much trouble.

The ToValue<>() methods are required, otherwise, I get an ArgumentException with the message "No projection elements specified.".

You can add the fields directly, without a lambda. If you specify a lambda, like you did in your version, then you have to specify ToValue to produce elements for the projection. If you use:

.Select(DocumentFields.ID.As("MaxVersieDocID"), 
        WindowFunctions.Rank().Over(WindowPartition.By(DocumentFields.RootDocID), sorter).As("MaxVersie"))

you don't need to specify them, as the fields for the projection are the ones you specify. This produces a List<object[]>, and in the case of your query, where you wrap the query in another query, you can use this without worrying about the value arrays (as you won't use them in your code). When the projection is the one of the outer query, you'd likely want to use the lambda to get a typed object.

The PrefetchPath needs to be cast before this can be added with WithPath():

foreach (var e in rpbp.PrefetchPath.Cast<IPrefetchPathElement>())
{
  q.WithPath(e);
}

Right, my bad. I checked 'PrefetchPath', which implements IList, but IPrefetchPath doesn't....