具有条件包含的EF查询

问题描述

我有两个表：一个WorkItem表和一个WorkItemNote表。我如何返回一个WorkItem和所有符合特定标准的WorkItemNotes？

我认为这应该很简单，几乎像条件包含，对吗？ / p>

解决方案

我一直在计划写作，但你的问题打败了我。

假设一个 WorkItem 有很多 WorkItemNotes

你可以这样做：

  var intermediary =（从ctx.WorkItems中的项目
从item.Notes 
其中note.SomeProp == SomeValue 
 select new {item，note}）AsEnumerable（）; 
  

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

EF身份解析保证了相同的 WorkItem （通过引用）如果有多个 WorkItemNotes 符合条件，则会多次返回。

我假设接下来你只想回到 WorkItems ，像这样：

  var workItems = intermediary.Select（x => x.item）.Distinct（）。ToList（）; 
  

然后，如果你现在这样做：

  foreach（工作项中的var workItem）
 {
 Console.WriteLine（workItem.Notes.Count）
} 
  

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

这是因为一些叫做关系修正的东西。

这个给你你想要的条件包括。

希望这有帮助

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.

Assuming a WorkItem has many WorkItemNotes

you can do this:

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

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

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

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

Then if you now do this:

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

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

This is because of something called Relationship Fixup.

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

Hope this helps

Alex

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