EF Query With Conditional Include

Question

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?

Answer 1

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