How to OrderBy() as per the order requested in distinct string list

Question

How to use OrderBy for shaping output in the same order as per the requested distinct list

    public DataCollectionList GetLatestDataCollection(List<string> requestedDataPointList)
    {
        var dataPoints = _context.DataPoints.Where(c => requestedDataPointList.Contains(c.dataPointName))
                                 .OrderBy(----------)  //TODO: RE-ORDER IN THE SAME ORDER AS REQUESTED requestedDataPointList
                                 .ToList();        
        
         
        dataPoints.ForEach(dp => 
        {
           ....
        });

    }

Loop over the requestDataPointList and add each DataPoint to a new List — Steve, Oct 22 '20 at 11:36
You query is backwards. It should be : requestedDataPointList.SelectMany(x => _context.DataPoints.Where(c => x.Contains(c.dataPointName))) — jdweng, Oct 22 '20 at 11:37
If i loop it will be 5000 calls to DB i don't wish to loop it — Rahul Uttarkar, Oct 22 '20 at 11:38
then `requestedDataPointList.SelectMany(x=>dataPoints.Where(c => x == c.dataPointName))` and remove OrderBy — Selvin, Oct 22 '20 at 11:40
Then add a intermediate query : var temp = requestedDataPointList.Select((x,i) => new { name = x, index = i}); var dataPoints = _context.DataPoints.SelectMany(c => temp.Where(x => x.name.Contains(c.dataPointName)) .Select(x => new { point = c, index = x.index })) .OrderBy(x => x.index); — jdweng, Oct 22 '20 at 13:11
@Selvin I don't see how that helps - that still generates one query per member of `requestedDataPointList`. — NetMage, Oct 22 '20 at 19:23
@NetMage no, obviousy it will not generate any query ... as it use `dataPoints` which is already "materialized" with `ToList()` — Selvin, Oct 22 '20 at 19:27

score 1 · Answer 1 · answered Oct 22 '20 at 19:21

Do the sorting on the client side:

public DataCollectionList GetLatestDataCollection(List<string> requestedDataPointList)
{
    var dataPoints = _context.DataPoints.Where(c => requestedDataPointList.Contains(c.dataPointName))
                             .AsEnumerable()
                             .OrderBy(requestedDataPointList.IndexOf(c.dataPointName));        
    
    foreach (var dp in dataPoints)
    {
       ....
    });

}

NOTE: Also, I don't think ToList().ForEach() is ever better than foreach ().

score 1 · Answer 2 · answered Oct 24 '20 at 20:29

It think the fastest method is to join the result back with the request list. This makes use of the fact that LINQ's join preserves the sort order of the first list:

var dataPoints = _context.DataPoints
    .Where(c => requestedDataPointList.Contains(c.dataPointName))
    .ToList();

var ordered = from n in requestedDataPointList
              join dp in dataPoints on n equals dp.dataPointName
              select dp;

foreach (var dataPoint in ordered)
{
    ...
}

This doesn't involve any ordering, joining does it all, which will be close to O(n).

Another fast method consists of creating a dictionary of sequence numbers:

var indexes = requestedDataPointList
    .Select((n, i) => new { n, i }).ToDictionary(x => x.n, x => x.i);
var ordered = dataPoints.OrderBy(dp => indexes[dp.dataPointName]);

How to OrderBy() as per the order requested in distinct string list

2 Answers2