How to Sort a Model Based on a Property in IList<T> within that Model

Question

This is a bit more complex than other questions. I have a Model that encapsulates an IList. Within each IList item there are multiple Lists. This makes it VERY difficult to flatten the entire dataset. I need to both filter and/or sort the highest IList items based on a value in that Ilist. So as an example, lets say my structure is as follows:

public class ScorecardModel
{
  //Measure Data
  public IList<ProgramScorecardModel> ProgramScorecardModel { get; set; }
                
}

public class ProgramScorecardModel
{
    public string programId { get; set; }
    public DateTime? reportingDate { get; set; }
    public string programName { get; set; }

    //Measure Data
    public IList<Sales_ViewModel> Sales_ViewModel { get; set; }
    public IList<Employees_ViewModel> Employees_ViewModel { get; set; }
    public IList<Locations_ViewModel> Locations_ViewModel { get; set; }

}

My scorecard returns all of the programs and their respective data. What I want to do is sort/filter the ProgramScorecard models based on the programId.

Here is how I call the queries that populate the scorecard.

ScorecardModel scorecard = new ScorecardModel();
scorecard = _adoSqlService.GetScorecard();

I want to call the following Sort function:

private ScorecardModel sortScorecard(ScorecardModel scorecardList, string sortField, string sortDirection)
{
    IQueryable<ScorecardModel> sortedScorecard = scorecardList.AsQueryable();
    sortedScorecard = sortedScorecard.OrderBy(sortField + " " + sortDirection);
    return sortedScorecard;
}

The problem is that the scorecardlist is not enumerable. If I change the functions to use this then the scorecard loading (GetScorecard) throws errors.

IEnumerable<ScorecardModel> scorecard = null;
scorecard = _adoSqlService.GetScorecard();

private IEnumerable<ScorecardModel> sortScorecard(IEnumerable<ScorecardModel> scorecardList, string sortField, string sortDirection)
{
  IQueryable<ScorecardModel> sortedScorecard = scorecardList.AsQueryable();
  sortedScorecard = sortedScorecard.OrderBy(sortField + " " + sortDirection);
  return sortedScorecard;
} 

I've tried changing the GetScorecard routine, but I can't get the following to work:

public IList<ScorecardModel> GetScorecard()
{
   string programid;
   string reportingdate = "2/1/2022";
   IList<ProgramViewModel> proglist = new List<ProgramViewModel>();
   proglist = GetPrograms();
   IList <ScorecardModel> scorecardData = new List<ScorecardModel>();
   scorecardData.ProgramScorecardModel = new List<ProgramScorecardModel>(); <---This Line Here, ProgramScorecardModel is not able to be a list
   int i = 0;
   foreach (var p in proglist)
   {
       ProgramScorecardModel programData = new ProgramScorecardModel();
       programData.programId = p.programId;
       programData.programName = p.programName;
       programid = p.programId;
       programData.Sales_ViewModel = new List<Sales_ViewModel>(GetSalesData(programid, reportingdate));
       programData.Employees_ViewModel = new List<Employees_ViewModel>(GetEmployeesData(programid, reportingdate));
       programData.Locations_ViewModel = new List<Locations_ViewModel>(GetLocationsData(programid, reportingdate));
       scorecardData.ProgramScorecardModel.Add(programData);
       i++;
    }
    return scorecardData;
}

How can I make the entire scorecardData and scorecardData.ProgramScorecardModel an enemrable object?

Assuming I get this working as an enumerable object, will I be able to sort AND Filter on scorecardData.ProgramScorecardModel[x].programName values?

Additional Info This is how I currently populate the scorecard model

public ScorecardModel GetScorecard()
{
    string programid;
    string reportingdate = "2/1/2022";
    IList<ProgramViewModel> proglist = new List<ProgramViewModel>();
    proglist = GetPrograms();
    ScorecardModel scorecardData = new ScorecardModel();
    scorecardData.ProgramScorecardModel = new List<ProgramScorecardModel>();
    int i = 0;
    foreach (var p in proglist)
    {
        ProgramScorecardModel programData = new ProgramScorecardModel();
        programData.programId = p.programId;
        programData.programName = p.programName;
        programid = p.programId;
        programData.Locations_ViewModel = new List<Locations_ViewModel>(GetLocationData(programid, reportingdate));
        programData.Employees_ViewModel = new List<Employees_ViewModel>(GetEmployeeData(programid, reportingdate));
        programData.Sales_ViewModel = new List<Sales_ViewModel>(GetSalesData(programid, reportingdate));

        scorecardData.ProgramScorecardModel.Add(programData);   
        i++; //This is used in some other code I have removed for simplification
    }
    return scorecardData;
}

The end result is this:
ScorecardModel
   ProgramScorecardModel[i]
       Programid
       Locations_ViewModel
       Employees_ViewModel
       Locations_ViewModel
   ProgramScorecardModel[i]
       Programid
       Locations_ViewModel
       Employees_ViewModel
       Locations_ViewModel
   ProgramScorecardModel[i]
       Programid
       Locations_ViewModel
       Employees_ViewModel
       Locations_ViewModel

Here is an oversimplification of how I'm using the scorecard model:

for (int i = 0; i < @Model.ProgramScorecardModel.Count; i++)
{
    <tr>
        <td rowspan="2" style="text-align: left; border: 1px solid black; padding-left: 5px">@Model.ProgramScorecardModel[i].programName</td>


        @{string results = Model.ProgramScorecardModel[i].Sales_ViewModel[0].rating;

            <td colspan="3" class="tdtooltip @(results == "R" ? "measure_red" : results == "Y" ? "measure_yellow" : results == "G" ? "measure_green" : results == "NR" ? "measure_nr" : results == "NS" ? "measure_ns" : "measure_na")">
                @Model.ProgramScorecardModel[i].Sales_ViewModel[0].rating
                @if (@Model.ProgramScorecardModel[i].Sales_ViewModel[0].comments.Length > 0)
                {
                    <span class="tooltiptext">@Html.Raw(Model.ProgramScorecardModel[i].Sales_ViewModel[0].comments)</span>
                }
            </td>
        }

        @{results = Model.ProgramScorecardModel[i].Employees_ViewModel[0].rating;

            <td colspan="3" class="tdtooltip @(results == "R" ? "measure_red" : results == "Y" ? "measure_yellow" : results == "G" ? "measure_green" : results == "NR" ? "measure_nr" : results == "NS" ? "measure_ns" : "measure_na")">
                @Model.ProgramScorecardModel[i].Employees_ViewModel[0].rating
                @if (@Model.ProgramScorecardModel[i].Employees_ViewModel[0].comments.Length > 0)
                {
                    <span class="tooltiptext">@Html.Raw(Model.ProgramScorecardModel[i].Employees_ViewModel[0].comments)</span>
                }
            </td>
        }

        @{ results = Model.ProgramScorecardModel[i].Locations_ViewModel[0].rating;

            <td colspan="3" class="tdtooltip @(results == "R" ? "measure_red" : results == "Y" ? "measure_yellow" : results == "G" ? "measure_green" : results == "NR" ? "measure_nr" : results == "NS" ? "measure_ns" : "measure_na")">
                @Model.ProgramScorecardModel[i].Locations_ViewModel[0].rating
                @if (@Model.ProgramScorecardModel[i].Locations_ViewModel[0].comments.Length > 0)
                {
                    <span class="tooltiptext">@Html.Raw(Model.ProgramScorecardModel[i].Locations_ViewModel[0].comments)</span>
                }
            </td>
        }
       
    </tr>
}

Answer 1

While writing the answer there are a couple things I did not understand. Maybe we can figure it out together. For now I will post what is a starting point to get things working.

using System.Linq;
using System.Collections.Generic;
                    
public class Program
{
    public static void Main()
    {
        var scorecard = GetScorecard();
        var sortedScorecard = scorecard.WithSortedPrograms("programId", "ascending");
    }
    
    public static ScorecardModel GetScorecard()
    {
        var proglist = new List<ProgramViewModel>();
        // proglist = GetPrograms();
        
        var scorecardData = new List<ProgramScorecardModel>();
        foreach (var p in proglist)
        {
          var programData = new ProgramScorecardModel();
          programData.ProgramId = p.ProgramId;
          scorecardData.Add(programData);
        }
        
        var scorecard = new ScorecardModel();
        scorecard.ProgramScorecardModel = scorecardData;
        return scorecard;
    }
}

public class ScorecardModel
{
  public List<ProgramScorecardModel> ProgramScorecardModel { get; set; }
    
  public ScorecardModel WithSortedPrograms(string sortField, string sortDirection)
  {
      var sortedModel = new ScorecardModel();
      // sortedModel.ProgramScorecardModel = ProgramScorecardModel.AsQueryable().OrderBy(sortField + " " + sortDirection);
      sortedModel.ProgramScorecardModel = ProgramScorecardModel.OrderBy(x => x.ProgramId).ToList();
      return sortedModel;
  }
}

public class ProgramScorecardModel
{
    public string ProgramId { get; set; }
}

public class ProgramViewModel
{
    public string ProgramId { get; set; }
}

I was not able to find a OrderBy method for IQueryable<T> that accepts a string. That is why for now I wrote the x => x.ProgramId expression.

There are many improvements I would personally make to your code though as I find it a bit messy. Once we find the correct solution to your problem, if you want, we can look at a different ways to approach this.