Please have a look at this code:

using (var adapter = new DataAccessAdapter())
            {
                var qf = new QueryFactory();
                var q = qf.Member
                    .WithPath(MemberEntity.PrefetchPathCampSubscriptions.WithInclusions(CampSubscriptionFields.Id));

                q.Page(page, recordsPerPage);

                if (includeFields != null) q.Include(includeFields);
                if (sortClause != null) q.OrderBy(sortClause);

                var result = await adapter.FetchQueryAsync(q);
                return ((EntityCollection<MemberEntity>)result).ToList();
            }

When I request the first page (page=1), the generated prefetchpath query fetches all the data from the db whilst any subsequent calls where page>1, only fetches the needed data in relation to the members.

page=1 - generated queries:

exec sp_executesql N'SELECT TOP(@p2) [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Id] AS [Id], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_First_Name] AS [FirstName], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Last_Name] AS [LastName], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Number] AS [Number], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Date_Of_Birth] AS [DateOfBirth], NULL AS [InternalRemarks], NULL AS [MedicalRemarks], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Gender_Type] AS [GenderType], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Member_Type] AS [MemberType] FROM [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member]  ',N'@p2 bigint',@p2=100

SELECT DISTINCT [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription].[sub_Id] AS [F4_0], NULL AS [F4_1], NULL AS [F4_2], NULL AS [F4_3], NULL AS [F4_4], NULL AS [F4_5], NULL AS [F4_6], NULL AS [F4_7], NULL AS [F4_8], NULL AS [F4_9], NULL AS [F4_10], NULL AS [F4_11], NULL AS [F4_12], NULL AS [F4_13], NULL AS [F4_14], NULL AS [F4_15], NULL AS [F4_16], NULL AS [F4_17], NULL AS [F4_18], NULL AS [F4_19], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Id] AS [F2_20], NULL AS [F2_21], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Guardian_Id] AS [F2_22], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Member_Id] AS [F2_23], NULL AS [F2_24], NULL AS [F2_25], NULL AS [F2_26], NULL AS [F2_27], NULL AS [F2_28], NULL AS [F2_29], NULL AS [F2_30], NULL AS [F2_31], NULL AS [F2_32], NULL AS [F2_33] FROM ( [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription]  INNER JOIN [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp]  ON  [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription].[sub_Id]=[c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Id]) WHERE ( [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Member_Id] IN (SELECT [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Id] AS [Id] FROM [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member]  ) AND ( [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Id] IS NO

page>1 - generated queries:

exec sp_executesql N'WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM (SELECT TOP(@p2) [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Id] AS [Id], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_First_Name] AS [FirstName], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Last_Name] AS [LastName], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Number] AS [Number], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Date_Of_Birth] AS [DateOfBirth], NULL AS [InternalRemarks], NULL AS [MedicalRemarks], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Gender_Type] AS [GenderType], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member].[mem_Member_Type] AS [MemberType] FROM [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Member]  ) AS _tmpSet) SELECT [Id], [FirstName], [LastName], [Number], [DateOfBirth], [InternalRemarks], [MedicalRemarks], [GenderType], [MemberType] FROM __actualSet WHERE [__rowcnt] > @p1 AND [__rowcnt] <= @p2 ORDER BY [__rowcnt] ASC',N'@p1 int,@p2 int',@p1=100,@p2=200

exec sp_executesql N'SELECT DISTINCT [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription].[sub_Id] AS [F4_0], NULL AS [F4_1], NULL AS [F4_2], NULL AS [F4_3], NULL AS [F4_4], NULL AS [F4_5], NULL AS [F4_6], NULL AS [F4_7], NULL AS [F4_8], NULL AS [F4_9], NULL AS [F4_10], NULL AS [F4_11], NULL AS [F4_12], NULL AS [F4_13], NULL AS [F4_14], NULL AS [F4_15], NULL AS [F4_16], NULL AS [F4_17], NULL AS [F4_18], NULL AS [F4_19], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Id] AS [F2_20], NULL AS [F2_21], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Guardian_Id] AS [F2_22], [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Member_Id] AS [F2_23], NULL AS [F2_24], NULL AS [F2_25], NULL AS [F2_26], 
NULL AS [F2_27], NULL AS [F2_28], NULL AS [F2_29], NULL AS [F2_30], NULL AS [F2_31], NULL AS [F2_32], NULL AS [F2_33] FROM ( [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription]  INNER JOIN 
[c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp]  ON  [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription].[sub_Id]=[c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Id]) WHERE ( 
[c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Member_Id] IN (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, 
@p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, 
@p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100) AND ( [c4.orms.subscription.specific].[dbo].[ORMS_IDEEKIDS_Subscription_Camp].[subc_Id] IS NOT NULL))',N'@p1 
uniqueidentifier,@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 uniqueidentifier,@p12 uniqueidentifier,@p13 
uniqueidentifier,@p14 uniqueidentifier,@p15 uniqueidentifier,@p16 uniqueidentifier,@p17 uniqueidentifier,@p18 uniqueidentifier,@p19 uniqueidentifier,@p20 uniqueidentifier,@p21 uniqueidentifier,@p22 uniqueidentifier,@p23 uniqueidentifier,@p24 uniqueidentifier,@p25 
uniqueidentifier,@p26 uniqueidentifier,@p27 uniqueidentifier,@p28 uniqueidentifier,@p29 uniqueidentifier,@p30 uniqueidentifier,@p31 uniqueidentifier,@p32 uniqueidentifier,@p33 uniqueidentifier,@p34 uniqueidentifier,@p35 uniqueidentifier,@p36 uniqueidentifier,@p37 uniqueidentifier,@p38 uniqueidentifier,@p39 uniqueidentifier,@p40 uniqueidentifier,@p41 uniqueidentifier,@p42 uniqueidentifier,@p43 uniqueidentifier,@p44 uniqueidentifier,@p45 uniqueidentifier,@p46 uniqueidentifier,@p47 uniqueidentifier,@p48 uniqueidentifier,@p49 uniqueidentifier,@p50 uniqueidentifier,@p51 uniqueidentifier,@p52 uniqueidentifier,@p53 uniqueidentifier,@p54 uniqueidentifier,@p55 uniqueidentifier,@p56 uniqueidentifier,@p57 uniqueidentifier,@p58 uniqueidentifier,@p59 uniqueidentifier,@p60 uniqueidentifier,@p61 uniqueidentifier,@p62 uniqueidentifier,@p63 uniqueidentifier,@p64 uniqueidentifier,@p65 uniqueidentifier,@p66 uniqueidentifier,@p67 uniqueidentifier,@p68 uniqueidentifier,@p69 uniqueidentifier,@p70 uniqueidentifier,@p71 uniqueidentifier,@p72 uniqueidentifier,@p73 uniqueidentifier,@p74 uniqueidentifier,@p75 uniqueidentifier,@p76 uniqueidentifier,@p77 uniqueidentifier,@p78 uniqueidentifier,@p79 uniqueidentifier,@p80 uniqueidentifier,@p81 uniqueidentifier,@p82 uniqueidentifier,@p83 uniqueidentifier,@p84 uniqueidentifier,@p85 uniqueidentifier,@p86 uniqueidentifier,@p87 uniqueidentifier,@p88 uniqueidentifier,@p89 uniqueidentifier,@p90 uniqueidentifier,@p91 uniqueidentifier,@p92 uniqueidentifier,@p93 uniqueidentifier,@p94 uniqueidentifier,@p95 uniqueidentifier,@p96 uniqueidentifier,@p97 uniqueidentifier,@p98 uniqueidentifier,@p99 uniqueidentifier,@p100 uniqueidentifier',@p1='98189DE4-6E18-E011-80FE-0019D23B3517',@p2='47189DE4-6E18-E011-80FE-0019D23B3517',@p3='BB189DE4-6E18-E011-80FE-0019D23B3517',@p4='B5189DE4-6E18-E011-80FE-0019D23B3517',@p5='59189DE4-6E18-E011-80FE-0019D23B3517',@p6='90189DE4-6E18-E011-80FE-0019D23B3517',@p7='A0189DE4-6E18-E011-80FE-0019D23B3517',@p8='40189DE4-6E18-E011-80FE-0019D23B3517',@p9='58189DE4-6E18-E011-80FE-0019D23B3517',@p10='43189DE4-6E18-E011-80FE-0019D23B3517',@p11='88189DE4-6E18-E011-80FE-0019D23B3517',@p12='5D189DE4-6E18-E011-80FE-0019D23B3517',@p13='C4389FEA-6E18-E011-80FE-0019D23B3517',@p14='85189DE4-6E18-E011-80FE-0019D23B3517',@p15='C8189DE4-6E18-E011-80FE-0019D23B3517',@p16='CE189DE4-6E18-E011-80FE-0019D23B3517',@p17='74189DE4-6E18-E011-80FE-0019D23B3517',@p18='C4189DE4-6E18-E011-80FE-0019D23B3517',@p19='5C189DE4-6E18-E011-80FE-0019D23B3517',@p20='8C189DE4-6E18-E011-80FE-0019D23B3517',@p21='8F189DE4-6E18-E011-80FE-0019D23B3517',@p22='6E189DE4-6E18-E011-80FE-0019D23B3517',@p23='C1189DE4-6E18-E011-80FE-0019D23B3517',@p24='71189DE4-6E18-E011-80FE-0019D23B3517',@p25='D6189DE4-6E18-E011-80FE-0019D23B3517',@p26='FC189DE4-6E18-E011-80FE-0019D23B3517',@p27='F0189DE4-6E18-E011-80FE-0019D23B3517',@p28='CB389FEA-6E18-E011-80FE-0019D23B3517',@p29='B7189DE4-6E18-E011-80FE-0019D23B3517',@p30='DB189DE4-6E18-E011-80FE-0019D23B3517',@p31='95189DE4-6E18-E011-80FE-0019D23B3517',@p32='55189DE4-6E18-E011-80FE-0019D23B3517',@p33='CB189DE4-6E18-E011-80FE-0019D23B3517',@p34='54189DE4-6E18-E011-80FE-0019D23B3517',@p35='D2189DE4-6E18-E011-80FE-0019D23B3517',@p36='84189DE4-6E18-E011-80FE-0019D23B3517',@p37='FB189DE4-6E18-E011-80FE-0019D23B3517',@p38='E6189DE4-6E18-E011-80FE-0019D23B3517',@p39='AE189DE4-6E18-E011-80FE-0019D23B3517',@p40='4E189DE4-6E18-E011-80FE-0019D23B3517',@p41='D1189DE4-6E18-E011-80FE-0019D23B3517',@p42='51189DE4-6E18-E011-80FE-0019D23B3517',@p43='77189DE4-6E18-E011-80FE-0019D23B3517',@p44='DE189DE4-6E18-E011-80FE-0019D23B3517',@p45='C9389FEA-6E18-E011-80FE-0019D23B3517',@p46='99189DE4-6E18-E011-80FE-0019D23B3517',@p47='F7189DE4-6E18-E011-80FE-0019D23B3517',@p48='E3189DE4-6E18-E011-80FE-0019D23B3517',@p49='E2189DE4-6E18-E011-80FE-0019D23B3517',@p50='39189DE4-6E18-E011-80FE-0019D23B3517',@p51='F3189DE4-6E18-E011-80FE-0019D23B3517',@p52='68189DE4-6E18-E011-80FE-0019D23B3517',@p53='DA189DE4-6E18-E011-80FE-0019D23B3517',@p54='93189DE4-6E18-E011-80FE-0019D23B3517',@p55='B6189DE4-6E18-E011-80FE-0019D23B3517',@p56='F6189DE4-6E18-E011-80FE-0019D23B3517',@p57='C6389FEA-6E18-E011-80FE-0019D23B3517',@p58='ED189DE4-6E18-E011-80FE-0019D23B3517',@p59='E9189DE4-6E18-E011-80FE-0019D23B3517',@p60='B2189DE4-6E18-E011-80FE-0019D23B3517',@p61='61189DE4-6E18-E011-80FE-0019D23B3517',@p62='CC389FEA-6E18-E011-80FE-0019D23B3517',@p63='79189DE4-6E18-E011-80FE-0019D23B3517',@p64='9A189DE4-6E18-E011-80FE-0019D23B3517',@p65='83189DE4-6E18-E011-80FE-0019D23B3517',@p66='9D189DE4-6E18-E011-80FE-0019D23B3517',@p67='A8189DE4-6E18-E011-80FE-0019D23B3517',@p68='4A189DE4-6E18-E011-80FE-0019D23B3517',@p69='A5189DE4-6E18-E011-80FE-0019D23B3517',@p70='7F189DE4-6E18-E011-80FE-0019D23B3517',@p71='C7189DE4-6E18-E011-80FE-0019D23B3517',@p72='6B189DE4-6E18-E011-80FE-0019D23B3517',@p73='D5189DE4-6E18-E011-80FE-0019D23B3517',@p74='D7189DE4-6E18-E011-80FE-0019D23B3517',@p75='3D189DE4-6E18-E011-80FE-0019D23B3517',@p76='BA189DE4-6E18-E011-80FE-0019D23B3517',@p77='C5389FEA-6E18-E011-80FE-0019D23B3517',@p78='64189DE4-6E18-E011-80FE-0019D23B3517',@p79='CA389FEA-6E18-E011-80FE-0019D23B3517',@p80='A1189DE4-6E18-E011-80FE-0019D23B3517',@p81='67189DE4-6E18-E011-80FE-0019D23B3517',@p82='AF189DE4-6E18-E011-80FE-0019D23B3517',@p83='FA189DE4-6E18-E011-80FE-0019D23B3517',@p84='89189DE4-6E18-E011-80FE-0019D23B3517',@p85='EA189DE4-6E18-E011-80FE-0019D23B3517',@p86='DF189DE4-6E18-E011-80FE-0019D23B3517',@p87='3C189DE4-6E18-E011-80FE-0019D23B3517',@p88='C3389FEA-6E18-E011-80FE-0019D23B3517',@p89='7C189DE4-6E18-E011-80FE-0019D23B3517',@p90='60189DE4-6E18-E011-80FE-0019D23B3517',@p91='BC189DE4-6E18-E011-80FE-0019D23B3517',@p92='78189DE4-6E18-E011-80FE-0019D23B3517',@p93='AB189DE4-6E18-E011-80FE-0019D23B3517',@p94='4B189DE4-6E18-E011-80FE-0019D23B3517',@p95='82189DE4-6E18-E011-80FE-0019D23B3517',@p96='FD189DE4-6E18-E011-80FE-0019D23B3517',@p97='94189DE4-6E18-E011-80FE-0019D23B3517',@p98='C2389FEA-6E18-E011-80FE-0019D23B3517',@p99='A4189DE4-6E18-E011-80FE-0019D23B3517',@p100='44189DE4-6E18-E011-80FE-0019D23B3517'

Is there a specific reason that the first page fethes all the data for the prefetch path sub queries or is there something I'm missing?

What LLBLGen version and runtime library version are you using? ( http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717 )

Where are you obtaining the generated SQL?

Additionally, check this documentation link which stays:

Documentation wrote:

Prefetch Paths and Paging LLBLGen Pro supports paging functionality in combination of Prefetch Paths. If you want to utilize paging in combination of prefetch paths, be sure to set DataAccessAdapter.ParameterisedPrefetchPathThreshold to a value larger than the page size you want to use. You can use paging in combination of prefetch path with a page size larger than DataAccessAdapter.ParameterisedPrefetchPathThreshold but it will be less efficient.

To use paging in combination of prefetch paths, use one of the overloads you'd normally use for fetching data using a prefetch path, which accept a page size and page number as well.

Yup, it was the ParameterisedPrefetchPathThreshold. I should have known this.

Thx for pointing it out to me D.