Pagination issue: returning total count and total pages in body, without adding to other data that i-CodePudding

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 are the 2 methods I use (one for search and one for count)

public async Task<IEnumerable<GetRequestModelOut>> GetRequest(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);

    int number = await getCount1(model);

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

    var getReq1 = await _sqlconnection.QueryAsync<GetRequestModelOut>($@"
    SELECT {model.totalCount} as totalCount, {model.totalPages} as totalPages,
    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;
}

Here's the count:

public async Task<int> getCount1(GetRequestModel model)
{   // Count method for Pagination in GetRequest
    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);
    var getCount = await _sqlconnection.QueryAsync<int>($@"
    SELECT TOP 2000(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);
    return getCount.Count();
}

As you can see in the first method, I am currently sending the total count and pages to the model meaning my current response looks like this:

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

And I think this is unnecessary since it's enough to get the total count and pages once. Here's how the models look btw;

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;
}

And the model for getting data out:

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 int totalCount { get; set; } = 0;
    public int totalPages { get; set; } = 0;    
}

And the controller:

[HttpGet]
public async Task<IActionResult> GetRequest([FromQuery] GetRequestModel model, int pageNumber) 
{
    if (!ModelState.IsValid)
    {
        return BadRequest(ModelState);
    }
    try
    {
        var list = await _request.GetRequest(model, pageNumber);
        return Ok(list);
    }
    catch (Exception e)
    {
        return BadRequest(e.Message);
    }
            
    return Ok();
}

Now I've tried doing something simlar to:

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

In the controller, but this still leaves the question as to how I can return these 2 values to the response body? Thankful for any input since I'm feeling a bit stuck at the moment.

CodePudding user response：

your expected output is off consider this:

{
    "totalcount" : "300",
    "totalpages" : "3",
    "items" : [
        {
            "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": ""
        }
    ]
}

the total count and total pages should not be part of the items. this way you're sending a jobject instead of an jarray though.

CodePudding user response：

I think, the better solution would be to actually wrap all those answers summary details in separate object, that holds them all together. Like:

public class SummaryModel
{
    public int totalCount { get; set; } = 0;
    public int totalPages { get; set; } = 0;
}

public class GetRequestModelOut
{
    // Things from your normal out-model
    // WITHOUT totalCount and totalPages
}

public class ResultModel
{
    public SummaryModel Summary { get; set; }
    public IEnumerable<GetRequestModelOut> Models { get; set; }
}

And when you will be invoking/returning the result, you will just get the object together like:

public async Task<ResultModel> GetRequest(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);
    //add all other params

    int totalCount = await getCount1(model);
    int totalPages = (int)Math.Ceiling(totalCount / (double)model.pageSize);
    var summary = new Summary()
    {
        totalCount = totalCount,
        totalPages = totalPages
    };

    var models = await _sqlconnection.QueryAsync<GetRequestModelOut>(/*query*/);
    
    return new ResultModel()
    {
        Summary = summary,
        Models = models
    };
}

Resulting JSON would look like:

{
    "Summary": {
        "totalCount": 300,
        "totalPages": 3
    },
    "Models": [
    {
        "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": ""
    }]
}