带条件包含的 EF 查询 [英] EF Query With Conditional Include

问题描述

我有两个表:一个 WorkItem 表和一个 WorkItemNote 表.如何返回一个 WorkItem 和所有符合特定条件的 WorkItemNotes?

I have two tables: a WorkItem table, and a WorkItemNote table. How do I return a WorkItem and all of the WorkItemNotes that meet a certain criteria?

我认为这应该很简单，几乎就像一个有条件的包含"，对吧?

I think this should be simple, almost like a conditional "Include", right?

推荐答案

我一直在计划写一个提示，但你的问题让我大吃一惊.

I've been planning on writing a tip on this but your question beat me to the punch.

假设一个WorkItem有很多WorkItemNotes

你可以这样做:

var intermediary = (from item in ctx.WorkItems
              from note in item.Notes
              where note.SomeProp == SomeValue
              select new {item, note}).AsEnumerable();

这会为每个匹配的 WorkItemNote 生成一个匿名元素，并保存相应的 WorkItem.

This produces an anonymous element for each WorkItemNote that matches, and holds the corresponding WorkItem too.

EF 标识解析确保相同的 WorkItem(通过引用)在具有多个符合条件的 WorkItemNotes 时被多次返回.

EF identity resolution insures that the same WorkItem (by reference) is returned multiple times if it has multiple WorkItemNotes that match the criteria.

我假设接下来您只想回到 WorkItems，如下所示:

I assume that next you want to just get back to just the WorkItems, like this:

var workItems = intermediary.Select(x => x.item).Distinct().ToList();

如果你现在这样做:

foreach(var workItem in workItems)
{
   Console.WriteLine(workItem.Notes.Count)
}

您将看到与原始过滤器匹配的 WorkItemNotes 已添加到每个 workItem 的 Notes 集合中.

You will see that WorkItemNotes that match the original filter have been added to the Notes collection of each workItem.

这是因为关系修复.

即这给了你你想要的条件包含.

I.e. this gives you what you want conditional include.

希望能帮到你

亚历克斯

这篇关于带条件包含的 EF 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！