Well, not everywhere. Only in my project. When I was querying RavenDB. Here how I solved it.

The catalog Model

Our catalog, as many other product catalogs in the world is made up of products and categories. A product must belong to at least a given standard category and can belong to zero or more non-standard categories. Although the most usual case is a product “living inside” one standard and one non-standard categories.

Each category object contains its globally unique identifier and a value that indicates the relative order of the product inside that category.

Querying the Catalog

When displaying the products that belong to a given category (standard or otherwise) the products collection is queried, filtering by the desired category id and sorting by the relative order of the product inside that category.

Raven-izing the query

RavenDB goes as far as requiring an index for each query you do to your data (or an ad-hoc index will be created the first time the query is performed). In that index, we will include the fields that are part of the filter (the category ids) as well as the fields that are used for sorting.
But… we do not have an specialized sorting field for each category in our products. No we don’t. But we can ask Raven to create one while indexing:

New entries in the index will contain a dynamic field that is named after the category and valued after the relative order. With those fields in the index we can query our catalog with the following query:

It seemed to work. The feature was demoed several times and approved by several people: change the order of a category –> products are displayed in that order. End of story? Hardly.
It did not pass the test that every feature has to pass in order to be considered a success: the reality test.

Real-World problems

We started t get bug reports in production:

— customer: “it does not work. Products are not in the order specified in the category”.
— developer: Head scratching. ”documents have the right information”. More scratching. “it works in staging, you saw it with your own eyes, look”
— “not in production, fix it”
— “what is what you are doing exactly?”
— “I change category A, then category B, then…”
— Vicious scratching. “let me look into it… once more”

And indeed, as usual, customer was right. When changing more than one category at a time, the order of products was less than predictable.

Why? Oh why?

After asking around, a colleague pointed me the solution: Do not use .OrderBy() extension. Instead, use .AddOrder() which allows the type of the field that we are using.

And the customer has not complained since.