Web Api IEnumerable order

Question

Short version:

Sorted lists lose their correct ordering when retrieving data via GET.

Long version:

The function in question consists of 3 steps:

1. Load data with dapper
2. Map data to a different list
3. Return Ok(models)

1) Dapper loading

Here we load some data using a dapper query. The query is basic and takes the following form:

SELECT * FROM tableA A 
JOIN tableB B on A.Id = B.Id 
ORDER BY A.Date DESC 
OFFSET @X ROWS
FETCH NEXT @Y ROWS ONLY

Using some units tests we can see that the data we receive from transaction.Query<ModelA>(theQuery).ToList() is sorted correctly and what we expect.

2) Map to different models

This step is basically return modelAs.Select(x => new ModelB(x.PropA, x.PropB)).ToList()

3) Web Api

Finally we simply respond to the request with this.Ok(modelBs) in our controller.

The controller action looks roughly like this:

[HttpGet]
[Route("the/resource")]
public IHttpActionResult LoadListOfResources(int pageNumber, int numberOfItems)
{
    var items = this.service.LoadMappedModels(pageNumber, numberOfItems);

    return this.Ok(items);
}

Our problem is that the data is no longer ordered by date DESC by the time it reaches the client. This only happens in our production and QA environments. Locally this problem does not occur on at least 3 different machines (could it be network related?).

With unit and integration tests we have verified that the data order is correct up to the point we call Ok(models). Can it be a serialization issue?

After this point we have verified with an Android, iOS and Postman client that the serialized data does not represent the data in the correct order.

We understand that the IEnumerable<> interface does not make any guarantees about ordering, however, we assume that as long as the underlying implementation (List<> in this case) does guarantee order, everything should be okay.

Any help or theories for how this is happening would be appreciated. Even though we think we've ruled out some steps (like Dapper loader) with unit tests, we are open to any ideas.

Answer 1

The network doesn't reorder your response, so you can rule out network related issues.
The serialization doesn't reorder the items in the list, because - as you already noted - the object being serialized guarantees a stable order of the items. So, you can rule out the serialization.

This leaves you with only one option: The data is not coming from the database in the order that you expect.
This could be due to culture differences between the different environments, either in the database or your application.
The easiest way to verify that this indeed is the cause of the incorrect ordering is to simply log the result that you get from the database, before processing it any further. Log it to a file and compare the results between environments.