Datalist paging with linq

Question

Hi,

I'm creating a page that uses Linqfor data access and I'm using DataList to display data. How can I use Linq to do data paging ? Please read simple code below :

I normally use a PagedDatasource but this only seems to work with a DataTable.

Here's my Linq to return Datatable which is bound with Datalist :

Public Shared Function GetStudentList() As DataTable

    Dim db As New DemoDataClassesDataContext()

    Dim query = From st In db.students _
                Order By st.st_studentid Ascending _
                Select st

    Dim dtStudent = New DataTable("myst")


    dtStudent.Columns.Add("st_id", GetType(Integer))
    dtStudent.Columns.Add("st_userid", GetType(Guid))
    dtStudent.Columns.Add("st_studentid", GetType(Integer))
    dtStudent.Columns.Add("st_firstname", GetType(String))
    dtStudent.Columns.Add("st_lastname", GetType(String))
    dtStudent.Columns.Add("st_gender", GetType(String))
    dtStudent.Columns.Add("st_email", GetType(String))


    For Each q In query
        dtStudent.Rows.Add(New Object() {q.st_id, q.st_userid, q.st_studentid, q.st_firstname, q.st_lastname, q.st_gender, q.st_email})
    Next

    Return dtStudent

End Function

In the code behind of the page :

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not Page.IsPostBack() Then
        LoadData()
    End If

End Sub

Private Sub LoadData()
    dsStduent = da_Student.GetStudentList()
    dt_Student.DataSource = dsStduent
    dt_Student.DataBind()

End Sub

Answer 1

You need to look at SQL Paging with LINQ using the Skip() and Take() methods.

Answer 2

You will find the methods .Skip() and .Take() very useful.

I noticed you provided some code from your project, so here's an update on how you should implement these methods.

In your method for getting the data, do the following:

Dim query = (From st In db.students _
            Order By st.st_studentid Ascending _
            Select st).Skip((CurrentPage - 1) * PageSize).Take(PageSize)

Then provide the CurrentPage and PageSize variables as arguments to the method. (You don't want to build them into the data access, as they could vary across different parts of your site...)