Hello,

I have to a complex filtering construct in my application that i have to execute on the database. I create an ipredicate and get all data through FetchEntityCollection.

The data must be display in a devexpress grid view which is responsible for the ordering, paging and grouping. As I have more than 100000 data lines, and only display 20, I would like to perform the paging, ordering and grouping on the Database through SQL. FetchEntityCollection returned all elements. I have also tried the LLBLGenProDataSource2 but the when I look on the request generated, all data are returned from data.

Devexpress grid view supports linq expression and then all paging, ordering and grouping are executed on the database server.

Is there a way to filter a DataSource2<Entity> with an ipredicate, so that with this filtered datasource the linq ordering, paging and grouping is performed on the database server and that only the needed entities are returned ?

Thanks for your help,

Martin

To begin with, FetchEntityCollection() has overloads that accepts paging parameters. Also you can enable paging at the server side in the LLBLDataSource control.

Have you tried any of these?

Additionally, doesn't the devexpress grid come with a linqdatasource control, which you can use to generate the iqueryable?

I have tried both solutions. With llblgenprodatasource the paing is not done on DB and fecthentitycollection paging parameter cannot be connected (at least easily) to the the llblgen grid view.

Devexpress support linq request but the SQL request generated from a linq request like context.object.where(o=>context.object2.select(o2=>o2.field).contains(o.field)) is so slow because the sql EXISTS statement is used.

The same request with ipredicate and fecthentitycollection uses the sql IN operator which is really better.

That's why i want to filter the DataSource2 with an ipredicate and after use the linq ordering and paging.

I wonder which version of LLBLGen runtime libraries are you using? and also which version of the ASPxGridView.

Ffew years back they had a property on the grid "DataSourceForceStandardPaging" that you need to set to true, so that they allow pasing the paging info to the underlying datasource. Otherwise you'd need to use their linq datasource control.

Additionally, if the paging isn't done on the server, according to you, is that because the query doesn't contain elements for paging? This might be caused by the fact one of the fields has a distinct violating type, so paging can't occur on the server. In that case the framework will page on the client, using the datareader (so reading enough rows to read the page, then close the reader) so not all rows are read.

I use LLBGLen 4.0 and devexpress MVC 12.2 Yes the query generation generated by the LLBLgenProDataSource used with the devexpress MVC 12.2 GridView doesn't contain the paging. All Data are returned and then filtered one the webserver and not on the DB server. As I have morte than 100000 entities and display only 20 per page it taked realy long.

To have paging on the server, you have to specify where to do the paging at the datasourcecontrol, as well as on the grid. The specification on the datasource control is for the control to pass the paging variables to the fetch, the grid specification is for that the grid actually shows the data in pages.

It's a bit unclear to me if you use livepersistence == true on the datasourcecontrol (so the fetch call is done by the control) or have eventhandlers to call the fetch method. In case of the former, you have to specify the paging on the datasourcecontrol. In case of the latter be sure to pass the values to the fetch method. If you use the latter and pass the page values, paging WILL happen, even if the query doesn't show paging parameters, it will fetch the page from the datareader and close the reader afterwards, not fetching more rows than needed.