How to improve performance of ordering list multiple times after getting data from database

I think the cause of your problems is because you get subsets or your sequence and order this subset. You do this several times, and you decide to make Lists of all intermediate results.

Let's first see how you want to order your students.

So you have a schoolId and a sequence of Students. Every Student has properties SchoolId, Message, and Status

You take all Students from the school with schoolId, and for some reason you decide to call these students studentMessages.

Then you want to order these Students (studentmessages) in the following order:

• First all Students with null message and Status unequal to notActive, ordered by descending UserId
• Then all Students with non-null message and Status unequal to notActive, ordered by Message.NextFollowUpdate
• Then all Students with non-null message and status equal to notActive, ordered by Message.NextFollowUpdate
• Finally all Students with null message and status equal to notActive, ordered by descending User.Id (sure you didn't mean UserId? I think that would be the same)

In a table:

group | Message |  Status      | Order by
  1   | == null | != notActive | descending UserId
  2   | != null | != notActive | ascending  message.NextFollowUpdate
  3   | != null | == notActive | descending message.NextFollowUpdate
  4   | == null | == notActive | ascending  UserId

One of the methods would be to let your database management system do this (AsQueryable). The ordering algorithm seems fairly complicated. I'm not sure whether the DBMS can do this more efficient than your process.

Another method would be to fetch only the Students you actually need and let your process do the ordering (AsEnumerable). Provide a class that implements IComparer<Student> to decide about the order.

int schoolId = ...
IComparer<Student> mySpecialStudentComparer = ...
var orderedStudents = dbContext.Students
    .Where(student => student.SchoolId == schoolId)
    .AsEnumerable()         // move the selected data to local process

    // now that the data is local, we can use our local Student Comparer
    .OrderBy(mySpecialStudentComparer);

If your Student has a lot of properties that you won't use after you fetched the data, consider creating a local class that contains only the properties you need and limit the selected data to this local class, for example FetchedStudent

    .Select(student => new FetchedStudent
    {
        // select only the properties you actually plan to use,

        // for the sorting we need at least the following:
        Message = student.Message,
        Status = student.Status
        UserId = student.UserId,

        // Select the other Student properties you plan to use, for example:
        Id = student.Id,
        Name = student.Name, 
        ...
    }

Of course in that case, your comparer needs to implement IComparer<FetchedStudent>.

So let's create a StudentComparer that will sort the Students according to your requirements!

class StudentComparer : IComparer<FetchedStudent>
{
    private readonly IComparer<int> UserIdComparer = Comparer<int>.Default;
    private readonly IComparer<DateTime> nextFollowUpdateComparer =
                     Comparer<DateTime>.Default;

    public int CompareTo(FetchedStudent x, FetchedStudent y)
    {
        // TODO: decide what to do with null students: exception?
        // or return as smallest or largest

        // Case 1: check if x is in sorting group 1
        if (x.Message == null && x.Status == notActive)
        {
            // x is in sorting group 1
            if (y.Message == null && y.Status == notActive)
            {
                // x and y are in sorting group 1.
                // order by descending UserId
                return -UserIdComparer.CompareTo(x.UserId, y.UserId);
                // the minus sign is because of the descending
            }
            else
            {   // x is in group 1, y in group 2 / 3 / 4: x comes first
                return -1;
            }
        }

        // case 2: check if X is in sorting group 2
        else if (x.Message != null && x.Status != notActive)
        {   // x is in sorting group 2
            if (y.Message == null && y.Status != notActive)
            {   // x is in group 2; y is in group 1: x is larger than y
                return +1;
            }
            else if (y.Message == null && y.Status != notActive)
            {   // x and y both in group 2: order by descending nextFollowUpDate
                // minus sign is because descending
                return -nextFollowUpdateComparer.CompareTo(
                       x.Message.NextFollowUpdate,
                       y.Message.NextFollowUpdate);
            }
            else
            {   // x in group 2, y in 3 or 4: x comes first
                return -1;
            }
        }

        // case 3: check if X in sorting group 3
        else if (x.Message == null && x.Status != notActive)
        {
           ... etc, you'll know the drill by know
    }
}    

Possible improvement

You see that the comparer constantly compares whether x.Message equal null and whether x.Status equals notActive, to detect to which sorting group x and y belong.

Consider creating a function that only once calculates to which sorting group a Student belongs and remember the sorting group:

.Select(student => new FetchedStudent
{
    SortingGroup = student.ToSortingGroup(),

    ... // other properties you need
}

public int CompareTo(FetchedStudent x, FetchedStudent y)
{
    switch (x.SortingGroup)
    {
        case 1:
           switch y.SortingGroup:
           {
               case 1: // x and y both in sorting group 1
                  return -UserIdComparer.CompareTo(x.UserId, y.UserId);

               default: // x in sorting group 1, y in 2 / 3 / 4: x smaller
                  return -1;
           }
        case 2:
           switch y.SortingGroup:
           {
               case 1: // x in sorting group 2; y in sorting group 1: x larger
                  return +1;
               case 2: // x and y both in sorting group 2
                  return -nextFollowUpdateComparer.CompareTo(
                       x.Message.NextFollowUpdate,
                       y.Message.NextFollowUpdate);
           }    

etc. This way the comparison with Message and Status is only done once