我正在寻求如何编写查询的建议。对于每个Goal
,我想选择第一个Task
(按Task.Sequence
排序),以及任何具有ShowAlways == true
的任务。 (我的实际查询更加复杂,但是此查询演示了我遇到的限制。)
我尝试了类似以下的内容:
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();
但是这个查询似乎太过复杂。
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.'
问题在于这行代码:
let nextTaskId =...
。如果我注释掉它,就不会出错。(但我得不到我想要的结果。)我很容易承认我不理解错误信息的细节。我能想到的另一种方法就是返回所有的
Task
,然后在客户端对它们进行排序和过滤。但我的偏好是不检索我不需要的数据。还有其他人看到这个查询的其他方法吗?
注意:我正在使用最新版本的Visual Studio和.NET。
更新:
我尝试了一种不同但效率较低的方法来处理这个查询。
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
});
但这也产生了一个错误:
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.'
g
(组)与任务(Tasks)之间是一对多的关系,而t
(任务)具有一个“Sequence”列。我不明白你的替换查询如何获取我感兴趣的任务。使用SelectMany()
应该与from a in DbContext.Areas from g in a.Goals from t in g.Tasks
相同。 - Jonathan WoodnextTaskId
,例如:g.Tasks.OrderBy(tt => tt.Sequence).Min(tt => tt.Id)
或g.Tasks.OrderBy(tt => tt.Sequence).FirstOrDefault().Id
。 - CanicanextTaskId
的那一行。注释掉那一行就可以消除错误了。 - Jonathan Wood