Problem with LINQ query: Select first task from each goal

This is a good example for the need of full reproducible example. When trying to reproduce the issue with similar entity models, I was either getting a different error about DefaulIfEmpty(-1) (apparently not supported, don't forget to remove it - the SQL query will work correctly w/o it) or no error when removing it.

Then I noticed a small deeply hidden difference in your error messages compared to mine, which led me to the cause of the problem:

MaterializeCollectionNavigation(Navigation: Goal.Tasks (<Tasks>k__BackingField, DbSet<Task>)

specifically the DbSet<Task> at the end (in my case it was ICollection<Task>). I realized that you used DbSet<T> type for collection navigation property rather than the usual ICollection<T>, IEnumerable<T>, List<T> etc., e.g.

public class Goal
{
    // ...
    public DbSet<Task> Tasks { get; set; }
}

Simply don't do that. DbSet<T> is a special EF Core class, supposed to be used only from DbContext to represent db table, view or raw SQL query result set. And more importantly, DbSets are the only real EF Core query roots, so it's not surprising that such usage confuses the EF Core query translator.

So change it to some of the supported interfaces/classes (for instance, ICollection<Task>) and the original problem will be solved.

Then removing the DefaultIfEmpty(-1) will allow successfully translating the first query in question.

I don't have EF Core up and running, but are you able to split it up like this?

    var allTasks = DbContext.Areas
        .SelectMany(a => a.Goals)
        .SelectMany(a => a.Tasks);

    var always = allTasks.Where(t => t.ShowAlways);

    var next = allTasks
        .OrderBy(tt => tt.Sequence)
        .Take(1);

    var result = always
        .Concat(next)
        .Select(t => new
         {
             // Member assignment
         })
        .ToList();

Edit: Sorry, I'm not great with query syntax, maybe this does what you need?

    var allGoals = DbContext.Areas
        .SelectMany(a => a.Goals);

    var allTasks = DbContext.Areas
        .SelectMany(a => a.Goals)
        .SelectMany(a => a.Tasks);

    var always = allGoals
        .SelectMany(a => a.Tasks)
        .Where(t => t.ShowAlways);

    var nextTasks = allGoals
        .SelectMany(g => g.Tasks.OrderBy(tt => tt.Sequence).Take(1));

    var result = always
        .Concat(nextTasks)
        .Select(t => new
         {
             // Member assignment
         })
        .ToList();