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 ObjectQueryCompile error. "Cannot convert anonymous type <a'> ... to Object Query"QueryDatabase() My
{
MyEntities db = newEntities();
// 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();
}
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>Runtime error: "The entity or complex type 'MyModel.MyTable' cannot be constructed in a LINQ to Entities query. " Sigh...QueryDatabase() My
{
MyEntities db = newEntities();
// 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;
}
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>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.
/// Server Side paging with LINQ to Entities.
/// </summary>
private ListQueryDatabase(int PageSize, My
int PageIndex,
out int Count)
{
MyEntities db = newEntities(); gvMyTables
// 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 countgvMyTables. .VirtualItemCount = virtualItemCount;
// Utilize the query to retrieve the proper pagegvMyTables DataSource = query; .DataBind(); }
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.