Since upgrading to V5.10, we see a “ORDER BY items must appear in the select list if SELECT DISTINCT is specified” exception in the LLBLGEN generated SQL. Note, this occurs on the second loop of a paged fetch.

using (BengalDataAccessAdapter adapter = new BengalDataAccessAdapter())
{
    EntityCollection<AssetTagEntity> tempColln;
    int m_nPageSize = 25;

    RelationPredicateBucket filter = new RelationPredicateBucket();
    IPredicateExpression tagTypePredExp = new PredicateExpression();
    tagTypePredExp.Add(AssetTagFields.TagTypeId == TagType);
    filter.PredicateExpression.AddWithAnd(tagTypePredExp);
    filter.Relations.Add(AssetTagEntity.Relations.StationSpecificAssetTagEntityUsingAssetTagId);

    IPredicateExpression stationIDPred = new PredicateExpression();
    stationIDPred.Add(StationSpecificAssetTagFields.StationId == m_stationId);
    filter.PredicateExpression.AddWithAnd(stationIDPred);

    AssetTagsTotal = (int)adapter.GetDbCount(new AssetTagEntityFactory().CreateFields(), filter);
    AssetTagsFetched = 0;
    int nPageCount = AssetTagsTotal / m_nPageSize + ((AssetTagsTotal % m_nPageSize == 0) ? 0 : 1);

    PrefetchPath2 prefetch = new PrefetchPath2(Zetta.Dal.EntityType.AssetTagEntity);
    SortExpression assetTagSort = new SortExpression(StationSpecificAssetTagFields.Sequence | SortOperator.Ascending);
    IPrefetchPathElement2 a2tPrefetch = prefetch.Add(AssetTagEntity.PrefetchPathStationSpecificAssetTagCollection, 0, null, null, assetTagSort);
    a2tPrefetch.SubPath.Add(StationSpecificAssetTagEntity.PrefetchPathAsset);
    a2tPrefetch.SubPath.Add(StationSpecificAssetTagEntity.PrefetchPathStation);

    // Notify that fetching has started
    IsFetching = true;

    for (int i = 0; i < nPageCount; i++)
    {
        if (m_FetchThread.IsCancelled)
            break;

        tempColln = new EntityCollection<AssetTagEntity>();
        adapter.FetchEntityCollection(tempColln, filter, 0, null, prefetch, i + 1, m_nPageSize);

        AssetTagsFetched += tempColln.Count;
        //AddAssetTagsToCollection(tempColln.ToList());
        tempColln.Clear();
    }
}

Here is the generated SQL before the upgrade (note, the @p parameters have been replaced by the values):

WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM (SELECT DISTINCT TOP(50) [ZettaDB_5_22_1].[dbo].[AssetTag].[AssetTagId],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[EraseDate],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[ExternalId],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[infoCreatedDate] AS [InfoCreatedDate],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[infoModifiedDate] AS [InfoModifiedDate],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[infoTimestamp] AS [InfoTimestamp],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[RoutingBehavior],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[Tag],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[TagTypeId],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[TargetLength],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[UniversalIdentifier] 
 FROM ([ZettaDB_5_22_1].[dbo].[AssetTag] 
 INNER JOIN [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag] ON [ZettaDB_5_22_1].[dbo].[AssetTag].[AssetTagId]=[ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[AssetTagId]) 
 WHERE ( ( [ZettaDB_5_22_1].[dbo].[AssetTag].[TagTypeId] = 1) AND ( [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[StationId] = 1))) AS _tmpSet) 
SELECT [AssetTagId],
 [EraseDate],
 [ExternalId],
 [InfoCreatedDate],
 [InfoModifiedDate],
 [InfoTimestamp],
 [RoutingBehavior],
 [Tag],
 [TagTypeId],
 [TargetLength],
 [UniversalIdentifier] 
 FROM __actualSet 
 WHERE [__rowcnt] > 25 AND [__rowcnt] <= 50 ORDER BY [__rowcnt] ASC
SELECT [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[AssetId],
 [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[AssetTagId],
 [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[infoCreatedDate] AS [InfoCreatedDate],
 [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[infoModifiedDate] AS [InfoModifiedDate],
 [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[infoTimestamp] AS [InfoTimestamp],
 [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[OrganizationId],
 [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[Sequence],
 [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[ShiftId],
 [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[StationId],
 [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[StationSpecificAssetTagId] 
 FROM [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag] 
 WHERE ( [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[AssetTagId] IN (1640, 1628, 1639, 1630, 1624, 1645, 1644, 1635, 1629, 1642, 1648, 1646, 1631, 1625, 1643, 1632, 1636, 1647, 1638, 1637, 1627, 1634, 1633, 1626, 1641)) ORDER BY [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[Sequence] ASC
SELECT [ZettaDB_5_22_1].[dbo].[Asset].[ArchiveDate],
 [ZettaDB_5_22_1].[dbo].[Asset].[AssetID] AS [AssetId],
 [ZettaDB_5_22_1].[dbo].[Asset].[AssetTypeID] AS [AssetTypeId],
 [ZettaDB_5_22_1].[dbo].[Asset].[BedTypeId],
 [ZettaDB_5_22_1].[dbo].[Asset].[Cart],
 [ZettaDB_5_22_1].[dbo].[Asset].[Comment],
 [ZettaDB_5_22_1].[dbo].[Asset].[Cut],
 [ZettaDB_5_22_1].[dbo].[Asset].[Ending],
 [ZettaDB_5_22_1].[dbo].[Asset].[EraseDate],
 [ZettaDB_5_22_1].[dbo].[Asset].[IgnorePitching],
 [ZettaDB_5_22_1].[dbo].[Asset].[infoCreatedDate] AS [InfoCreatedDate],
 [ZettaDB_5_22_1].[dbo].[Asset].[infoModifiedDate] AS [InfoModifiedDate],
 [ZettaDB_5_22_1].[dbo].[Asset].[infoTimestamp] AS [InfoTimestamp],
 [ZettaDB_5_22_1].[dbo].[Asset].[ISCI] AS [Isci],
 [ZettaDB_5_22_1].[dbo].[Asset].[ISRC] AS [Isrc],
 [ZettaDB_5_22_1].[dbo].[Asset].[Label],
 [ZettaDB_5_22_1].[dbo].[Asset].[LogID] AS [LogId],
 [ZettaDB_5_22_1].[dbo].[Asset].[Mix],
 [ZettaDB_5_22_1].[dbo].[Asset].[Opening],
 [ZettaDB_5_22_1].[dbo].[Asset].[Origin],
 [ZettaDB_5_22_1].[dbo].[Asset].[OverrideChainType],
 [ZettaDB_5_22_1].[dbo].[Asset].[PlaybackMethod],
 [ZettaDB_5_22_1].[dbo].[Asset].[PlaybackText],
 [ZettaDB_5_22_1].[dbo].[Asset].[Publisher],
 [ZettaDB_5_22_1].[dbo].[Asset].[ScriptConfirmationRequired],
 [ZettaDB_5_22_1].[dbo].[Asset].[ScriptNoteID] AS [ScriptNoteId],
 [ZettaDB_5_22_1].[dbo].[Asset].[TargetDuration],
 [ZettaDB_5_22_1].[dbo].[Asset].[ThirdPartyID] AS [ThirdPartyId],
 [ZettaDB_5_22_1].[dbo].[Asset].[Title],
 [ZettaDB_5_22_1].[dbo].[Asset].[UniversalIdentifier] 
 FROM [ZettaDB_5_22_1].[dbo].[Asset] 
 WHERE ( [ZettaDB_5_22_1].[dbo].[Asset].[AssetID] IN (89223, 89234, 89231, 89225, 89212, 89227, 89229, 89214, 89235, 89216, 89218, 89220, 89233, 89211, 89222, 89213, 89224, 89215, 89226, 89228, 89217, 89219, 89230, 89221, 89232))
SELECT [ZettaDB_5_22_1].[dbo].[Station].[ActiveFlag],
 [ZettaDB_5_22_1].[dbo].[Station].[ApprovalRequired],
 [ZettaDB_5_22_1].[dbo].[Station].[BlockFillCategoryId],
 [ZettaDB_5_22_1].[dbo].[Station].[CallLetters],
 [ZettaDB_5_22_1].[dbo].[Station].[CartPlayContainerID] AS [CartPlayContainerId],
 [ZettaDB_5_22_1].[dbo].[Station].[Comment],
 [ZettaDB_5_22_1].[dbo].[Station].[ConditionalBedStationModes],
 [ZettaDB_5_22_1].[dbo].[Station].[ConditionalDonutBedStationModes],
 [ZettaDB_5_22_1].[dbo].[Station].[CpuID] AS [CpuId],
 [ZettaDB_5_22_1].[dbo].[Station].[CurrentAppearanceID] AS [CurrentAppearanceId],
 [ZettaDB_5_22_1].[dbo].[Station].[CurrentHighlightID] AS [CurrentHighlightId],
 [ZettaDB_5_22_1].[dbo].[Station].[CurrentShiftId],
 [ZettaDB_5_22_1].[dbo].[Station].[DefaultShiftId],
 [ZettaDB_5_22_1].[dbo].[Station].[EnterpriseStationId],
 [ZettaDB_5_22_1].[dbo].[Station].[FillCategoryId],
 [ZettaDB_5_22_1].[dbo].[Station].[HotkeyPlayContainerId],
 [ZettaDB_5_22_1].[dbo].[Station].[HotSpareActivateDeactivateGpioTaskGroupID] AS [HotSpareActivateDeactivateGpioTaskGroupId],
 [ZettaDB_5_22_1].[dbo].[Station].[HotSpareArm],
 [ZettaDB_5_22_1].[dbo].[Station].[infoTimestamp] AS [InfoTimestamp],
 [ZettaDB_5_22_1].[dbo].[Station].[LiveEventPlayStyleID] AS [LiveEventPlayStyleId],
 [ZettaDB_5_22_1].[dbo].[Station].[LocalRouteID] AS [LocalRouteId],
 [ZettaDB_5_22_1].[dbo].[Station].[Mode],
 [ZettaDB_5_22_1].[dbo].[Station].[Name],
 [ZettaDB_5_22_1].[dbo].[Station].[PlayContainerID] AS [PlayContainerId],
 [ZettaDB_5_22_1].[dbo].[Station].[SatelliteRouteID] AS [SatelliteRouteId],
 [ZettaDB_5_22_1].[dbo].[Station].[ShouldAssignLiveEventToStream],
 [ZettaDB_5_22_1].[dbo].[Station].[SplitsMasterStationID] AS [SplitsMasterStationId],
 [ZettaDB_5_22_1].[dbo].[Station].[SplitsPlayContainerId],
 [ZettaDB_5_22_1].[dbo].[Station].[StackPlayContainerId],
 [ZettaDB_5_22_1].[dbo].[Station].[StationID] AS [StationId],
 [ZettaDB_5_22_1].[dbo].[Station].[StationRole],
 [ZettaDB_5_22_1].[dbo].[Station].[UniversalIdentifier] 
 FROM [ZettaDB_5_22_1].[dbo].[Station] 
 WHERE ( [ZettaDB_5_22_1].[dbo].[Station].[StationID] = 1)

Here is the generated SQL after the upgrade,. Note, the exception is thrown after the SELECT, hence, no subsiquent SELECTs.

SELECT DISTINCT [ZettaDB_5_22_1].[dbo].[AssetTag].[AssetTagId],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[EraseDate],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[ExternalId],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[infoCreatedDate] AS [InfoCreatedDate],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[infoModifiedDate] AS [InfoModifiedDate],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[infoTimestamp] AS [InfoTimestamp],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[RoutingBehavior],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[Tag],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[TagTypeId],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[TargetLength],
 [ZettaDB_5_22_1].[dbo].[AssetTag].[UniversalIdentifier] 
 FROM ([ZettaDB_5_22_1].[dbo].[AssetTag] 
 INNER JOIN [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag] ON [ZettaDB_5_22_1].[dbo].[AssetTag].[AssetTagId]=[ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[AssetTagId]) 
 WHERE ( ( [ZettaDB_5_22_1].[dbo].[AssetTag].[TagTypeId] = @p1) AND ( [ZettaDB_5_22_1].[dbo].[StationSpecificAssetTag].[StationId] = @p2)) 
 ORDER BY (SELECT NULL) OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY
Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.
Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

I verified that our code did not change when we performed the upgrade. Is this something you are aware of? How can we get it fixed?

Thank you.

Dan

We changed the default for SQL Server compatibility to 2012+ which means you get different, more compact paging queries, as you can see. The query you posted from earlier versions is the one from 2005 compatibility. You can switch this back through the config file of your application or Runtime Configuration, see: https://www.llblgen.com/Documentation/5.10/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_applicationconfiguration.htm#dqe-compatibility-mode-sql-server-only

Keep in mind that paging of prefetch path queries is only supported when the query is using the ParameterizedPrefecthPathThreshold. See https://www.llblgen.com/Documentation/5.10/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/gencode_prefetchpaths_adapter.htm#prefetch-paths-and-paging (at the bottom)

Also, paging over a non-ordered set is by definition undefined (as the order of a select statement is undefined). Paging queries therefore are better off with an ordering of some kind so the order of the resultset is predictable and the pages in the resultset therefore are predictable (as otherwise the query for page 2 could in theory result in the same rows)

Thanks. I managed to correct the exception by placing a sort based on the primary key of the entity being fetched into the entity collection. I also added a ParameterizedPrefecthPathThreshold per your suggestion.