C# web api returns the wrong amount of data

Question

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

shown us the query you execute at the db. It probably does not match the query as shown in the code. Did you take a look at the query generated by the code? — Peter Bons
– Peter Bons, Commented Jun 2, 2017 at 18:42
What I mean is that there is 300 000 rows in the DB so the data is there, the problem is that it always returns less than my TakeNoOfRows variable, the where clause doesn't change it just consistently give me about 1/3 to 1/4 of the number in the variable — Matt Douhan
– Matt Douhan, Commented Jun 2, 2017 at 18:45
You should not "expect 10 rows of data" but rather you would expect to get a max of 10 rows of data, if there are enough matches in the where clause. — nurdyguy
– nurdyguy, Commented Jun 2, 2017 at 18:45
@nurdyguy there are 300 000 plus matching rows of data with that where clause, if I just increase the variable to take 100 rows I get 30 which is more than 10 from the first query, the where clause don't change just cause I change the number of rows taken and that should prove there is 10 rows to return in URL invocation 1 now? — Matt Douhan
– Matt Douhan, Commented Jun 2, 2017 at 18:49
Well, my statement is still true but that is an interesting situation. Set the debugger and look at the query generated, as Peter suggested above. Post that query. — nurdyguy
– nurdyguy, Commented Jun 2, 2017 at 18:55

Igor · Accepted Answer · 2017-06-02 19:29:08Z

1

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;

edited Jun 2, 2017 at 19:29

answered Jun 2, 2017 at 19:21

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Douhan Over a year ago

Will this also work if I download in batches? first invocation start at 0 take 10 rows, then start at 10 take 10, start at 20 take 10?

Igor Over a year ago

@MattDouhan - Yes but you must filter first, then sort, then skip, and then take in that order.

Matt Douhan Over a year ago

works like a charm, once you explained it it all makes sense, the table has 4 languages for every item, so thats why I would get roughly 25% of the expected rows every time because I was working on the unfiltered list and then filtering which would remove the languages not passing the filter, I was further confused because I compared this to a count api which of course doesn't care about the order at all it just counts the rows passing the filter

Collectives™ on Stack Overflow

C# web api returns the wrong amount of data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related