Paging with GridView and LINQ to SQL

My grideview:

<asp:GridView runat="server" ID="MyGridView" AutoGenerateColumns="false" 
    DataKeyNames="ID" 
    OnRowCreated="MyGridView_RowCreated" AllowPaging="true" Width="100%" 
    PageSize="5" onpageindexchanging="MyGridView_PageIndexChanging" >

My code behind on page_load:

MyGridView.DataSource = new Emp.GetData();
MyGridView.DataBind();

My code:

using (DataContext db = new DataContext())
{
    var query = //valid query here   

    query = query.Skip(StartRowIndex *5 ).Take(5);

    return query.ToList();
}

if i have 15 records in my db, upon page load i see links for page 1,2 3 with data for page 1 shown - 5 records. then when i go to page 2 with 5 records, i see page 1 and 3 links. when i go to page 3 i see only 2 records instead of 5 and sometimes the paging link does not show up correctly either.

I want to display 5 records per page and want the GridView to determine how many pages to show.

i am not using a LinqDataSource, just have a method that returns a list.

It actually tends to be easier if you do use a LinqDataSource.

<asp:LinqDataSource ID="MyDataSource" runat="server"
    OnSelecting="MyDataSource_Selecting">
</asp:LinqDataSource>

And in the code-behind, you can just re-route the LinqDataSource to call your business logic layer. However, it will now need to leave the DataContext object open, i.e., don't wrap it in a using block, or you will get an error (and also don't apply the manual paging with Skip(..).Take(..).

protected void MyDataSource_Selecting(object sender,
        LinqDataSourceSelectEventArgs e) {
    e.Result = Emp.GetData();
}

Now the LinqDataSource should manage all the paging for you automatically.

Alternatively, you could use the PagedDataSource class to achieve this.

Here's an article explaining how it works.