Complex Entity Framework Relationships and sorting issue

Question

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.

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

At the core of the Model is a fairly simple Program class.

public abstract class Program
{

    public Program()
    {
        ProgramDegrees = new HashSet<ProgramDegree>();
        ProgramOptions = new HashSet<ProgramOption>();
        Keywords = new HashSet<Keyword>();
        Departments = new HashSet<Department>();
    }

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

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

    //...some virtual navigation properties go here

}

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

        Programs = Programs.OrderBy(p => p.Name).Skip(cardsStartIndex ?? 0).Take(15);

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.

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; } 
}

I map the relationship in my data context using the Fluent API

        modelBuilder.Entity<Program>()
            .HasMany(e => e.ProgramOptions)
            .WithRequired(e => e.Program)
            .WillCascadeOnDelete(false);

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.

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 alongside 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 could just manually sort the final list within the View Model, except that the UI design calls for an infinite scroll effect. So even if the first load was alphabetical, the next 15 or so entities wouldn't be.

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.

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 and 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).

Is there a solution or pattern that I'm overlooking here?

" 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 alongside its program" I am having a hard time visualizing the problem. I guess the question is, if you have a solution already (simply by not lazy-loading), why spend cycles trying to find another solution? You could either eager-load, or simply issue a separate query for the options independently later on, or use ajax, etc. The way the question is phrased, it sounds like a UI issue. — DVK, Jun 13 '16 at 20:05
I mean "lazy-loading" in the UI sense, not in the sense of the data context. I think this is also referred to as "Infinite Scroll". Basically, I'm loading groups of paged results. — jonnybot, Jun 13 '16 at 20:19
I'm thinking what I need is an implementation of the `Join` syntax, similar to http://stackoverflow.com/questions/5010110/entityframework-join-using-join-method-and-lambdas or http://stackoverflow.com/questions/10222255/entity-framework-4-what-is-the-syntax-for-joining-2-tables-then-paging-them — jonnybot, Jun 13 '16 at 20:22

score 0 · Answer 1 · answered Jun 13 '16 at 20:30

Typically if you end up in a situation like this, it's a sign that you're loading too much up-front. Basically your problem is that you're loading the whole data model on initial page load, but you don't really know which ProgramOptions you want to display (or in what order) until later. Instead of solving the complex object model sorting problem, I would break it up into simpler components.

This isn't specifically tagged, but based on your description it sounds like you're in an MVC app. I would add an ajax callback when a Program is selected which loads the ProgramOptions (in sorted order) for that program. On the server side, this should probably be heavily cached or even pre-calculated for each program if necessary.

If you aren't locked in to MVC, this is exactly the kind of problem Angular + WebApi are really good at solving.

score 0 · Answer 2 · answered Jun 13 '16 at 21:05

Finally realized that I was overthinking it.

        var joinedProgramOptions = Programs.AsEnumerable().SelectMany(p =>
        {
            if (p.ProgramOptions.Any())
            {
                return p.ProgramOptions.Select(po => new { Prog = p, ProgOpt = po });
            }
            int[] dummy = new int[] { 0 };
            return dummy.Select(d => {
                return new { Prog = p, ProgOpt = p.ProgramOptions.FirstOrDefault() };
            });
        });

        joinedProgramOptions = joinedProgramOptions
            .OrderBy(jq => jq.ProgOpt?.Name ?? jq.Prog.Name)
            .Skip(cardsStartIndex ?? 0)
            .Take(15);

        List<ProgramViewModel> cards = joinedProgramOptions.Select(hybridObject =>
            new ProgramViewModel()
            {
                Title = hybridObject.ProgOpt?.Name ?? hybridObject.Prog.Name,
                OptionOf = hybridObject.ProgOpt?.Program,
                Degrees = hybridObject.Prog.ProgramDegrees,
                Departments = hybridObject.Prog.Departments
            }
        ).ToList();

Yeah, casting it to an Enumerable with AsEnumerable means the database isn't doing the ordering, but I think I'm okay with that.

Complex Entity Framework Relationships and sorting issue

2 Answers2