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

Published Tuesday, January 22, 2008 12:56 PM by stefan.sedich
Filed under: , , , , , ,

Comments

# re: Gotcha with linq and paging

Tuesday, January 22, 2008 1:44 AM by vikram

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.

# re: Gotcha with linq and paging

Tuesday, January 22, 2008 2:13 AM by stefan.sedich

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

Leave a Comment

(required) 
(required) 
(optional)
(required)