Example: I have three tables: Area ->AreaStrand ->Strand AreaStrand is a junction table.

So, I do a FetchEntityCollection to Area using a predicate and a maxAmountOfItemsToReturn of 1. I also have a prefetch using StrandCollectionViaAreaStrand (no max). The following sql get generated:

exec sp_executesql N'SELECT DISTINCT TOP 1 [SPEEDe].[dbo].[Area].[AreaIdent], [SPEEDe].[dbo].[Area].[Area], 
[SPEEDe].[dbo].[Area].[AreaDescription], [SPEEDe].[dbo].[Area].[CreatedDate], [SPEEDe].[dbo].[Area].[CreatedUsername], 
[SPEEDe].[dbo].[Area].[UpdatedDate], [SPEEDe].[dbo].[Area].[UpdatedUsername] FROM [SPEEDe].[dbo].[Area]  WHERE ( ( 
[SPEEDe].[dbo].[Area].[AreaIdent] = @AreaIdent1))',N'@AreaIdent1 int',@AreaIdent1=1


exec sp_executesql N'SELECT DISTINCT [SPEEDe].[dbo].[Strand].[StrandIdent], [SPEEDe].[dbo].[Strand].[Strand], 
[SPEEDe].[dbo].[Strand].[StrandDescription], [SPEEDe].[dbo].[Strand].[CreatedDate], [SPEEDe].[dbo].[Strand].[CreatedUsername], 
[SPEEDe].[dbo].[Strand].[UpdatedDate], [SPEEDe].[dbo].[Strand].[UpdatedUsername] FROM (( [SPEEDe].[dbo].[Area] [LPA_A1]  INNER JOIN 
[SPEEDe].[dbo].[AreaStrand] [LPA_A2]  ON  [LPA_A1].[AreaIdent]=[LPA_A2].[AreaIdent]) INNER JOIN [SPEEDe].[dbo].[Strand]  ON  
[SPEEDe].[dbo].[Strand].[StrandIdent]=[LPA_A2].[StrandIdent]) WHERE ( ( ( [LPA_A2].[AreaIdent] = @AreaIdent1)))',N'@AreaIdent1 
int',@AreaIdent1=1


exec sp_executesql N'SELECT DISTINCT TOP 1 [LPA_A1].[AreaIdent] AS [AreaIdent0], [SPEEDe].[dbo].[Strand].[StrandIdent] AS [StrandIdent1] 
FROM (( [SPEEDe].[dbo].[Area] [LPA_A1]  INNER JOIN [SPEEDe].[dbo].[AreaStrand] [LPA_A2]  ON  [LPA_A1].[AreaIdent]=[LPA_A2].[AreaIdent]) 
INNER JOIN [SPEEDe].[dbo].[Strand]  ON  [SPEEDe].[dbo].[Strand].[StrandIdent]=[LPA_A2].[StrandIdent]) WHERE ( ( ( ( [LPA_A2].[AreaIdent] = 
@AreaIdent1))))',N'@AreaIdent1 int',@AreaIdent1=1

Now I'm supposed to get back 2 StrandEntities but I only get one. I think it's because of the TOP 1 on the last sql statement above. If I change the maxAmountOfItemsToReturn on the primary collection (Not the prefetch) to 2 then I get both StrandEnities.

So, I don't think that last sql statement should have a TOP unless you are limiting the Prefetch.

Thanks,

Fishy

Hi Fishy,

Please post the relevant code that generates that sql. Also post the LLBLGen version and Runtime Library version (http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7717).

Here's the code from the main application:

        Dim Areas = SpeedeMgr.AreaManager.FetchEntityPlus(SpeedeMgr.AreaManager.CreateBucket(SpeedeMgr.AreaManager.MyFields.AreaIdent = 1), , , SpeedeMgr.AreaManager.CreateBasicPrefetchList(Speede.ManagerClasses.AreaManager.PrefetchItemEnum.StrandCollectionViaAreaStrand))

Here is the codegen manager classes:

        ''' <summary>
        ''' This enum represents tables that can be prefetched from
        ''' from the Area Table.
        ''' </summary>
        Public Enum PrefetchItemEnum
            None
            AreaStrand
            StrandCollectionViaAreaStrand
        End Enum

        ''' <summary>
        ''' This class represents tables to prefetch
        ''' from the Area Table.
        ''' </summary>
        Public Class PrefetchList
            ''' <summary>Table related to the Area Table</summary>
            Public PrefetchItem As PrefetchItemEnum
            ''' <summary>Fields to include when returning entities (Nothing = All Fields)</summary>
            Public IncludeFields As List(Of EntityField2)
            ''' <summary>Maximum number of Entities to return.</summary>
            Public Max As Integer
            ''' <summary>Sort Expression</summary>
            Public Sort As SortExpression
            ''' <summary>Predicate Expression</summary>
            Public Predicate As IPredicateExpression
            ''' <summary>List of SubPathList objects related to this Prefetch Table</summary>
            Public SubPath As List(Of SubPathList)
        End Class
        
        ''' <summary>
        ''' This class represents tables to prefetch
        ''' from other prefetch tables (SubPaths).
        ''' </summary>
        Public Class SubPathList
            ''' <summary>Fields to include when returning entities (Nothing = All Fields)</summary>
            Public IncludeFields As List(Of EntityField2)
            ''' <summary>Maximum number of Entities to return.</summary>
            Public Max As Integer
            ''' <summary>Sort Expression</summary>
            Public Sort As SortExpression
            ''' <summary>Predicate Expression</summary>
            Public Predicate As IPredicateExpression
            ''' <summary>The Table you wish to Prefetch</summary>
            Public PrefetchItem As IPrefetchPathElement2
            ''' <summary>List of SubPathList objects related to this SubPath Table</summary>
            Public SubPath As List(Of SubPathList)
        End Class
        
        ''' <summary>
        ''' Creates a basic List(Of PrefetchList) from passed in PrefetchItemEnum.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function CreateBasicPrefetchList(Optional ByVal prefetchItems As PrefetchItemEnum = PrefetchItemEnum.None, Optional ByVal prefetchItems2 As PrefetchItemEnum = PrefetchItemEnum.None, Optional ByVal prefetchItems3 As PrefetchItemEnum = PrefetchItemEnum.None, Optional ByVal prefetchItems4 As PrefetchItemEnum = PrefetchItemEnum.None, Optional ByVal prefetchItems5 As PrefetchItemEnum = PrefetchItemEnum.None) As List(Of PrefetchList)
        
            Dim PrefetchLists As New List(Of PrefetchList)
            
            If prefetchItems <> PrefetchItemEnum.None Then PrefetchLists.Add(New PrefetchList With {.PrefetchItem = prefetchItems})
            If prefetchItems2 <> PrefetchItemEnum.None Then PrefetchLists.Add(New PrefetchList With {.PrefetchItem = prefetchItems2})
            If prefetchItems3 <> PrefetchItemEnum.None Then PrefetchLists.Add(New PrefetchList With {.PrefetchItem = prefetchItems3})
            If prefetchItems4 <> PrefetchItemEnum.None Then PrefetchLists.Add(New PrefetchList With {.PrefetchItem = prefetchItems4})
            If prefetchItems5 <> PrefetchItemEnum.None Then PrefetchLists.Add(New PrefetchList With {.PrefetchItem = prefetchItems5})
            
            Return PrefetchLists
        End Function

        ''' <summary>
        ''' Returns the first AreaEntity or Nothing if no entries are found.
        ''' This is the preferred method for retrieving information.
        ''' </summary>
        ''' <param name="predicate">The RelationPredicateBucket</param>
        ''' <param name="includeFields">Contains a List(Of EntityField2) or Nothing (default) for all Fields.  You can use AreaManager.CreateIncludeItems to easily create field lists.</param>
        ''' <param name="sort">A SortExpression</param>
        ''' <param name="prefetch">Contains a List(Of PrefetchList)</param>
        ''' <returns></returns>
        ''' <remarks></remarks>             
        Public Shared Function FetchEntityPlus(Optional ByVal predicate As RelationPredicateBucket = Nothing, Optional ByVal includeFields As List(Of EntityField2) = Nothing, Optional ByVal sort As SortExpression = Nothing, Optional ByVal prefetch As List(Of PrefetchList) = Nothing) As AreaEntity

            Using adapter As New CustomDataAccessAdapter

                Dim Entity As New AreaEntity

                Dim EntityCollection = FetchEntityCollectionPlus(predicate, includeFields, 1, sort, prefetch)
                
                If EntityCollection IsNot Nothing AndAlso EntityCollection.Count = 1 Then
                    Return EntityCollection.First
                Else
                    Return Nothing
                End If

            End Using

        End Function


        ''' <summary>
        ''' Returns a Area EntityCollection or an empty EntityCollection if no entities are found.
        ''' This is the preferred method for retrieving information.
        ''' </summary>
        ''' <param name="predicate">The RelationPredicateBucket</param>
        ''' <param name="includeFields">Contains a List(Of EntityField2) or Nothing (default) for all Fields.  You can use AreaManager.CreateIncludeItems to easily create field lists.</param>
        ''' <param name="maxEntries">The Maximum number of entities to retrieve or 0 (default) for all entities</param>
        ''' <param name="sort">A SortExpression</param>
        ''' <param name="prefetch">Contains a List(Of PrefetchList)</param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function FetchEntityCollectionPlus(Optional ByVal predicate As RelationPredicateBucket = Nothing, Optional ByVal includeFields As List(Of EntityField2) = Nothing, Optional maxEntries As Integer = 0, Optional ByVal sort As SortExpression = Nothing, Optional ByVal prefetch As List(Of PrefetchList) = Nothing) As EntityCollection(Of AreaEntity)
            Using adapter As New CustomDataAccessAdapter

                Dim EntityCollection As New EntityCollection(Of AreaEntity)
                
                adapter.FetchEntityCollection(EntityCollection, predicate, maxEntries, sort, CreatePrefetchPaths(prefetch), If(includeFields IsNot Nothing, New IncludeFieldsList(includeFields), Nothing))

                Return EntityCollection
            End Using
            
        End Function



#Region " MyFields (AreaFields) "
        ''' <summary>Shortcut for AreaFields.</summary>
        Public Class MyFields : Inherits AreaFields : End Class
#End Region

        Public Shared Function CreateBucket(Optional ByVal predicate As IPredicate = Nothing) As RelationPredicateBucket
            If predicate Is Nothing Then Return New RelationPredicateBucket

            Return New RelationPredicateBucket(predicate)
        End Function

Version: 2.6.8.612 2.6 Final

Thanks,

Fishy

Version: 2.6.8.612

First thing to do is try using the latest build, as you are using a very old one.

That took care of the problem.

Thanks !