DevExpress MVC Gridview + LINQ

Question

We have a DX gridview being rendered in a specifically designed view. We pass a predefined ViewModel object whose values are filled from a Linq-2-Entities query. The problem is that in our callback function the L2E query is actually executed on the DB before any filtering, sorting and paging has been performed by the DevExpress grid. IE.: (simplified example, in the actual situation we select data from several tables but still in a single linq query)

public ActionResult GridViewPartial(string myParameters) 
{
    var modelData = from s in db.myTable 
                                     select new { modelName = s.Name };

    return PartialView("GridViewPartial", modelData);
}

In this situation the query is actually executed before the data has been passed to the View. Therefore it actually selects way too much data from the DB while the gridview only displays the selected page.

How would we have to modify the query so it only selects the data of the page the user has selected? IE. skip 10 rows and take 10 in the L2E query when the user selects page 2, instead of selecting 100000 rows (if there are that many in the table) and afterwards applying the filtering/sorting/paging, like in the described situation?

Answer 1

The MVC GridView Extension supports the so-called “server mode” functionally via the internal LinqServerModeDataSource object.

It requires the IQueryable object as a datasource:

Direct LINQ query:

http://www.devexpress.com/issue=Q333116

@Html.DevExpress().GridView(...).BindToLINQ(string.Emptry, string.Emptry, (s, e) => { e.KeyExpression = Key_Column_Here; e.QueryableSource = Linq_Query_Here; }

The Table/View from the LinqToX DataCotnext/Classes;

http://mvc.devexpress.com/GridView/DataBindingToLargeDatabase

@Html.DevExpress().GridView(...).BindToLINQ(Data_Context_Name_Here, Table_View_Name_Here).GetHtml()

Answer 2

It appears that the object type should be of System.Linq.IQueryable in order for DevExpress's gridview to effectively use its Linq DB commands. Inside your controller, build up your logic and pass your Linq query to the View:

System.Linq.IQueryable modelData = from s in db.myTable 
                                 select new { modelName = s.Name };

return PartialView("GridViewPartial", modelData);

Inside the Razor view, initiate the gridview with the command:

@model System.Linq.IQueryable
@Html.DevExpress().GridView(...).BindToLINQ((string)null, null, (s, e) => { e.KeyExpression = "Table_id"; e.QueryableSource = Model;})

Answer 3

I would implement the paging/sorting/filtering in the data access layer/level and return only what needs to be shown, because the Grid as you noticed can show the right page but this happens client side and everything is always loaded from the database, except if you use their XPO ORM (which I dont) and enable the grid server-mode (at least this is the concept in their windows forms and ASP.NET Grid).

the answer to your question is that you should design your LINQ queries to accept as parameters page size and page index and do a Take(pageSize) from the specific pageIndex you need. All of this can also be done in a stored procedure directly on the db.