.ToDataSourceResult ignores paging if filtering or grouping is used

5 posts, 0 answers
  1. Bernd
    Bernd avatar
    53 posts
    Member since:
    Feb 2013

    Posted 19 Sep Link to this post

    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

  2. Maria Ilieva
    Admin
    Maria Ilieva avatar
    4017 posts

    Posted 21 Sep Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. UI for ASP.NET MVC is VS 2017 Ready
  4. Bernd
    Bernd avatar
    53 posts
    Member since:
    Feb 2013

    Posted 21 Sep in reply to Maria Ilieva Link to this post

    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

  5. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 23 Sep Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  6. Bernd
    Bernd avatar
    53 posts
    Member since:
    Feb 2013

    Posted 23 Sep in reply to Daniel Link to this post

    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

Back to Top
UI for ASP.NET MVC is VS 2017 Ready