Pagination issue: returning total count and total pages in body

Question

0.00/5 (No votes)

See more:

I have a search function where I can either fill in a value for exempel: registration number and get data about it, or if i don't enter any parameters, i will get all of the data, with 100 rows per page. Now, I have also created a sort of count where I can see how many rows there is in total, and how many pages this will be if every page has 100 rows. The problem is that I want to return the total count and total pages in the body of the request, so for example here's how a request answer can look:

[
    {
        "claimHandlingStatus": "HANDLING",
        "requestDate": "2021-09-20",
        "requestID": 118095,
        "countryID": 1,
        "regNumber": "CNK258365",
        "produkt": "Produkt 54",
        "workshop": "Workshop 55",
        "asignee": null,
        "claimId": "387385",
        "vinnumber": ""
    },
    {
        "claimHandlingStatus": "HANDLING",
        "requestDate": "2021-09-203",
        "requestID": 118094,
        "countryID": 1,
        "regNumber": "CNK258365",
        "produkt": "Produkt 10",
        "workshop": "Workshop 3",
        "asignee": null,
        "claimId": "387384",
        "vinnumber": ""
    }

]

And instead of adding 2 rows with total count and total pages in every answer i would like to get these just once, at the top of the request answer in body, so it could look something like this:

[
     {
      "totalcount" : "300",
      "totalpages" : "3"
      },
    {
        "claimHandlingStatus": "HANDLING",
        "requestDate": "2021-09-20",
        "requestID": 118095,
        "countryID": 1,
        "regNumber": "CNK258365",
        "produkt": "Produkt 54",
        "workshop": "Workshop 55",
        "asignee": null,
        "claimId": "387385",
        "vinnumber": ""
    },
    {
        "claimHandlingStatus": "HANDLING",
        "requestDate": "2021-09-203",
        "requestID": 118094,
        "countryID": 1,
        "regNumber": "CNK258365",
        "produkt": "Produkt 10",
        "workshop": "Workshop 3",
        "asignee": null,
        "claimId": "387384",
        "vinnumber": ""
    }

]

Here's the C# method for the search function:

public async Task<IEnumerable<GetRequestModelOut>> GetRequest2(GetRequestModel model, int pageNumber = 1)
       {
           var parameters = new DynamicParameters();

           parameters.Add("@RegNumber", model.RegNumber == string.Empty ? null :
           model.RegNumber);
           parameters.Add("@Vinnumber", model.Vinnumber == string.Empty ? null :
           model.Vinnumber);
           parameters.Add("@CountryID", model.CountryId == -1 ? (int?)null :
           model.CountryId);
           parameters.Add("@assigneid", model.AssigneId == string.Empty ? null :
           model.AssigneId);
           parameters.Add("@dateFrom", model.dateFrom);
           parameters.Add("@dateTo", model.dateTo);
           parameters.Add("@pageSize", model.pageSize);
           parameters.Add("@PageNumber", model.PageNumber);
           //parameters.Add("@totalCount", model.totalCount);
           //parameters.Add("@totalPages", model.totalPages);

           var getCount = await _sqlconnection.QueryAsync<int>($@"
           SELECT COUNT(Claim.ID)
           FROM Request
           INNER JOIN RequestCrossClaim ON Request.ID =
           RequestCrossClaim.RequestID
           INNER JOIN Claim ON RequestCrossClaim.ClamID = Claim.ID
           INNER JOIN Contract ON Request.ContractID = Contract.ID
           INNER JOIN Vehicle ON Contract.VehicleID = Vehicle.ID
           INNER JOIN Product ON Contract.ProductID = Product.ID
           INNER JOIN Retailer ON Contract.RetailerID = Retailer.ID
           INNER JOIN ClaimHandlingStatus ON Claim.ClaimHandlingStatusID =
           ClaimHandlingStatus.ID
           where (@RegNumber IS NULL OR RegNumber = @Regnumber)
           AND (@Vinnumber IS NULL OR Vinnumber = @Vinnumber)
           AND (@CountryID IS NULL OR Contract.CountryID = @CountryID)
           AND (@assigneid IS NULL OR Claim.CreatedBy = @assigneid)
           AND (@dateFrom IS NULL OR Request.CreatedDate BETWEEN @dateFrom AND
           @dateTo)
           ", parameters);

           model.totalCount = getCount.First();
           model.totalPages = (int)Math.Ceiling(model.totalCount /
           (double)model.pageSize);


           var getReq1 = await _sqlconnection.QueryAsync<GetRequestModelOut>($@"
           SELECT
           ClaimHandlingStatus.Name as ClaimHandlingStatus,
           Request.CreatedDate as requestDate,
           Request.ID as RequestID, Contract.CountryID,
           RegNumber, Product.Name as produkt,
           Retailer.Name as workshop, Claim.CreatedBy as UserID,
           Claim.ID as claimId,
           Vinnumber
           FROM Request
           INNER JOIN RequestCrossClaim ON Request.ID =
           RequestCrossClaim.RequestID
           INNER JOIN Claim ON RequestCrossClaim.ClamID = Claim.ID
           INNER JOIN Contract ON Request.ContractID = Contract.ID
           INNER JOIN Vehicle ON Contract.VehicleID = Vehicle.ID
           INNER JOIN Product ON Contract.ProductID = Product.ID
           INNER JOIN Retailer ON Contract.RetailerID = Retailer.ID
           INNER JOIN ClaimHandlingStatus ON Claim.ClaimHandlingStatusID =
           ClaimHandlingStatus.ID
           where (@RegNumber IS NULL OR RegNumber = @Regnumber)
           AND (@Vinnumber IS NULL OR Vinnumber = @Vinnumber)
           AND (@CountryID IS NULL OR Contract.CountryID = @CountryID)
           AND (@assigneid IS NULL OR Claim.CreatedBy = @assigneid)
           AND (@dateFrom IS NULL OR Request.CreatedDate BETWEEN @dateFrom
           AND @dateTo)
           ORDER BY requestDate desc OFFSET @pageSize * (@PageNumber-1)
           ROWS FETCH NEXT
           @pageSize ROWS ONLY",
           parameters);

           return getReq1;
       }

Controller:

[HttpGet]
        public async Task<IActionResult> GetRequest2([FromQuery] 
        GetRequestModel model, 
        int pageNumber)
        {

            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }
            try
            {
                var list = await _request.GetRequest2(model, pageNumber);
                /*var metadata = new
                {
                    model.totalCount,
                    model.totalPages
                };*/
                //Response.Headers.Add("X-Pagination", 
                //JsonConvert.SerializeObject(metadata));
                return Ok(list);
            }
            catch (Exception e)
            {
                return BadRequest(e.Message);
            }

            return Ok();
        }

Models:

public class GetRequestModelOut
    {
        public string ClaimHandlingStatus { get; set; }

        public DateTime RequestDate { get; set; }

        public int RequestID { get; set; }

        public int CountryID { get; set; }

        public string RegNumber { get; set; }

        public string Produkt { get; set; }

        public string Workshop { get; set; }

        public string Asignee { get; set; }

        public string claimId { get; set; }

        public string Vinnumber { get; set; }
        
    }

public class GetRequestModel
   {
       public int CountryId { get; set; } = -1;

       public string AssigneId { get; set; } = "";

       [StringLength(50)]
       public string RegNumber { get; set; } = "";

       [StringLength(50)]
       public string Vinnumber { get; set; } = "";

       public DateTime? dateFrom { get; set; } = null;

       public DateTime? dateTo { get; set; } = null;

       // Pagination information
       public int pageSize { get; set; } = 100;

       public int PageNumber { get; set; } = 1;

       public int totalCount { get; set; } = 0;

       public int totalPages { get; set; } = 0;
   }

I have no idea how to solve this without adding 2 parameters to the GetRequestModelOUT and adding totalCount and totalPages there, but then ofcouse every request answer will also get these 2 extra row.
So if anyone have any idea about how I could proceed or how to think about this i would be super grateful.

What I have tried:

I've tried creating new models to seperate totalcount and totalpages but it has not been successful.

Posted 21-Sep-21 22:21pm

Member 15365676

Updated 21-Sep-21 22:47pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2021-09-21T22:47:00

The simplest option would be to send the data as a custom response header. It looks like you've already tried that, but the code is commented out:

C#

var metadata = new
{
    model.totalCount,
    model.totalPages
};

Response.Headers.Add("X-Pagination", JsonConvert.SerializeObject(metadata));