1. Home
  2. Forum
  3. Blazor
  4. Blazor Grid with Remote Paging and Searching

Blazor Grid with Remote Paging and Searching

Hi,

I am using Blazor Server Side with Version 18.1.0.42 and a UrlAdaptor to access data for a grid using EF Core from an Azure SQL Server that is working with the controller code shown below.

However, I have some tables that will have thousands of rows and want to convert this controller to handle the sorting and paging on the SQL server instead of retrieving all the records (db.Suppliers.ToList() will retrieve all rows in the database first).

Can you let me know how to modify this controller to do the sorting and paging on the SQL server?

Kind regards,
Stuart

    public class SuppliersController : ControllerBase
    {
        private readonly SkyNetContext db;
        public SuppliersController(SkyNetContext context)
        {
            db = context;
        }

        [HttpPost]
        [Route("api/[controller]")]
        public Object Post([FromBody]DataManagerRequest dm)
        {
            var DataSource = db.Suppliers.ToList();
            if (dm.Search != null && dm.Search.Count > 0)
            {
                DataSource = DataOperations.PerformSearching(DataSource, dm.Search);  //Search
            }
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting
            {
                DataSource = DataOperations.PerformSorting(DataSource, dm.Sorted);
            }
            if (dm.Where != null && dm.Where.Count > 0) //Filtering
            {
                DataSource = DataOperations.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator);
            }
            int count = DataSource.Cast<Supplier>().Count();
            if (dm.Skip != 0)
            {
                DataSource = DataOperations.PerformSkip(DataSource, dm.Skip);   //Paging
            }
            if (dm.Take != 0)
            {
                DataSource = DataOperations.PerformTake(DataSource, dm.Take);
            }
            return dm.RequiresCounts ? new { result = DataSource, count = count } : DataSource as object;
        }
    }

5 Replies
RS Renjith Singh Rajendran Syncfusion Team April 9, 2020 11:17 AM UTC

Hi Stuart, 

Thanks for contacting Syncfusion support. 

Based on your requirement, we suggest you to fetch data from DB as Queryable data instead of List. We have prepared an UrlAdaptor sample based on this requirement, please download the sample from the link below, 

Please refer the code below, 

 
        public object Post([FromBody]DataManagerRequest dm) 
        { 
            var DataSource = db.GetAllOrders().AsQueryable();            
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 
            { 
                DataSource = DataOperations.PerformSorting(DataSource, dm.Sorted); 
            } 
            ... 
           IEnumerable<Order> ResultData = DataSource.ToList();   //We are converting this to List here to prevent unwanted db query executions. 
            return new { result = ResultData, count = count }; 
 
        }        


And to prevent unwanted DB query execution calls, we have converted the resultant Queryable data to List in the Post method before returning to Grid. 

Please refer the above attached sample and the codes and get back to us if you need further assistance. 

Regards, 
Renjith Singh Rajendran. 


ST Stuart April 10, 2020 05:18 AM UTC

Thanks for your help on this Renjith,

This all seems to be working well for me.

I appreciate your help!

Kind regards,
Stuart


RS Renjith Singh Rajendran Syncfusion Team April 13, 2020 04:13 AM UTC

Hi Stuart, 

Thanks for your update. 

We are glad to hear that the provided solution helped you in achieving your requirement. 

Please get back to us if you need further assistance. 

Regards, 
Renjith Singh Rajendran. 


FI Filip December 19, 2020 02:32 PM UTC

Hi,

I found this post when searching for a way to only query the database for amount of records we want when we use paging or something else
I've downloaded the presented solution but the line var DataSource = db.GetAllOrders().AsQueryable(); still triggers the OrderAccessDataLayer to query the database for all orders. Maybe it filters after that but still all are requested or not?

I would rather expect that I can call something like OrdersPaged(int page, int rows, string conditions = "", string orderby="", object parameters=null) where I can send my page, sort, search variables to 

What am I missing here?

In my case it's a blazor website, the data access layer is using Dapper & Dapper.SimpleCrud 

Kind regards
Filip

RS Renjith Singh Rajendran Syncfusion Team December 24, 2020 03:08 AM UTC

Hi Filip, 

Greetings from Syncfusion support. 

We are not clear about your exact requirement or the problem you are facing. We suspect that you would like to send the request to controller only on initial Grid data loading, and you want all other dataoperations to be performed with the fetched data instead of calling the Post method in controller each time.  

If so, then we suggest you to use the Offline property of SfDataManager. Using the Offline property enabled in SfDataManager, only on initial post will be send to server to fetch the data from server and all other Data operations(paging/sort/filter/search) and CRUD operations will be handled in client side. 

 
<SfGrid @ref="Grid" ID="Grid" TValue="Order" ...> 
    <SfDataManager Url="/api/Default" Adaptor="Adaptors.UrlAdaptor" Offline="true"></SfDataManager> 
    ... 
</SfGrid> 


If we have misunderstood your requirement or if you are still facing difficulties then the following details would be helpful for us to proceed further. 
  1. Share with us a detailed explanation of the problem you are facing.
  2. Share the detailed explanation of your complete requirement.
  3. Share the complete Grid codes and the adaptor you are using to bind data to Grid.

The provided information will help us analyze the problem, and provide you a solution as early as possible. 

Reference :  

Regards, 
Renjith Singh Rajendran 


Loader.
Up arrow icon