Question: Select item from one list based on item in another list in the same order

Question

Select item from one list based on item in another list in the same order

Answers 1
Added at 2017-11-07 22:11
Tags
Question

exhibits contain ids that are in a certain order. When I query another table to get the BMI ids based on the exhibit ids, the order is not the same. Instead of pulling the first document id in exhibit, I think it is pulling the first record in the database that has the same exhibit id, but I want it to pull the record in the database in the same order as the exhibits ids.

var exhibits = _context.ApExhibits.Where(x => x.CASE_ID == apDockets.CASE_ID)
    .Where(x => x.EXHIBIT_NBR != null)
    .Where(x => !documents617.Contains(x.DOC_ID))
    .OrderBy(x => x.EXHIBIT_NBR)
    .Select(x => x.DIM_ID).ToList();

if (exhibits.Count > 0)
{
    var bmiIds =
        _context.DocumentImages.Where(x => exhibits.Contains((int)x.DIM_ID))
            .Select(x => (int)x.BMI_ID).ToList();
}
Answers to

Select item from one list based on item in another list in the same order

nr: #1 dodano: 2017-11-07 22:11

it seems like your first collection exhibits is ordered based on EXHIBIT_NBR whereas when you query the _context.DocumentImages you're not ordering it by the same property, hence you're going to receive results based on the order of the elements in the source sequence which in this case is _context.DocumentImages. Essentially, you're saying "given an element of the source sequence DocumentImages, search linearly within the exhibits collection and if there is an element which meets the given criteria then retain the element of the source sequence".

So let's say the first element from the source sequence DocumentImages to be passed into the Where clause has an equivalent id of an element from the collection exhibits but the element in exhibits is say at the 5th position, this will make the element of the source sequence the first element of the result list when we perform a ToList() eager operation on the methods whereas it should technically be at the 5th position of the result list given that the matching element from exhibits is also at the 5th position.

So in order to have elements that are in the same order as exhibits, one solution is to inner join the DocumentImages and exhibits collections which would be the equivalent of checking if one element in one collection is contained within another. Then we can order by the same property as you did with exhibits.

example with query syntax:

var bmiIds = (from e in _context.ApExhibits
              join x in _context.DocumentImages on e.DIM_ID equals (int)x.DIM_ID
              where exhibits.Contains((int)x.DIM_ID)
              orderby e.EXHIBIT_NBR
              select (int)x.BMI_ID).ToList();

example with fluent syntax:

var bmiIds = _context.ApExhibits
                     .Join(_context.DocumentImages,
                          e => e.DIM_ID,
                          x => (int)x.DIM_ID,
                         (e, x) => new { e.EXHIBIT_NBR, x.BMI_ID })
                     .Where(x => exhibits.Contains((int)x.DIM_ID))
                     .OrderBy(e => e.EXHIBIT_NBR)
                     .Select(x => (int)x.BMI_ID).ToList();
Source Show
◀ Wstecz