locked
Recommended architecture for combining database queries and pagination through Web API? RRS feed

  • Question

  • User-2031132340 posted

    Hello,

    I have a SQL Server database on my backend, and in front of it a placed a WebAPI 2 service.  The web api is using Entity Framework to query the database.  I have a client application in WPF that I'm using to call the web service  and get records for view and editing.  So far I implemented a controller in my web api to do pagination according to this post

    https://www.c-sharpcorner.com/article/how-to-do-paging-with-asp-net-web-api/

    My Controller looks like this

            // GET: api/Reports/GetReportsPage
            //
            // example http://localhost/ReportsAPI/api/Reports/GetReportsPage?&pageNumber=2&pageSize=5
            [HttpGet]
            [Route("api/Reports/GetReportsPage")]
            public IEnumerable<ReportsDTO.ReportDTO> GetReportsPage([FromUri]PagingParameterModel pagingparametermodel)
            {
                // Return List of Reports  
                var source = (from reports in db.Reports.
                                OrderBy(a => a.id)
                              select reports).AsQueryable();
    
                // Get's No of Rows Count   
                int count = source.Count();
    
                // Parameter is passed from Query string if it is null then it default Value will be pageNumber:1  
                int CurrentPage = pagingparametermodel.pageNumber;
    
                // Parameter is passed from Query string if it is null then it default Value will be pageSize:20  
                int PageSize = pagingparametermodel.pageSize;
    
                // Display TotalCount to Records to User  
                int TotalCount = count;
    
                // Calculating Totalpage by Dividing (No of Records / Pagesize)  
                int TotalPages = (int)Math.Ceiling(count / (double)PageSize);
    
                // Returns List of Customer after applying Paging   
                var items = source.Skip((CurrentPage - 1) * PageSize).Take(PageSize).ToList();
    
                // if CurrentPage is greater than 1 means it has previousPage  
                var previousPage = CurrentPage > 1 ? "Yes" : "No";
    
                // if TotalPages is greater than CurrentPage means it has nextPage  
                var nextPage = CurrentPage < TotalPages ? "Yes" : "No";
    
                // Object which we are going to send in header   
                var paginationMetadata = new
                {
                    totalCount = TotalCount,
                    pageSize = PageSize,
                    currentPage = CurrentPage,
                    totalPages = TotalPages,
                    previousPage,
                    nextPage
                };
    
                // Setting Header  
                System.Web.HttpContext.Current.Response.Headers.Add("Paging-Headers", Newtonsoft.Json.JsonConvert.SerializeObject(paginationMetadata));
    
    
                // Returing List of Reports DTO Collection
                List<ReportsDTO.ReportDTO> l = new List<ReportsDTO.ReportDTO>();
                foreach(var item in items)
                {
                    l.Add(TranslateDTO.ConvertReportToDTO(item));
                }
                return l;
            }

    And I call the service in my WPF app like this

                string responseString = string.Empty;
                string url = "http://localhost/ReportsAPI/api/Reports/GetReportsPage?&pageNumber=" + pageNumber + @"&pageSize=" + pageSize;
    
                var response = client.GetAsync(url).Result;
                if (response.IsSuccessStatusCode)
                {
                    responseString = response.Content.ReadAsStringAsync().Result;
                }

    var pagingHeaders = (Newtonsoft.Json.Linq.JObject)Newtonsoft.Json.JsonConvert.DeserializeObject(response.Headers.GetValues("Paging-Headers").FirstOrDefault());

    var reports = Newtonsoft.Json.JsonConvert.DeserializeObject<List<ReportsDTO.ReportDTO>>(responseString);

    But now I want to do more complicated things like sort on a column, and introduce searches using 'LIKE' and 'CONTAINS' and also Semantic search (I enabled full text query on the database with semantic search)

    How do I combine the queries I want to do with the pagination controller.  

    Should I add all possible properties I want to the PaginationModel class and just send everything in a single JSON object including query strings, sort columns and order etc?

    What about this part in my controller?

                // Return List of Reports  
                var source = (from reports in db.Reports.
                                OrderBy(a => a.id)
                              select reports).AsQueryable();

    If I include the other parts to the query I would have to modify the query above to include everything I passed to it and it would repeat it every time I change pages?

    What is the recommended approach for this?

    Friday, May 3, 2019 6:42 PM

All replies

  • User1120430333 posted

    IMHO, paging is paging, The sorting and searching could be done against the results returned to the client on the clinet-side. To be honest., I would just handle the paging based on the results returened to the client-side and not do it on the WebAPI side, because the results can be kept in state in memory due to this is a desktop solution using the power of the workstation computer on the client-side.   There would be a limit on how much data could be retuned on a search as an example.

     

    Friday, May 3, 2019 10:19 PM
  • User-2031132340 posted

    I don't think I can handle paging on the client side, if I understand what you are saying, that would mean returning the entire result set to the client.  But what if those results contain large objects (like images or large amounts of text) and there were thousands if not millions in the result.  It wouldn't make much sense to offload the entire set to the client.. 

    Saturday, May 4, 2019 1:47 AM
  • User-474980206 posted
    If your webapi needs to support more than some sim0,e canned queries, then you need to define a query language. You could go with a json structure like mongodb, but graphql apis are the choice now. There are entity mapping libraries, see for example


    https://github.com/SimonCropp/GraphQL.EntityFramework




    Saturday, May 4, 2019 3:18 AM
  • User1120430333 posted

    I don't think I can handle paging on the client side, if I understand what you are saying, that would mean returning the entire result set to the client.  But what if those results contain large objects (like images or large amounts of text) and there were thousands if not millions in the result.  It wouldn't make much sense to offload the entire set to the client.. 

    You set a limitation on the service-side as to the amount of data that can be brought back from the service.    You also use DTO(s) to shape what needs to comeback. 

    https://docs.microsoft.com/en-us/aspnet/web-api/overview/data/using-web-api-with-entity-framework/part-5

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    What should be coming back from the service is a list of custom objects. So I don't see why you can't  use Linq doing paging on the returned collection.

    https://www.c-sharpcorner.com/article/how-to-implement-paging-using-skip-and-take-operators-in-linq/

    The DTO(s) can be kept in a classlib project, name it whatever you want, and all projects have reference to the classlib project and know about the DTO(s)

    Saturday, May 4, 2019 3:59 AM
  • User-2031132340 posted

    I already did the part about keeping the DTO in a separate classlib project that is referenced by all projects that need it.  

    My DTO contains the large item, are you saying its not supposed to?  

    Monday, May 6, 2019 2:04 PM
  • User-474980206 posted

    your DTO needs to support searching, and pagination. Once you have decided on a search api for your DTO, then the webapi can expose it.

    Monday, May 6, 2019 2:46 PM
  • User1120430333 posted

    I already did the part about keeping the DTO in a separate classlib project that is referenced by all projects that need it.  

    My DTO contains the large item, are you saying its not supposed to?  

    The DTO can hold a large item, but you also said the you could potentially pull back a large amount of DTO(s) in a collection. I say there should be set limitations on just how many DTO can be returned based on quantity and/or total size/length of the collection, like the user should not be allowed to bring back 500K, 1.5 million objects, etc. and etc. some kind of manageable amount should be returned. The search should be using criteria that allows subsets of results to be returned.

    Monday, May 6, 2019 6:12 PM