英文:
EF Linq. How to keep "Where in" sorting
问题
我有一个ID列表，我需要按照列表中的ID的确切顺序从数据库中检索实体。
这样的查询将返回错误的顺序：
List<Guid> listOfIds = targetIds;
var result = await dbContext
.TargetEntities
.Where(x => listOfIds.Contains(x.Id))
.ToListAsync();
我看到一种方法是在循环中发出请求并联合结果，但这看起来太昂贵了。
如何解决这个问题？
英文:
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?
答案1
得分: 3
一旦您将列表存储在内存中，然后尝试这样做：
var sorted = result.OrderBy(x => listOfIds.IndexOf(x.Id)).ToList();
如果有大量的标识，则使用字典是加快处理速度的好选择：
var ordering = listOfIds.Select((g, i) => (g, i)).ToDictionary(x => x.g, x => x.i);
var sorted = result.OrderBy(x => ordering[x.Id]).ToList();
英文:
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();
答案2
得分: 2
数据库不保证任何顺序。如果您不指定 ORDER BY 子句，SELECT 命令可能以任何顺序返回记录。具体而言，不能保证记录按照插入顺序返回。
数据库表不同于 Excel 表格。也就是说，没有自然的行号，因为数据库理论基于集合理论，而集合在定义上是无序的。
您必须指定显式的排序方式
var result = await dbContext
.TargetEntities
.Where(x => listOfIds.Contains(x.Id))
.OrderBy(x => x.MyColumn)
.ThenBy(x => x.SomeOtherColumn)
.ThenBy(...)
.ToListAsync();
还有 OrderByDescending 和 ThenByDescending 扩展方法可用于降序排序。您必须从 OrderBy 或 OrderByDescending 开始，然后可以选择性地添加多个 ThenBy 和 ThenByDescending 调用。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论