.ToDataSourceResult ignores paging if filtering or grouping is used

[Bernd]

Hello.

I am using the UI for ASP.NET MVC Grid version 2016.3.914.545 with Telerik DataAccess version 2016.1.224.1 with a MSSQLLocalDB for development.

As long as there is no filter or group used in the grid everything is working. If the user filters, even a simple "=", the request times out. Using the DataAccess Profiler I found the reason. The generated SQL statement reads all records. Not only the required 10. Which takes about three minutes with subsequent requests to get the detailed data.

The grid config in my view:

01.@(Html.Kendo().Grid<CalendarEventViewModel>()
02.    .Name(componentName: "eventGrid")
03.    .Pageable()
04.    .Scrollable(s => { s.Height(value: "auto"); s.Enabled(value: true); })
05.    .Filterable()
06.    .Groupable()
07.    .Sortable(s => s.Enabled(value: true))
08.    .Resizable(r => r.Columns(value: true))
09.    .Selectable()
10.    .Columns(columns => {
11.        columns.Bound(c => c.Operation.OperationIdFormatted)
12.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.MasterSystemId)}" })
13.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.MasterSystemId)}", resourceSet: ResourceSet.Properties));
14.        columns.Bound(c => c.Title)
15.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.Title)}" })
16.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.Title)}", resourceSet: ResourceSet.Properties));
17.        columns.Bound(c => c.Workplace.Label)
18.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.Workplace)}" })
19.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.Workplace)}", resourceSet: ResourceSet.Properties));
20.        columns.Bound(c => c.Description)
21.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.Description)}" })
22.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.Description)}", resourceSet: ResourceSet.Properties));
23.        columns.Bound(c => c.StartWeek)
24.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.StartWeek)}" })
25.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.StartWeek)}", resourceSet: ResourceSet.Properties));
26.        columns.Bound(c => c.EndWeek)
27.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.EndWeek)}" })
28.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.EndWeek)}", resourceSet: ResourceSet.Properties));
29.        columns.Bound(c => c.Start).Format(value: "{0:F}")
30.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.Start)}" })
31.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.Start)}", resourceSet: ResourceSet.Properties));
32.        columns.Bound(c => c.End).Format(value: "{0:F}")
33.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.End)}" })
34.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(CalendarEventViewModel)}.{nameof(CalendarEventViewModel.End)}", resourceSet: ResourceSet.Properties));
35.        columns.Bound(c => c.Operation.TimeSpanPerUnit)
36.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.TimePerUnit)}" })
37.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.TimePerUnit)}", resourceSet: ResourceSet.Properties))
38.            .ClientTemplate(value: "#if (data.TimeSpanPerUnit) {# #:kendo.toString(TimeSpanPerUnit.Hours, '00')#:#:kendo.toString(TimeSpanPerUnit.Minutes, '00')#:#:kendo.toString(TimeSpanPerUnit.Seconds, '00')# #}#");
39.        columns.Bound(c => c.Operation.QuantityToProduce).Format(value: "{0:n}")
40.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.QuantityToProduce)}" })
41.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.QuantityToProduce)}", resourceSet: ResourceSet.Properties));
42.        columns.Bound(c => c.Operation.QuantityConfirmed).Format(value: "{0:n}")
43.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.QuantityConfirmed)}" })
44.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.QuantityConfirmed)}", resourceSet: ResourceSet.Properties));
45.        columns.Bound(c => c.Operation.QuantityOpen).Format(value: "{0:n}")
46.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.QuantityOpen)}" })
47.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.QuantityOpen)}", resourceSet: ResourceSet.Properties));
48.        columns.Bound(c => c.Operation.Length)
49.            .HeaderHtmlAttributes(new { data_resource_set = ResourceSet.Properties, data_resource_id = $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.Length)}" })
50.            .Title(Html.GetGlobalResourceString(resourceKey: $"{nameof(OperationViewModel)}.{nameof(OperationViewModel.Length)}", resourceSet: ResourceSet.Properties))
51.            .ClientTemplate(value: "#if (data.Length) {# #:kendo.toString(Length.Hours, '00')#:#:kendo.toString(Length.Minutes, '00')#:#:kendo.toString(Length.Seconds, '00')# #}#")
52.            .EditorTemplateName(templateName: "TimeSpan");
53.    })
54.    .DataSource(d => d.Ajax().Model(m => {
55.            m.Id(f => f.Id);
56.            m.Field(f => f.Id).Editable(enabled: false);
57.        })
58.        .Events(e => e.Error(handler: "scheduler_error"))
59.        .Read(read => read.Route(SchedulerControllerRoute.PostOperationEvents, new RouteValueDictionary { { "culture", UICulture.ToLower() } }))
60.        .Update(update => update.Route(SchedulerControllerRoute.PostUpdate, new RouteValueDictionary { { "culture", UICulture.ToLower() } }))
61.        //.Destroy(destroy => destroy.Route(OperationControllerRoute.PostDestroy, new RouteValueDictionary { { "culture", UICulture.ToLower() } }))
62.        .Create(create => create.Route(SchedulerControllerRoute.PostCreate, new RouteValueDictionary { { "culture", UICulture.ToLower() } }))
63.    )
64.    .ClientDetailTemplateId(id: "operationDetailTemplate")
65.    .Events(e => e.DataBound(handler: "dispatcher.onGridDataBound"))
66.    .Deferred()
67.)

The read service:

01.public IQueryable<CalendarEventViewModel> OperationEvents() {
02.    IQueryable<CalendarEventViewModel> result = new CalendarEventViewModel[] { }.AsQueryable();
03.    FetchStrategy oldStrategy = _db.FetchStrategy;
04.    var strategy = new FetchStrategy();
05.    strategy.LoadWith<WorkOrder>(w => w.Endproduct);
06.    _db.FetchStrategy = strategy;
07.    PreferenceProfile activeProfile = HttpContext.Current.Session.Get<PreferenceProfile>(SessionKey.ActiveProfile);
08.    if (activeProfile != null) {
09.        string masterSystemId = activeProfile.Company.MasterSystemId;
10.        result =_db.CalendarEvents.Where(ce => ce.Operation != null && ce.Operation.MasterSystemHierarchy==masterSystemId && !filteredStates.Contains(ce.Operation.ProcessingStatus) && ce.Operation.WorkOrder.QuantityToProduce-ce.Operation.QuantityConfirmed > 0)
11.            .Select(ce => _mapper.Map<CalendarEventViewModel>(ce));
12.    }
13.    _db.FetchStrategy = oldStrategy;
14.    return result;
15.}

In the controller itself nothing special happens:

1.public JsonResult OperationEvents([DataSourceRequest] DataSourceRequest request) {
2.    IQueryable<CalendarEventViewModel> result = ((ISchedulerEventService) crudService).OperationEvents();
3.    return Json(result.ToDataSourceResult(request));
4.}

The SQL generated after setting a filter:

01.SELECT
02.    a.[Id] AS COL1,
03.    a.[ActivityStatus] AS COL2,
04.    a.[BackgroundColor] AS COL3,
05.    a.[EventType] AS COL4,
06.    a.[Description] AS COL5,
07.    a.[en] AS COL6,
08.    a.[EventType] AS COL7,
09.    a.[IsAllDay] AS COL8,
10.    a.[OperationId] AS COL9,
11.    a.[OperationId] AS COL10,
12.    a.[RecurrenceException] AS COL11,
13.    a.[RecurrenceRule] AS COL12,
14.    a.[strt] AS COL13,
15.    a.[Status] AS COL14,
16.    a.[Title] AS COL15,
17.    a.[WorkplaceId] AS COL16,
18.    a.[WorkplaceId] AS COL17
19.
20.FROM [CalendarEvent] a
21.
22.JOIN [Operation] AS b
23.  ON (a.[OperationId] = b.[Id])
24.
25.JOIN [WorkOrder] AS c
26.  ON (b.[WorkOrderId] = c.[Id])
27.
28.WHERE a.[OperationId] IS NOT NULL
29.  AND b.[MasterSystemHierarchy] = @p0
30.  AND NOT ((b.[ProcessingStatus] IN (@p1, @p2)))
31.  AND c.[QuantityToProduce] - b.[QuantityConfirmed] > 0
32.
33.ORDER BY COL1

The grid filter, Workplace.Label="Something", is missing and the TOP() statement is missing. Seems Like ToDataSourceResult wants to laod everything into memory and do the filtering itself.

How can I convince ToDataSourceResult from taking only the needed and filterd 10 rows? Does it help if I add the filters in my where clause as request filters?

Side note for the DataAccess team: I configured logging of the stack trace, but the profiler says there is none. My configuration:

config.Logging.LogEvents = LoggingLevel.Verbose;
config.Logging.StackTrace = true;
config.Logging.EventStoreCapacity = 10000;
config.Logging.MetricStoreCapacity = 3600;
config.Logging.MetricStoreSnapshotInterval = 1000;
config.Logging.Downloader.EventText = true;
config.Logging.Downloader.EventPollSeconds = 1;
config.Logging.Downloader.MetricPollSeconds = 1;
config.Logging.Downloader.Filename = "C:\\Temp\\OALog";
config.Logging.Downloader.EventBinary = true;
config.Logging.Downloader.MetricBinary = true;

Kind regards

Bernd

[Maria Ilieva]

Hello,

Note that The ToDataSourceResult() method will page, sort, filter, and group the collection that is passed to it. If this collection is already paged, the method returns an empty result. To perform the data operations manually, use custom binding instead.

Regards,
Maria Ilieva
Telerik by Progress

[Bernd]

Hello Maria.

First of all thank you for taking the time answering me and pointing me to the custom binding.

But I am sorry. I don't understand what's forcing the custom binding here.

The collection I pass to ToDataSourceResult isn't paged or grouped. According to your answer, this should work. The initial loading of the grid proofs it. ToDataSourceResult pages it as expected.

Please help me to better understand ToDataSourceResult and avoid the need of a custom binding.

Kind regards

Bernd

[Daniel]

Hello Bernd,

Can you please isolate the problem in a simple, runnable example that we could debug locally? This should help us find the exact reason for this behavior. I would suggest that you attach your code to a support ticket rather than in the forum here.

Regards,
Daniel
Telerik by Progress

[Bernd]

Hello Daniel.

Thank you for your reply.

Meanwhile I did some more research and solved the problem.

Line 11 in my service snippet as the problem. The call to AutoMapper triggered the SQL loading everything without additional filtering and paging. Submitting the call to the mapper as selector in the ToDataSourceResult call solved this issue.

Thanks for your help.

Kind regards

Bernd