如何从ids的自定义顺序中获取表的记录？ [英] How to get records from tables in custom order of ids?

问题描述

 列表< ; INT> IDS; 
  

现在我想根据上述Ids获取记录，但是与上述 Ids。

例如：数据库中的记录如下：

员工：

  Id 
 1 
 2 
 3 
 4 
 5 
  

现在如果Ids数组保持Ids， 4,2,5,3,1 ，那么我只想按此顺序获取记录：

查询：

  var data = context.Employee.Where（t => Ids.Contains（t.Id））ToList（）; 
  

但是上面的查询给我的输出就像在表中：

  Id 
 1 
 2 
 3 
 4 
 5 
  

预期输出

 code> Id 
 4 
 2 
 5 
 3 
 1 
  

更新：我已经尝试过以下解决方案，但由于这是实体框架，它没有奏效：

  var data = context.Employee.Where（t => Ids.Contains（t.Id））
 .OrderBy（d => Ids.IndexOf（d。 Id）的）ToList（）; 
  

对于上述解决方案使其正常工作，我必须添加到列表中：

  var data = context.Employee.Where（t => Ids.Contains（t.Id））ToList（）
 .OrderBy（ d => Ids.IndexOf（d.Id））ToList（）; 
  

但我不想在内存中加载数据，然后过滤掉我的记录。

有人可以帮我实现吗？

解决方案

当您未指定 ORDER BY 是未确定，则必须添加一个 ORDER BY 来指示如何排序。不幸的是，你必须基于内存中的对象/值进行排序，并且不能使用它在SQL查询中进行排序。

因此，最好的是订购一旦数据从数据库中检索出来就可以使用内存。

  var data = context.Employee 
 //添加一个标准，我们只需要知道的ids 
 .Where（t => Ids.Contains（t.Id））
 //之后的任何内容而不是数据库
 .AsEnumerable（）
 //排序结果，in-memory 
 .OrderBy（d => Ids.IndexOf（d.Id））
 //实现到列表
 .ToList（）; 
  

I have some Ids store in below variable:

List<int> Ids;

Now i want to get records based on above Ids but with same order as it is in above Ids.

For eg: Records are like this in database:

Employee:

Id
1
2
3
4
5

Now if Ids array holds Ids like this : 4,2,5,3,1 then i am trying to get records in this order order only:

Query:

  var data = context.Employee.Where(t => Ids.Contains(t.Id)).ToList();

But above query is giving me output like it is in table:

Id
1
2
3
4
5

Expected output :

Id
4
2
5
3
1

Update:I have already tried this below solution but as this is entity framework it didnt work out:

var data = context.Employee.Where(t => Ids.Contains(t.Id))
                    .OrderBy(d => Ids.IndexOf(d.Id)).ToList();

For above solution to make it working i have to add to list :

var data = context.Employee.Where(t => Ids.Contains(t.Id)).ToList()
                    .OrderBy(d => Ids.IndexOf(d.Id)).ToList();

But i dont want to load data in memory and then filter out my record.

Can anybody help me to achieve this??

解决方案

Since the order in which the data is returned when you do not specify an ORDER BY is not determined, you have to add an ORDER BY to indicate how you want it sorted. Unfortunately you have to order based on objects/values in-memory, and cannot use that to order in your SQL query.

Therefore, the best you can do is to order in-memory once the data is retrieved from the database.

var data = context.Employee
    // Add a criteria that we only want the known ids
    .Where(t => Ids.Contains(t.Id))
    // Anything after this is done in-memory instead of by the database
    .AsEnumerable()
    // Sort the results, in-memory
    .OrderBy(d => Ids.IndexOf(d.Id))
    // Materialize into a list
    .ToList();

这篇关于如何从ids的自定义顺序中获取表的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！