I'm using v2.6 of LLBLGen with SQL Server 2008. I have an entity modeled from a table with 2 PK columns, both of which are integers. The table has 4050 rows in it. I'm using Linq to page through it 300 rows at a time (no filter or orderby). I'm seeing a row duplicated between the first page and second page. I believe it may be because the query to get the first page has no explicit ordering while the second is ordering by rowcount.

This is the query for the first page (taken from SQL Server Profiler):

SELECT DISTINCT TOP 300 [resource].[dbo].[Messages].[Msg_Id] AS [MsgId], [resource].[dbo].[Messages].[LanguageId], [resource].[dbo].[Messages].[Msg_Desc] AS [MsgDesc], [resource].[dbo].[Messages].[Call_Index] AS [CallIndex], [resource].[dbo].[Messages].[User_Id] AS [UserId], [resource].[dbo].[Messages].[Station], [resource].[dbo].[Messages].[Action], [resource].[dbo].[Messages].[Msg_Secs] AS [MsgSecs], [resource].[dbo].[Messages].[Msg_Bytes] AS [MsgBytes], [resource].[dbo].[Messages].[RecordRate], [resource].[dbo].[Messages].[MessageClass], [resource].[dbo].[Messages].[MessageClassId], [resource].[dbo].[Messages].[SamplingRate], [resource].[dbo].[Messages].[BitsPerSample], [resource].[dbo].[Messages].[LastModifiedDt], [resource].[dbo].[Messages].[FileTypeId], [resource].[dbo].[Messages].[RecordFormatId], [resource].[dbo].[Messages].[TenantId] AS [UIPTenantId] FROM [resource].[dbo].[Messages] 

and this is the query for the second page:

exec sp_executesql N'CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT NULL,[MsgId][Int] NULL,[LanguageId][Int] NULL,[MsgDesc][NVarChar](240) NULL,[CallIndex][Int] NULL,[UserId][NVarChar](48) NULL,[Station][NVarChar](72) NULL,[Action][NVarChar](48) NULL,[MsgSecs][Int] NULL,[MsgBytes][Int] NULL,[RecordRate][Int] NULL,[MessageClass][Int] NULL,[MessageClassId][Int] NULL,[SamplingRate][Int] NULL,[BitsPerSample][Int] NULL,[LastModifiedDt][DateTime2] NULL,[FileTypeId][Int] NULL,[RecordFormatId][Int] NULL,[UIPTenantId][Int] NULL);INSERT INTO #TempTable ([MsgId],[LanguageId],[MsgDesc],[CallIndex],[UserId],[Station],[Action],[MsgSecs],[MsgBytes],[RecordRate],[MessageClass],[MessageClassId],[SamplingRate],[BitsPerSample],[LastModifiedDt],[FileTypeId],[RecordFormatId],[UIPTenantId]) SELECT DISTINCT TOP 601 [resource].[dbo].[Messages].[Msg_Id] AS [MsgId], [resource].[dbo].[Messages].[LanguageId], [resource].[dbo].[Messages].[Msg_Desc] AS [MsgDesc], [resource].[dbo].[Messages].[Call_Index] AS [CallIndex], [resource].[dbo].[Messages].[User_Id] AS [UserId], [resource].[dbo].[Messages].[Station], [resource].[dbo].[Messages].[Action], [resource].[dbo].[Messages].[Msg_Secs] AS [MsgSecs], [resource].[dbo].[Messages].[Msg_Bytes] AS [MsgBytes], [resource].[dbo].[Messages].[RecordRate], [resource].[dbo].[Messages].[MessageClass], [resource].[dbo].[Messages].[MessageClassId], [resource].[dbo].[Messages].[SamplingRate], [resource].[dbo].[Messages].[BitsPerSample], [resource].[dbo].[Messages].[LastModifiedDt], [resource].[dbo].[Messages].[FileTypeId], [resource].[dbo].[Messages].[RecordFormatId], [resource].[dbo].[Messages].[TenantId] AS [UIPTenantId] FROM [resource].[dbo].[Messages] ;SELECT [MsgId],[LanguageId],[MsgDesc],[CallIndex],[UserId],[Station],[Action],[MsgSecs],[MsgBytes],[RecordRate],[MessageClass],[MessageClassId],[SamplingRate],[BitsPerSample],[LastModifiedDt],[FileTypeId],[RecordFormatId],[UIPTenantId] FROM #TempTable WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC;DROP TABLE #TempTable',N'@__rownoStart int,@__rownoEnd int',@__rownoStart=300,@__rownoEnd=600

Thanks for any help,

Ken

Hi Ken,

Paging always requires ordering, please specify an orderby in your linq query. If, after that you still have troubles, please post your code snippet. Also, make sure you are using the latest runtime library version.

Hi David,

Oh, I didn't realize that. Its not enforced obviously. Is this something with LinqToLLBLGen or with Linq in general?

I'll give specify an orderby and see how that goes.

Thanks,

Ken

It's general to paging, SQL by definition doesn't give an ordered list back (so the ordering of the items is not defined, so it can be different each time), and paging requires an ordered set, otherwise page 2 can have, in theory, the same values.

Thanks guys. After adding explicit ordering all seems to work fine.

Ken