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?

Mikhail · Accepted Answer · 2012-01-26T15:06:18.343

4

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()

edited Jan 26 '12 at 15:06

answered Jan 26 '12 at 14:41

Mikhail

9,186
4
33
49

The first URL you posted, http://www.devexpress.com/issue=Q333116, contains an example project that appears to actually utilise its own logic to generate a LINQ query to use paging/sorting/filtering parameters. Isn't it possible to make a "base" linq query (IQueryable myLinqObject = from s in dbContext.myTable) and have the gridview automatically append these parameters before executing the query on the database side? If possible I want to avoid implementing my own logic for this - I assumed the BindToLINQ() function appended actions such as Take, Skip etc. by itself. – L2Eer Jan 26 '12 at 15:31
Absolutely. When using the BindToLINQ method (either of the both ways I mentioned), the GridView perform the corresponding LINQ command (Take, Skip, etc.) itself. i.e. there is no need to perform it manually. Simply use the code you/I provided and check the required queries via the SQL Server Profiler. – Mikhail Jan 26 '12 at 15:51

L2Eer · Answer 2 · 2012-01-26T09:48:48.123

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;})

score 1 · Answer 3 · answered Jan 25 '12 at 17:00

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.

I was thinking of something similar but I'm unaware if you able to influence DX's paging parameters, such as for example telling it there's a maximum of 100 pages while the collection being passed only holds 10 objects. Does the client-side DX Gridview component also pass readable parameters such as which column is being sorted on, etc.? More importantly, doesn't this solution make employing DX's Gridview component rather pointless? — L2Eer, Jan 25 '12 at 17:16

DevExpress MVC Gridview + LINQ

3 Answers3