Get count of filtered data without casting?

[Vinayak]

Hi,

I have implemented server-side paging and filtering in my grid.

This is my C# method...

        public ActionResult DataSource(DataManagerRequest dm)
        {
            IEnumerable data = db.RegisteredVoters;
            DataOperations operation = new DataOperations();
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting
            {
                data = operation.PerformSorting(data, dm.Sorted);
            }
            if (dm.Where != null && dm.Where.Count > 0) //Filtering
            {
                WhereFilter whereFilter = dm.Where.First();
                data = operation.PerformFiltering(data, dm.Where, whereFilter.Operator);
            }
            int count = data.Cast<RegisteredVoter>().Count();
            if (dm.Skip != 0)
            {
                data = operation.PerformSkip(data, dm.Skip);
            }
            if (dm.Take != 0)
            {
                data = operation.PerformTake(data, dm.Take);
            }
            return Json(new { result = data, count = count });
        }

The line that I have underlined, converts the ENTIRE search results into a list and then gets the count. This is very inefficient when my search returns 1 million records. This completely negates the reason I have implemented server-side paging and filtering.

Is there any way to ONLY get the count of the current filter query without converting all the items into a list?

Please do let me know.

Regards,

pixie.

[Sujith Kumar Rajkumar]

Hi Vinayak, 

 

Greetings from Syncfusion support. 

 

Based on the query we would like to let you know that the total records count value is required by the pager when pagination is enabled in the Grid. The Grid request will be sent with the skip and take values which allows to fetch the required page size data alone and return it back to the Grid along with the total data count. This total data count is required for updating the total record count in the pager component. And so the data needs to be returned back from the server as an object of result and count properties to update in the Grid. However only the current processed data based on the skip, take count will be processed and returned back as data source. This is the default behavior of the EJ2 Data Manager and DataGrid and so you need to retrieve the total count from your data. 

 

Now the count value retrieval is done using LINQ’s cast method and the time delay in fetching the count is a common problem with the LINQ execution when large number of data is present and not related to the Grid control. So pease check the below stackoverflow links where this general case is discussed for more details, 

 

https://stackoverflow.com/questions/28756/the-best-way-to-get-a-count-of-ienumerablet 

https://stackoverflow.com/questions/168901/count-the-items-from-a-ienumerablet-without-iterating 

 

Let us know if you have any concerns. 

 

Regards, 

Sujith R 

[Vinayak]

Hi,

I don't think you have answered my question.

I do know that the count is required for the paging component and I do know that calling the .count() LINQ method iterates ALL the records and gets the count. These two points I already know.

But all the records are not required to be returned for the paging component to work, it only needs the count correct?

I am asking you if there is any functionality that your DataOperations class can provide to achieve this since we both know that iterating 1 million records to get the count is not very efficient.

The DataOperations class is already generating the query from the filters selected. It would be much more efficient and useful if that class also runs a "count *" query with those same filters so that iterating to get the count is then not required.

If you force the users to call the .count() method on the entire result set, then you are negating the whole concept of server-side paging.

Do you understand what I am trying to say?

Is there any solution to this?

Regards,

pix.

[Sujith Kumar Rajkumar]

Hi Vinayak, 

 

Based on the query we would like to let you know that even in the DataOperations class we have used LINQ queries for data processing and execution. As the data and queries can have complex structures we have provided in-built support using the DataOperations class to execute them using its corresponding action methods like, PerformFiltering, PerformSorting, etc.,. But the data count retrieval is a common case where the count has to be fetched by executing the entire data. Even if we provide support to execute this action in the DataOperations class it will be using the sample approach implemented in the application level and so we have not provided in-built support for this case. 

 

So you need to implement this code for fetching the data count and as mentioned in our previous update the time delay occurring is a common problem with the LINQ query execution. This is the default behavior of this case. 

 

Regards, 

Sujith R 

[Vinayak]

Hi,

I'

I'm sorry, but what you said there, is incorrect. You said, "But the data count retrieval is a common case where the count has to be fetched by executing the entire data."

That is not correct as the count may be fetched in two ways as I have mentioned below.

1) The inefficient way:

    1.1) Execute the query in question, SELECT [columns] from [table name] where [filter clauses]

    1.2) Iterate through all the records and get the count

2) What I am suggesting:

    2.1) Have a functionality to get the count of the query directly by executing,  SELECT count(*) from [table name] where [filter clauses]

In the first method, if the page size is 10, and the query returns 1 million records, all 1 million records are returned from the server, just to get the count. This cannot be right!

In the second method, two queries are executed, one which returns just the count as a number, and another that returns just the 10 records. This will be orders of magnitude faster than the first method when large data is concerned.

What I am asking is, does the functionality for the second method currently exist? And if it does not exist, then can it be implemented?

Regards,

pix.

[Sujith Kumar Rajkumar]

Hi Vinayak, 

We are currently validating the query from our end and we will provide the further details on 29^th December 2021. 

Until then your patience is appreciated. 

Regards, 

Sujith R 

[Sujith Kumar Rajkumar]

Hi Vinayak, 

 

Thanks for your patience. 

 

We validated this query further and would like to let you know that with list data the entire data will be processed for retrieving the count which is the behavior. So for your case we suggest you to use queryable data for faster record processing and retrieval. And for queryable data please use the QueryableOperation class to perform the actions instead of DataOperations as shown below, 

 

IQueryable<Orders> DataSource = _context.Orders.AsQueryable(); 

QueryableOperation operation = new QueryableOperation(); 

 

We have prepared a sample based on this for your reference. You can find it below, 

 

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/GridQueryable-277372538 

 

Note: A local MDF file is used in the above sample to bind data to the Grid. So before running the sample, please make sure the connection for this file is established in the server explorer and the correct connection string is provided OrderDbContext.cs model file in the application. 

 

Reference link: https://www.c-sharpcorner.com/UploadFile/a20beb/ienumerable-vs-iqueryable-in-linq/  

 

Let us know if you have any concerns. 

 

Regards, 

Sujith R