Complex Entity Framework Relationships and sorting issue
<p>So, I'm building a web app to display information about my campus's academic offerings. The site is supposed to lazy-load a list of programs based on some user-selected criteria.</p>

<p>The issue I'm running into is how to properly sort the data in conjunction with a complex set of associations.</p>

<p>At the core of the Model is a fairly simple Program class.</p>

<pre><code>public abstract class Program
{

    public Program()
    {
        ProgramDegrees = new HashSet&lt;ProgramDegree&gt;();
        ProgramOptions = new HashSet&lt;ProgramOption&gt;();
        Keywords = new HashSet&lt;Keyword&gt;();
        Departments = new HashSet&lt;Department&gt;();
    }

    [Key]
    public int Id { get; set; }

    [Required]
    [StringLength(100)]
    public string Name { get; set; }

    //...some virtual navigation properties go here
</code></pre>

<p>}</p>

<p>In my Controller, I query for the programs based on some user inputs, then order and limit them.</p>

<pre><code>        Programs = Programs.OrderBy(p =&gt; p.Name).Skip(cardsStartIndex ?? 0).Take(15);
</code></pre>

<p>What's tripping me up is the ProgramOptions and how to order them alongside the programs in alphabetical order. Options are basically a sub-field or emphasis within an academic program, such as an Illustration option in the Design program.</p>

<pre><code>public partial class ProgramOption : AbstractProgram
{
    [Key]
    public int Id { get; set; }

    [Required]
    [StringLength(100)]
    public string Name { get; set; }

    public virtual Program Program { get; set; } 
}
</code></pre>

<p>I map the relationship in my data context using the Fluent API</p>

<pre><code>        modelBuilder.Entity&lt;Program&gt;()
            .HasMany(e =&gt; e.ProgramOptions)
            .WithRequired(e =&gt; e.Program)
            .WillCascadeOnDelete(false);
</code></pre>

<p>In the UI, we're listing ProgramOptions right alongside programs themselves. That's all well and good. Once I have the list of programs that matches any search criteria, I just add the ProgramOptions associated with each program to the View Model.</p>

<p>Just one teeny problem. Even though I sort the Programs found by their Name property so they display in alphabetical order, adding the ProgramOptions in after the fact means that a given option always displays <em>alongside</em> its program, even if the Option name should be alphabetically elsewhere in the list. This makes the list less useful if you're scanning for a particular heading. I <em>could</em> just manually sort the final list within the View Model, <em>except</em> that the UI design calls for an <a href="https://en.support.wordpress.com/infinite-scroll/" rel="nofollow">infinite scroll</a> effect. So even if the first load was alphabetical, the next 15 or so entities wouldn't be.</p>

<p>My first thought was to use an inheritance pattern (make ProgramOptions a subclass of Programs, or of some third AbstractPrograms class), but that's proving to be far more complicated than its worth given all the other associations.</p>

<p>The only other solution I can think of is to select all the programs from the database, construct the relevant ViewModel for all of them <em>and</em> their options, and then take the number I want to include on the next load. I dislike this because it more or less defeats half the purpose of lazy loading (fetching less data from the DB at one time, and delivering it to the client piece-by-piece).</p>

<p>Is there a solution or pattern that I'm overlooking here?</p>