c# - LINQ查询问题：从每个目标中选择第一个任务(Problem with LINQ query: Select first task from each goal)

Question

The bounty expires in 5 days) . 赏金将 在5天后过期) 。) Answers to this question are eligible for a +150 reputation bounty. 该问题的答案有资格获得+150声望奖励。) Jonathan Wood is looking for an answer from a reputable source) . 乔纳森·伍德 （ Jonathan Wood）正在寻找信誉良好的消息来源) 。)

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 .

(对于每个Goal ，除了任何具有ShowAlways == true任务，我想选择第一个Task （按Task.Sequence排序）。)

(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 =... .

(问题是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 Task s and then sort and filter them on the client.

(关于该方法，我想到的唯一另一种方法是返回所有Task ，然后在客户端上对它们进行排序和过滤。)

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.

(注意：我正在使用最新版本的Visual Studio和.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.'

  ask by Jonathan Wood translate from so

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.

(当尝试使用类似的实体模型重现该问题时，我或者遇到了有关DefaulIfEmpty(-1)的其他错误（显然不支持，请不要忘记删除它-SQL查询可以正常工作而不需要它）或没有删除错误。)

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

(特别是DbSet<Task> （在我的情况下是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., eg

(我意识到您将DbSet<T>类型用于集合导航属性，而不是通常的ICollection<T> ， IEnumerable<T> ， List<T>等，例如)

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.

(DbSet<T>是一个特殊的EF Core类，应该仅在DbContext用于表示数据库表，视图或原始SQL查询结果集。)

And more importantly, DbSet s are the only real EF Core query roots , so it's not surprising that such usage confuses the EF Core query translator.

(而且更重要的是， DbSet是唯一真正的EF Core查询根目录 ，因此这种用法会混淆EF Core查询转换器也就不足为奇了。)

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

(因此，将其更改为某些受支持的接口/类（例如ICollection<Task> ），即可解决原始问题。)

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

(然后删除DefaultIfEmpty(-1)将允许成功翻译有问题的第一个查询。)