Next and Previous links using Linq to SQL

Question

I need to add "Next" and "Previous" links to a Web page displaying messages in date order. A SQL table holds the MessageNumber, Subject and Date. Currently I am using a stored procedure that uses the ROW_NUMBER function:

with MessageList AS
(
select msg_num,
row_number() over (order by msg_date) as rownum
from tblHeaders)

SELECT

        nextrow.msg_num AS NextMsg
FROM
        MessageList currow
LEFT JOIN MessageList nextrow
        ON currow.rownum = nextrow.rownum - 1
LEFT JOIN MessageList prevrow
        ON currow.rownum = prevrow.rownum + 1
where currow.msg_num = @msgnum

Using Linq to SQL how would I generate links to the "Next" and "Previous" message numbers given the current message number and where the table is sorted in date order?

Answer 1

Skip() and Take() can be used for paging functionality.

For example:

Queryable<Customer> custQuery3 =
    (from custs in db.Customers
     where custs.City == "London"
     orderby custs.CustomerID
     select custs)
    .Skip(1).Take(1);

http://msdn.microsoft.com/en-us/library/bb386988.aspx