EF Query With Conditional Include

entity-framework

Solution

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

Problem

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?

Original source