Attention: We have retired the ASP.NET Community Blogs. Learn more >

Gotcha with linq and paging

Hey All,

Had a query which I was paging on the front end. I knew that a certain product was meant to be in my display but could not see it. But on page 2 a product would repeat itself. Odd, got into profiler and looked at the queries. The first page would get a select top 9 which would not do any orderby, the next page would have a query like so: SELECT ROW_NUMBER() OVER (ORDER BY [t15].[test], [t15].[ID], [t15].[CreatedDate] which was ordering by all my columns.

So I added an orderby to my LINQ query which ordered my results by ProductName, then the paging was working as expected. Had another look in profiler and now I had what I expected.


So a warning to all, make SQL Profiler your best friend because if you are not careful you could get spanked by LINQ.


Thanks

Stefan

2 Comments

  • This is one of the reason why I would think twice before using LINQ to query over database. My stored procedure looks still better to work with database.

  • I would disagree with that one :), can your stored procedure look as easy as (from p in db.Products orderby p.Name select p).Skip(10).Take(10), Does you stored proc have type safe objects wrapped around it so you get compile time checking, I agree that you have control over the SQL and know exactly what is happening. But if you are aware and use profiler to check you have not made a monster. LINQ is excellent, but then again if you do not know nothing about SQL and never look at profiler and just write LINQ I think you could get into endless trouble.

    Thanks
    Stefan

Comments have been disabled for this content.