Problem with LINQ query: Select first task from each goal

Question

I'm looking for suggestions on how to write a query. For each Goal, I want to select the first Task (sorted by Task.Sequence), in addition to any tasks with ShowAlways == true. (My actual query is more complex, but this query demonstrates the limitations I'm running into.)

I tried something like this:

var tasks = (from a in DbContext.Areas
             from g in a.Goals
             from t in g.Tasks
             let nextTaskId = g.Tasks.OrderBy(tt => tt.Sequence).Select(tt => tt.Id).DefaultIfEmpty(-1).FirstOrDefault()
             where t.ShowAlways || t.Id == nextTaskId
             select new CalendarTask
             {

                 // Member assignment

             }).ToList();

But this query appears to be too complex.

System.InvalidOperationException: 'Processing of the LINQ expression 'OrderBy<Task, int>(
    source: MaterializeCollectionNavigation(Navigation: Goal.Tasks(< Tasks > k__BackingField, DbSet<Task>) Collection ToDependent Task Inverse: Goal, Where<Task>(
        source: NavigationExpansionExpression
            Source: Where<Task>(
                source: DbSet<Task>,
                predicate: (t0) => Property<Nullable<int>>((Unhandled parameter: ti0).Outer.Inner, "Id") == Property<Nullable<int>>(t0, "GoalId"))
            PendingSelector: (t0) => NavigationTreeExpression
                Value: EntityReferenceTask
                Expression: t0
        ,
        predicate: (i) => Property<Nullable<int>>(NavigationTreeExpression
            Value: EntityReferenceGoal
            Expression: (Unhandled parameter: ti0).Outer.Inner, "Id") == Property<Nullable<int>>(i, "GoalId"))), 
    keySelector: (tt) => tt.Sequence)' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.'

The problem is the line let nextTaskId =.... If I comment out that, there is no error. (But I don't get what I'm after.)

I'll readily admit that I don't understand the details of the error message. About the only other way I can think of to approach this is return all the Tasks and then sort and filter them on the client. But my preference is not to retrieve data I don't need.

Can anyone see any other ways to approach this query?

Note: I'm using the very latest version of Visual Studio and .NET.

UPDATE:

I tried a different, but less efficient approach to this query.

var tasks = (DbContext.Areas
      .Where(a => a.UserId == UserManager.GetUserId(User) && !a.OnHold)
      .SelectMany(a => a.Goals)
      .Where(g => !g.OnHold)
      .Select(g => g.Tasks.Where(tt => !tt.OnHold && !tt.Completed).OrderBy(tt => tt.Sequence).FirstOrDefault()))
    .Union(DbContext.Areas
      .Where(a => a.UserId == UserManager.GetUserId(User) && !a.OnHold)
      .SelectMany(a => a.Goals)
      .Where(g => !g.OnHold)
      .Select(g => g.Tasks.Where(tt => !tt.OnHold && !tt.Completed && (tt.DueDate.HasValue || tt.AlwaysShow)).OrderBy(tt => tt.Sequence).FirstOrDefault()))
    .Distinct()
    .Select(t => new CalendarTask
    {
        Id = t.Id,
        Title = t.Title,
        Goal = t.Goal.Title,
        CssClass = t.Goal.Area.CssClass,
        DueDate = t.DueDate,
        Completed = t.Completed
    });

But this also produced an error:

System.InvalidOperationException: 'Processing of the LINQ expression 'Where<Task>(
    source: MaterializeCollectionNavigation(Navigation: Goal.Tasks (<Tasks>k__BackingField, DbSet<Task>) Collection ToDependent Task Inverse: Goal, Where<Task>(
        source: NavigationExpansionExpression
            Source: Where<Task>(
                source: DbSet<Task>, 
                predicate: (t) => Property<Nullable<int>>((Unhandled parameter: ti).Inner, "Id") == Property<Nullable<int>>(t, "GoalId"))
            PendingSelector: (t) => NavigationTreeExpression
                Value: EntityReferenceTask
                Expression: t
        , 
        predicate: (i) => Property<Nullable<int>>(NavigationTreeExpression
            Value: EntityReferenceGoal
            Expression: (Unhandled parameter: ti).Inner, "Id") == Property<Nullable<int>>(i, "GoalId"))), 
    predicate: (tt) => !(tt.OnHold) && !(tt.Completed))' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.'

Answer 1

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.

Answer 2

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();