For Datatables.

GetTrackerPaginatedList.cs

/// <summary>
/// Retrieves a paginated list of trackers for a specified request, supporting sorting, searching, and pagination.
/// Returns a JSON result with paginated data compatible with DataTables.
/// See: <see cref="ListedTrackerDto"/>.
/// </summary>
/// <param name="requestId">ID of the request for which trackers are being retrieved.</param>
/// <returns>JSON result containing paginated tracker data, record count, and error information if applicable.</returns>
[HttpPost]
public virtual ActionResult GetTrackerPaginatedList(int requestId) {
  var watch = Stopwatch.StartNew();

  try {
    Debug.WriteLine($"TrackerController.GetTrackerPaginatedList START: {watch.Elapsed}");

    int userId = _accountService.UserID;
    // https://datatables.net/forums/discussion/40690/sample-implementation-of-serverside-processing-in-c-mvc-ef-with-paging-sorting-searching

    // Retrieve DataTables parameters from the request form with safe fallback values
    string draw = Request.Form["draw"] ?? string.Empty;
    int start = int.TryParse(Request.Form["start"], out var st) ? st : 0;
    int pageSize = int.TryParse(Request.Form["length"], out var len) ? len : 10;
    string sortColumn = Request.Form[$"columns[{Request.Form["order[0][column]"]}][name]"] ?? string.Empty;
    string sortDirection = Request.Form["order[0][dir]"] ?? "asc";
    string searchedValue = Request.Form["search[value]"] ?? string.Empty;
    var searchPanesOptions = Request.Form["searchPanes_options"];

    var spTrackerProvinceCode = Request.Form["searchPanes[TrackerProvinceCode][0]"];
    var spTrackerYearCode = Request.Form["searchPanes[TrackerYearCode][0]"];
    var spTrackerRequestCode = Request.Form["searchPanes[TrackerRequestCode][0]"];
    var spTrackerSimpleCode = Request.Form["searchPanes[TrackerSimpleCode][0]"];
    var spMineralName = Request.Form["searchPanes[MineralName][0]"];
    var spExpeditionDate = Request.Form["searchPanes[ExpeditionDate][0]"];
    var spExpeditionTime = Request.Form["searchPanes[ExpeditionTime][0]"];
    var spMineralQuantity = Request.Form["searchPanes[MineralQuantity][0]"];

    Debug.WriteLine($"> TrackerProvinceCode: {(spTrackerProvinceCode.IsEmpty() ? "No-value-0" : spTrackerProvinceCode)}");
    Debug.WriteLine($"> spTrackerYearCode: {(spTrackerYearCode.IsEmpty() ? "No-value-1" : spTrackerYearCode)}");
    Debug.WriteLine($"> spTrackerRequestCode: {(spTrackerRequestCode.IsEmpty() ? "No-Value-2" : spTrackerRequestCode)}");
    Debug.WriteLine($"> spTrackerSimpleCode: {(spTrackerSimpleCode.IsEmpty() ? "No-Value-3" : spTrackerSimpleCode)}");
    Debug.WriteLine($"> spMineralName: {(spMineralName.IsEmpty() ? "No-Value-4" : spMineralName)}");
    Debug.WriteLine($"> spExpeditionDateTime: {(spExpeditionDate.IsEmpty() ? "No-Value-5" : spExpeditionDate)}");
    Debug.WriteLine($"> spExpeditionDateTime: {(spExpeditionTime.IsEmpty() ? "No-Value-5" : spExpeditionTime)}");
    Debug.WriteLine($"> spMineralQuantity: {(spMineralQuantity.IsEmpty() ? "No-Value-6" : spMineralQuantity)}");

    // Query the tracker list with deferred execution and user/request scope
    var listedTrackers = _trackerService.GetListedTrackerQueryable(userId, requestId);

    // ADD SEARCH PANES OPTIONS
    var trackerProvinceCodeArray = listedTrackers.GroupBy(tracker => tracker.TrackerProvinceCode).Select(group => SearchPaneField(group)).ToArray();
    var trackerYearCodeArray = listedTrackers.GroupBy(tracker => tracker.TrackerYearCode).Select(group => SearchPaneField(group)).ToArray();
    var trackerRequestCodeArray = listedTrackers.GroupBy(tracker => tracker.TrackerRequestCode).Select(group => SearchPaneField(group)).ToArray();
    var trackerSimpleCodeArray = listedTrackers.GroupBy(tracker => tracker.TrackerSimpleCode).Select(group => SearchPaneField(group)).ToArray();
    var mineralNameArray = listedTrackers.GroupBy(tracker => tracker.MineralName).Select(group => SearchPaneField(group)).ToArray();
    var expeditionDateArray = listedTrackers.GroupBy(tracker => tracker.ExpeditionDate.ToString() ?? "No value").Select(group => SearchPaneField(group)).ToArray();
    var expeditionTimeArray = listedTrackers.GroupBy(tracker => tracker.ExpeditionTime.ToString() ?? "No value").Select(group => SearchPaneField(group)).ToArray();
    var mineralQuantitArray = listedTrackers.GroupBy(tracker => tracker.MineralQuantity).Select(group => SearchPaneField(group)).ToArray();

    var searchPanesResult = new { options = new {
      TrackerProvinceCode = trackerProvinceCodeArray,
      TrackerYearCode = trackerYearCodeArray,
      TrackerRequestCode = trackerRequestCodeArray,
      TrackerSimpleCode = trackerSimpleCodeArray,
      MineralName = mineralNameArray,
      ExpeditionDate = expeditionDateArray,
      ExpeditionTime = expeditionTimeArray,
      MineralQuantity = mineralQuantitArray,
    } };

    listedTrackers = listedTrackers.Where(m => string.IsNullOrEmpty(spTrackerProvinceCode) || m.TrackerYearCode.Contains(spTrackerProvinceCode)).Where(m => string.IsNullOrEmpty(spTrackerYearCode) || m.TrackerYearCode.Contains(spTrackerYearCode)).Where(m => string.IsNullOrEmpty(spTrackerRequestCode) || m.TrackerRequestCode.Contains(spTrackerRequestCode)).Where(m => string.IsNullOrEmpty(spTrackerSimpleCode) || m.TrackerSimpleCode.Contains(spTrackerSimpleCode)).Where(m => string.IsNullOrEmpty(spMineralName) || m.MineralName.Contains(spMineralName)).Where(m => string.IsNullOrEmpty(spExpeditionDate) || m.ExpeditionDate.Contains(spExpeditionDate)).Where(m => string.IsNullOrEmpty(spExpeditionTime) || m.ExpeditionTime.Contains(spExpeditionTime)).Where(m => string.IsNullOrEmpty(spMineralQuantity) || m.MineralQuantity == spMineralQuantity);

    // Apply search filter if a search value is provided
    if (!string.IsNullOrEmpty(searchedValue)) {
      listedTrackers = listedTrackers.Where(m => m.TrackerProvinceCode.Contains(searchedValue) || m.TrackerYearCode.Contains(searchedValue) || m.TrackerRequestCode.Contains(searchedValue) || m.TrackerSimpleCode.Contains(searchedValue) || m.MineralName.Contains(searchedValue) || m.ExpeditionDate.Contains(searchedValue) || m.ExpeditionTime.Contains(searchedValue) || m.MineralQuantity.Contains(searchedValue));
    }

    // Get total record count after applying filters
    int recordsTotal = listedTrackers.Count();

    // Apply sorting if sort parameters are provided
    if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortDirection)) {
      listedTrackers = listedTrackers.OrderBy($"{sortColumn} {sortDirection}");
    }

    // Apply pagination or retrieve all records if pageSize is negative
    var paginatedTrackers = pageSize < 0 ? listedTrackers.ToList() : listedTrackers.Skip(start).Take(pageSize).ToList();

    Debug.WriteLine($"TrackerController.GetTrackerPaginatedList END: {watch.Elapsed}");

    // Return result in DataTables format
    return Json(new { draw, recordsFiltered = recordsTotal, recordsTotal, data = paginatedTrackers, searchPanes = searchPanesResult });
  } catch (Exception ex) {
    // Log the exception with additional context
    string errorId = LogHelper.LogError(ex, "Tracker", "GetTrackerPaginatedList");

    // Return an error response with a user-friendly message
    return Json(new { type = Config.ReturnViewType.Alert, data = string.Format(Resources.Global.TRY_CATCH_ERROR_MESSAGE, errorId) });
  }
}

private object SearchPaneField(IGrouping<string, ListedTrackerDto> group) {
  return new { label = group.Key, total = group.Count(), value = group.Key, count = group.Count() };
}