Process custom KPI calculations for IQueryable<T> interface exposed

Question

I have IQueryable<T> interface exposed by an out of process Caching framework (Apache-Ignite), for which my processing requirement is to filter, sort, paginate the data and also for filtered data numeric columns (int, double), calculate the summary KPIs like sum, min, max, avg as that needs to be shown in the Ui at the end of the call.

Example would be, for a Type Person:

public class Person
{
  public string Name {get;set;}

  public int Age {get;set;}

  public double Salary {get;set;}
}

My query looks like:

IQueryable<Person> personQueryable = <Fetch Queryable API>

var result = personQueryable.AsParallel()   // For Parallel Processing
                            .Where(// Apply Filter) // Filter Data
                            .OrderBy(// Order By Clause) // Sort Data
                            .Take(// Pagination) // Take a Subset of Data

Now the challenge, that I have though I want to Calculate the Summary KPI (sum, min, max, avg), for the numeric columns Age,Salary, but that needs to be done on the filtered records, not the paginated, so how to accomplish the same.

One option is to bring all the filtered records into active memory (List) then calculate the KPIs, though at times filtered data could be > 100 K, thus making the response slow, any idea on how to achieve it.

In my view, I need to return a wrapper object, which at run-time calculates the KPI dynamically based on numeric columns passed, which can be stored in a Dictionary, with column name as key and KPI calls as value with all the calculations for a given column.

Also please note, currently we use In process Cache, so we do all the processing on IEnumrable<T>, using custom extension methods, another point I am thinking is can I simply replace IEnumrable<T> by IQueryable<T> and all the extension methods will work as is though at the out of process Cache level and I will get the final results when data is serialized to Ui. .

Answer 1

It is not possible to retrieve one page of the data and aggregates over the whole data set in one go. You have to do two calls, one to get paged data, and one to calculate aggregates.

Looks like you use Ignite LINQ, so queries are already configured. In this case just do a separate SQL query:

personCache.QueryFields(new SqlFieldsQuery("select min(age), max(age), avg(age), sum(age), min(salary), max(salary), avg(salary), sum(salary) from person"))