I am working on a ASP.NET MVC application where we have to write our own code for paging. And I see elements getting repeated on different pages. This is happening because the order of elements in the Iquerable varies randomly and one have to query the database for each page. I solved this problem by ordering the elements by date of creation and then the order I wanted. But I feels its heavy. Is there a better way to solve this problem.
views:
45answers:
3
A:
If you fetch rows from a database via IQuerable and order on one column, if there are rows that have the same value in that column the order of those rows may vary, you need to do as you do now, that is order by two columns, first the one you are really interested in and then the second (like date), there should not be that big a performance hit since the sorting is done in the database.
But what need to do is specify the ordering like this:
Context.Products
.Where(p => p.ProductID > 100)
.OrderBy(p => p.CategoryID)
.ThenBy(p => p.Date).ToList();
Notice the ThenBy, that will generate the correct SQL.
Torkel
2009-12-15 07:53:02
What difference does it make if I write .OrderBy(p => p.CategoryID) .OrderBy(p => p.Date).ToList() ??
San
2009-12-15 07:58:19
Because then you order the list by categoryId then order it again by date, the first ordering (by category) will not take effect, the list will be ordered by date. The only effect the category ordernig will have is that items with identical date will be ordered by id so in effect that should be equivalent of doing OrderBy(p => p.Date) .ThenBy(p => p.CategoryID) but am not sure that is the case, depends on the implementation of the OrderBy function.
Torkel
2009-12-16 19:10:55
+1
A:
No, that is the correct way. A database might give back rows in a non-determistic order if you do not tell it to order by something.
nos
2009-12-15 09:02:54
A:
Have a look at: http://srtsolutions.com/blogs/billwagner/archive/2006/10/14/paging-output-with-linq2sql.aspx of Bill Wagner.
He uses Take() and Skip() to create paged output.
Marcel
2009-12-15 16:11:28