Paging with LINQ to Entities

Tue, 30 Jun 2009 13:32:00 +0000

I was in a situation where I found myself adding multiple copies of a query with LINQ to Entities on the code behind of a Page because I could not find a way to build a query inside a method and return that query.

The Problem:
Since I was using a GridView and I was doing server side paging on that GridView, I wanted to only return a select number of records from the database. The first thought was to simply place the repeated code in a method and return the (System.Data.Objects.ObjectQuery) query back to the caller.

private ObjectQuery QueryDatabase()
{
MyEntities db = new MyEntities();

// Define the query to use to return data
var query = from x in db.MyTable
        where (String.IsNullOrEmpty(txtSearchMyName.Text) ||
               x.Name.StartsWith(txtSearchMyName.Text))
        orderby x.Name
        select new
        {
          MyTableID = x.MyTableID,
          Name = x.Name,
          Description = x.Description
        };
return query;
}

protected void btnSearch_Click(object sender, EventArgs e)
{
// Define the query to use, including any search points
var query = BuildQuery();

// Utilize the query to retrieve the proper page
gvFeats.DataSource = query;
gvFeats.DataBind();
}

Compile error. "Cannot convert anonymous type <a'> ... to Object Query"

The problem I faced was what was the type that should be returned? Since we are using the var keyword, I fired up the debugger to find what type was being returned when I executed my query. Turns out it was a System.Data.Objects.ObjectQuery<T>, where T being the anonymous type being returned from the select clause in the query.

Since you cannot return Anonymous types from a method, I thought to replace the anonymous type with the known Entity Framework type created for that table.

private IQueryable<MyTable> QueryDatabase()
{
MyEntities db = new MyEntities();

// Define the query to use to return data
var query = from x in db.MyTable
        where (String.IsNullOrEmpty(txtSearchMyName.Text) ||
               x.Name.StartsWith(txtSearchMyName.Text))
        orderby x.Name
        select new MyTable
        {
          MyTableID = x.MyTableID,
          Name = x.Name,
          Description = x.Description
        };
return query;
}

Runtime error: "The entity or complex type 'MyModel.MyTable' cannot be constructed in a LINQ to Entities query. " Sigh...

So, we can't return an anonymous type, and we can't build the LINQ entity in the query directly... Instead of returning the query to the caller to perform server side paging, perform the paging for the caller, and return the results in a List<MyTable>.

The Solution:

/// <summary>
/// Server Side paging with LINQ to Entities.
/// </summary>
private List QueryDatabase(int PageSize,
                       int PageIndex,
                       out int Count)
{
MyEntities db = new MyEntities();

// Define the query to use to return data
var query = from x in db.MyTable
        where (String.IsNullOrEmpty(txtSearchMyName.Text) ||
               x.Name.StartsWith(txtSearchMyName.Text))
        orderby x.Name
        select new
        {
          MyTableID = x.MyTableID,
          Name = x.Name,
          Description = x.Description
        };
Count = query.Count();

// Perform server side paging by Skipping and Taking
return query.Skip(PageIndex * PageSize)
        .Take(PageSize)
        .ToList()
        .ConvertAll(x => new MyTable
                   {
                     MyTableID = x.MyTableID,
                     Name = x.Name,
                     Description = x.Description
                   });
}

protected void btnSearch_Click(object sender, EventArgs e)
{
int virtualItemCount;

// Define the query to use, including any search points
var query = BuildQuery(gvMyTables.PageSize,
                   0,
                   out virtualItemCount);

// Utilize the query to retrieve the full data count
gvMyTables.VirtualItemCount = virtualItemCount;

// Utilize the query to retrieve the proper page
gvMyTables.DataSource = query;
gvMyTables.DataBind();
}

Because I am using my PagingGridView, I need to know the total number of records the query can return without any paging (so the PagingGridView will display the correct number of pages), so the method above uses an out parameter to communicate that information to the caller, which passes it to the VirtualItemCount of the PagingGridView.

I had to define the anonymous type for the query, but immediately convert it to the Entity Framework type to return in the List<MyTable>.

It is a little more code to type, but at least it works. Hopefully the Entity Framework will change their policy on not being able to create Complex types with LINQ.

Confessions of a Full-Time Consultant

Paging with LINQ to Entities