I’m getting some inconsistent paging results. On a table with 539 rows, paging 10 rows at a time the first page displays correctly then pages 2 though 9 all return the exact same 10 rows (which isn’t even the second page results that I expected to see). Pages 10 through 53 all list the correct data.

Do you currently have any paging bugs on your ‘todo’ list that might cause this behaviour?

Also, by running query profiler (Sql server 2005) I can also see the sql being generated for the first page looks like

SELECT TOP 10 ...  ORDER BY [LPLA_1].[IsPremium] DESC,[LPLA_1].[CreatedDateTimeUTC] ASC

which returns a different result set to the first 10 records of my test query:

Select * From ... order by IsPremium desc, CreatedDateTimeUtc asc

So the data returned from a "select *" appears to be inconsistant when using ‘select * Top X’ with order columns. Gah!

Update: Just to make sure I wasn't doing anything stupid I created a test project with both an llblgen data context and one generated by dragging a table from the server explorer onto a dbml file. When using the same query the microsoft generated data context paged correctly, the llblgen one did not.

We can't help you unless you post the code you used, the table layout and how data is distributed over teh fields you're sorting on. The thing is that if you run a SELECT ... FROM T, the order in which the rows are returned is by definition undefined (SQL Standard). So if you want to have effective paging, you should sort on a field or set of fields which are unique. If you sort on 1 field which has duplicate fields, you won't get the right rows every time.

We have no paging issues open at the moment, all our test code with paging works OK. So if you could give us more information about your situation it would be great

Fair enough

I've attached a wee sample app that fetches data both via an Microsoft generated data context and an LLBLGen one. Use the up / down buttons on the numeric control to page through results.

You can see on the first page how the order by clauses return different results. This isn't a huge issue as at least the results are consistant.

The issue for me is that from pages 2 to 10(ish) the llblgen code returns the same 10 pages of data - and I can't see why as the ms version returns the results I was expecting.

Cheers, Padgett

No attachment?

Doh, hit the 1mb limit and i'm on my cruddy home mobile broadband...

Sample app can be downloaded from here:

*removed

Padgett wrote:

Doh, hit the 1mb limit and i'm on my cruddy home mobile broadband...

Sample app can be downloaded from here:

http://www.padgett.com.au/files/win1.zip

I'm sure it's not 4MB of code . I got it. You can remove the link now Will check it out.

The issue is exactly as I explained above:

The thing is that if you run a SELECT ... FROM T, the order in which the rows are returned is by definition undefined (SQL Standard). So if you want to have effective paging, you should sort on a field or set of fields which are unique. If you sort on 1 field which has duplicate fields, you won't get the right rows every time.

If you sort on CompanyId, the paging shows the same rows. You sort on 2 fields, both result in many duplicate rows for these two fields, i.o.w.: it's undefined in which order these rows are returned by a select. So, just because two o/r mappers page over the table with the same query doesn't mean they return the data in the same order, as it's undefined what the order will be, as I've explained above.

What I wonder is why you kept thinking it is an issue somewhere, even though you could have seen in the database that your query would result in an ordering which isn't precise enough: the order in which the rows are returned would be undetermined because your ordering results in many duplicates for these two fields.

I don't understand how sorting on two fields will result in pages 2 though 10 all showing the exact same page of results:

2653 Allfreight Australia 2652 Allens Transport Australia 2651 All Things Are Posible 2650 Alderson Transport Services Pty Ltd 2649 AJG Transport 2648 AJ Peters Transport 2647 Ahssad Pty Ltd 2646 ADL Transport 2645 Adam Stewart 2644 AC Freight Lines

How can a sort criteria return duplicate results? There are no outer joins? The equivalent sql statement:

Select * From Company c order by c.IsPremium desc, c.CreatedDateTimeUtc asc

Doesn't return duplicate rows and doesn't require the sort fields to be unique. I'd expect the items flaged with 'IsPremium' to be grouped at the top, then sorted by CreatedDateTimeUtc.

Are you saying I need to change the order to be: c.IsPremium desc, c.CreatedDateTimeUtc asc, companyId ?

My apologies if I'm being thick, but I need to understand why this is occuring, and how to fix my paging while still maintaining the business requirment of sorting by the two (non unique) fields. I don't mind if the order is diferent from what the MS context generates, I just don't want the duplicate rows.

It's also a matter of personal pride as having just started with a new company I'm having to defend my OR mapper of choice from what others in the office see as a bug Around here the choice of OR framework is as near a religious debate as *nix / Mac / Windows!

Cheers, Padgett

There's no bug, so I won't go into that. See my explanation above. THis isn't stubborness, but there's nothing we can do. I'll try to explain things below again

The thing is: with each page request, the same query is executed, and then the paging logic selects the page to fetch from that set, this happens inside the DB.

As I have explained above: there's no defined order in which the rows have to be returned, if there are duplicate rows for the sorting. This means that the database will optimize the query so it obeys the rules of the SQL standard and also gives back the rows requested.

If you append an orderby on CompanyId after the two fields, you'll see you'll get proper paging. I didn't check the query on linq to sql, but I think they append the PK to the ordering in paging situations, LLBLGen Pro doesn't do that. I can't explain in another way how linq to sql could have proper paging over a set which has no pre-defined order.

I also found it weird that page 2-10 were the same and after that it was equal... the thing is though that the query is correct, and sadly, the database is also correct in returning the same pages: simply because there's no correct ordering.

For duplicates, I didnt' mean complete duplicate rows , but duplicates in the fields to order.

If you fetch customers, and you order on Country, and there are more customers with the same country, what is the order in which these customers with the same country are returned? That's undefined. So one time you'll get them in one order, and another time in another order. To get a deterministic ordering, you'll have to specify a sort clause which, across the ordering, has unique values.

If you for example omit the orderby in linq to sql and you use paging, it will tell you it can't proceed. This is why.

So, it's not a matter of bugs or not, it's a matter of what the SQL standard says and what an RDBMS has to do. With these kind of things, the best thing to do is examine the SQL. You'll see that the paging query LLBLGen pro uses is way different than the one Linq to sql uses, as linq to sql can't page over all resultsets.

I tested different sortings in your testcode, and when I formulated a sorting which, across the fields sorted, had unique values (so the sorting was deterministic!), the paging worked ok. If your collegues still think there's a bug in our code, let them define a query which has a deterministic ordering and show that paging fails.

Thanks Otis,

Yup, your right – MS automatically appends the PK to the end of the order by clause. Once I added this to my LLBLGen query the results returned as expected and I can now see why. I guess this is an example of where MS does something for me, limiting my understanding and creating a crutch.

So I learnt something new today and LLBLGen remains unblemished

Cheers, Padgett

pfew