[Solved] Sort a Linq query

Question

Start by breaking up your logic into separate operations rather than trying to write everything in one line. Compilers are smart cookies so they optimize things quite well, there’s no point making your code harder to read/understand:

var facultySpecialties = specialty.faculty_specialties
  .Where(fs => fs.faculty.active)
  .ToList(); // Executes the query to retrieve faculty specialties.

foreach(var facultySpecialty in facultySpecialties)
{
  // do stuff.
}

Keep mindful of the scope of the entities you are referring to with your queries. The above query will be returning faculty_specialties, not faculties. A gotcha to beware of: since this returns fac_specs, if you want to access details from the fac_spec and the faculty in the foreach loop then .Include the faculty as well.

var facultySpecialties = specialty.faculty_specialties
  .Include(fs => fs.faculty)
  .Where(fs => fs.faculty.active)
  .ToList();

If you just want Faculties, and don’t need info from the fac_spec:

var faculties = specialty.faculty_specialties
  .Select(fs => fs.faculty)
  .Where(f => f.active)
  .ToList();

This selects the faculty from our fac_specs. Note the Where clause changes because the scope after the .Select() becomes Faculties instead of Faculty_Specialities. If you still want details from the fac_specs, you can use a .Include(f => f.faculty_specialties) however don’t include relationships if they aren’t needed as it increases the performance cost and transfer cost (size) of the queries.

Now you want the faculties ordered. The question is what entity does priority apply? If Priority is a field in Faculty then:

var faculties = specialty.faculty_specialties
  .Select(fs => fs.faculty)
  .Where(f => f.active)
  .OrderBy(f => f.Priority)
  .ToList();

However, I suspect the priority is on the faculty_speciality.

If you have navigation properties from faculty back to faculty_specialities (I’m assuming a many-to-many relationship so faculty could have a ICollection<faculty_specialities> mapped) and faculties are a DbSet in your context, you might want to restructure the query since this will make ordering a bit easier:

var faculties = dbContext.Faculties
  .Where(f => f.active && f.faculty_specialties.Any(fs => fs.specialty.specialtyId == specialty.specialtyId))
  .ToList();

To implement an order by on the faculty_speciality.priority:

var faculties = dbContext.Faculties
  .Where(f => f.active 
    && f.faculty_specialties.Any(fs => fs.specialty.specialtyId == specialty.specialtyId))
  .OrderByDescending(f => f.faculty_specialties.Min(fs => fs.priority))
  .ToList();

You can try an order-by on the original query with something like:

var faculties = specialty.faculty_specialties
  .OrderBy(fs => fs.priority)
  .Select(fs => fs.faculty)
  .Where(f => f.active)
  .ToList();

However I’m not sure this sorting would honored down to the final result set.

One last thing to consider with queries like this is that as systems grow the amount of data may make operations like this very expensive as the # of facilities for a given specialty increase. You should consider imposing a limit to the result size and paging support early on using .Take() and .Skip() unless the system’s size will be capped at a reasonable number.

Accepted Answer