Click here to Skip to main content
Click here to Skip to main content

Tagged as

Paging with LINQ to Entities

, 13 Jul 2009
Rate this:
Please Sign up or sign in to vote.
CodeProjectI 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
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 <feattype>QueryDatabase()
{
MyEntities db = new </feattype>My<feattype>Entities();

// 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<feattype>;
}</feattype></feattype>

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> <feattype>QueryDatabase()
{
MyEntities db = new </feattype>My<feattype>Entities();

// 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<feattype>;
}</feattype></feattype>
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<feattype> QueryDatabase(int PageSize,
                       int PageIndex,
                       out int Count)
{
MyEntities db = new </feattype>My<feattype>Entities();

// 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<feattype>(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
</feattype></feattype>gvMyTables<feattype><feattype>.VirtualItemCount = virtualItemCount;

// Utilize the query to retrieve the proper page
</feattype></feattype>gvMyTables.<feattype><feattype>DataSource = query;
</feattype></feattype>gvMyTables<feattype><feattype>.DataBind();
</feattype></feattype><feattype><feattype>}
</feattype></feattype>
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Stephen Inglish
Software Developer (Senior) Harland Financial Solutions
United States United States
No Biography provided

Comments and Discussions

 
GeneralUse "select x" instead of the anonymous type PinmemberMember 786718523-Apr-11 13:33 
QuestionWhat about sorting? Pinmembernickoles13-Nov-09 16:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140814.1 | Last Updated 14 Jul 2009
Article Copyright 2009 by Stephen Inglish
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid