0

I have a list of IDs and I need to retrieve entities from a database in exact same order, that IDs in the list.

Such query will return wrong order:

List<Guid> listOfIds = targetIds;
var result = await dbContext
      .TargetEnities
      .Where(x => listOfIds.Contains(x.Id))
      .ToListAsync();

I see a way to make requests in a loop and union result, but it looks too expensive.

How to solve that?

IngBond
  • 601
  • 1
  • 5
  • 18

2 Answers2

3

Once you have your list in memory, then try this:

var sorted = result.OrderBy(x => listOfIds.IndexOf(x.Id)).ToList();

If there are a large number of ids then using a dictionary is a good option to speed up the process:

var ordering = listOfIds.Select((g, i) => (g, i)).ToDictionary(x => x.g, x => x.i);
var sorted = result.OrderBy(x => ordering[x.Id]).ToList();
Enigmativity
  • 113,464
  • 11
  • 89
  • 172
2

Databases do not guarantee any order. SELECT commands may return the records in any order if you do not specify a ORDER BY clause. Specifically, there is no guarantee that the records are returned in INSERT order.

A database table is not an Excel sheet. I.e., there is no natural line number, because database theory is based on set theory and sets are by definition unordered.

You must specify an explicit order

var result = await dbContext
      .TargetEnities
      .Where(x => listOfIds.Contains(x.Id))
      .OrderBy(x => x.MyColumn)
      .ThenBy(x => x.SomeOtherColumn)
      .ThenBy(...)
      .ToListAsync();

There are also OrderByDescending and ThenByDescending extension methods to sort in descending order. You must begin with a OrderBy or OrderByDescending and can then optionally add multiple ThenBy and ThenByDescending calls.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • Thank you. Unfortunately, I have no field for sorting, Ids in the list can be in any order. – IngBond Jun 27 '23 at 10:47
  • You can add an [indentity column](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property) that is incremented automatically that you sort on. – Olivier Jacot-Descombes Jun 27 '23 at 10:52