none
Paging through LINQ to SQL results RRS feed

  • Question

  • Hi all,

    I need to page through the results of a LINQ to SQL query. I already know that it is possible and efficient to do it with queries written down in LINQ (See ScottGu's Blog ), but what about if I'm using LINQ to SQL to call stored procedures (e.g. myDataContext.sp_GetMyRows())... does the deferred execution model still applies for these queries?

    Thanks all!
    Friday, December 18, 2009 8:47 PM

Answers

  • You can't do paging with stored procedures server-side - there is no way LINQ to SQL can change the SQL of the stored procedure to implement the necessary query.

    Your only option would be to execute the stored procedure and then process pages client-side by putting ToList() between the call and the skip/take operations.

    [)amien
    Saturday, December 19, 2009 2:11 AM
    Moderator
  • Alternatively, you can make your stored procedure parameterized and do paging in TSQL using row_number() and top(x) clauses (see example here ). This will shield you from bringing all records to client-side, and only the relevant records will be retrieved.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Saturday, December 19, 2009 7:08 PM

All replies

  • You can't do paging with stored procedures server-side - there is no way LINQ to SQL can change the SQL of the stored procedure to implement the necessary query.

    Your only option would be to execute the stored procedure and then process pages client-side by putting ToList() between the call and the skip/take operations.

    [)amien
    Saturday, December 19, 2009 2:11 AM
    Moderator
  • Alternatively, you can make your stored procedure parameterized and do paging in TSQL using row_number() and top(x) clauses (see example here ). This will shield you from bringing all records to client-side, and only the relevant records will be retrieved.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Saturday, December 19, 2009 7:08 PM