CODEWITHSUNDEEP
c#asp.netasp.net-web-api2
Matt Douhan
Matt Douhan

Reputation: 2113

C# web api returns the wrong amount of data

I have a very simple webapi that returns the wrong amount of data, the API is so simple I simply don't see how this can happen, the controller code is below:

[HttpGet("{TillID}")]
public IEnumerable<object> Get(int tillID, int StartAtRow=0, int TakeNoOfRows=1000)
{
    string hmCompanyId;
    string languageName;
    PrepareParameter(tillID, out hmCompanyId, out languageName);

    var tillData = from tbd in _context.TillBasicData.OrderBy(i => i.ItemId).Skip(StartAtRow).Take(TakeNoOfRows)
                   where tbd.CompanyId == hmCompanyId && tbd.languageCode == languageName
                   select tbd;

    return tillData.ToList();
}

if I use the following URL to call the API I would expect 10 rows of data but I only get 3

https://host.domain.zzz:5443/api/till/tilldata/1?StartAtRow=1&TakeNoOfRows=10

Similar if I use the following URL I would expect 20 rows but I get 5

https://host.domain.zzz:5443/api/till/tilldata/1?StartAtRow=1&TakeNoOfRows=20

Manually querying the DB shows the data in the DB is correct.

Why do I not get the correct amount of rows returned?

EDIT, the following SQL code returns 351443 with the same matching where clause as the actual where clause in the controller

select count(*) from tillbasicdata where CompanyId = 'BE_HM' and languageCode = 'eng'

EDIT, The server log shows the query being executed looks ok, the variables have to be correct because if I increase rows to take to 100 I get more than 10 which should be returned in URL 1

Upvotes: 0

Views: 405

Answers (1)

Igor
Igor

Reputation: 62213

The issue is the order in which you have written your query statement. The way you have it now you are ordering the table, taking the top XXX of the ordered table, and then applying the filter to only those records.

Lets say the there are 1 million records and you want 20 that match the filter. In your existing query you take the first 20 in the unfiltered data table and then apply the filter to those top 20, you will be lucky if there are any matches at all.

What you want is to apply the filter to all the data in the table and then take the top XXX of that filtered result.

var tillData = _context.TillBasicData
                .Where(tbd => tbd.CompanyId == hmCompanyId && tbd.languageCode == languageName)
                .OrderBy(i => i.ItemId)
                .Skip(StartAtRow)
                .Take(TakeNoOfRows)
                .ToList();
return tillData;

Upvotes: 1

Related Questions