1

I have a list of integer IDs lets say MyIdList = {3, 7, 4, 1, 9, 2} which is subset of IDs present in my table. This list is sorted according to my conditions. I have DbContext and Dbset variables to query my postgres database table lets say MyTable. MyTable has lot of columns and rows.

When I issue the below query, i get the correct results(6 rows) but are sorted in some order which is not same as above order list.

My query is 

List<MyTable> myresult = await dbset.Where(p => MyIdList.Contains(p.Id)).ToListAsync();

My requirement is to get myresult list of objects from this table using a linq query in the same order MyIdList exists.

Note: Only data that tells me the order is in the list and not present in this table or any table.

vinmm
  • 277
  • 1
  • 3
  • 14
  • there doesn't appear to be a specific pattern to the order your require, so this leads me to suggest that you will have to manually sort, by defining your own algorithm/function – developer May 06 '20 at 12:23
  • you can use a reference array of ordered ids, to order your results : ref: http://taswar.zeytinsoft.com/linq-tip-ordering-list-using-existing-array/ but this assumes you have a array with the ids in the required order – developer May 06 '20 at 12:27
  • maybe you use a distributed database? – AlleXyS May 06 '20 at 12:27
  • @developer: That ref is what exactly I am looking for. But making it exactly same as in that link results in some error as "lambda expression with statement body cannot be converted to an expression tree". Not sure why the person who gave solution didnt face that problem. – vinmm May 06 '20 at 12:54

1 Answers1

0

This is a duplicate of this question.

You just need to add an OrderBy extension to sort by the index of your MyIdList list. Try this out: 

List<MyTable> myresult = await dbset
    .Where(p => MyIdList.Contains(p.Id))
    .OrderBy(p => MyIdList.IndexOf(p.Id)     
    .ToListAsync();
Nik P
  • 2,693
  • 9
  • 21
  • IndexOf cannot be used as my Ids in list are Guid. For simplicity, i mentioned it as int in my example. Your query reports this problem: LINQ to Entities does not recognize the method 'Int32 IndexOf(System.Guid)' method, and this method cannot be translated into a store expression. – vinmm May 07 '20 at 04:08
  • I used the extension method option in the link you provided as duplicate and it solved the problem. Thanks. – vinmm May 07 '20 at 05:27