EntityFramework 5 filter an included navigation property

Question

I would like to find a way using Linq to filter a navigation property to a subset of related entities. I know all answers around this subject suggest doing an anonymous selector such as:

query.Where(x => x.Users.Any(y => y.ID == actingUser.ID)) .Select(x => new { Event = x, Discussions = x.Discussions.Where(actingUser.GenerateSecurityFilterFor<Domain.Discussion>()) }) .OrderBy(x => x.Discussions.Count()) .ThenBy(x => x.Event.Name);

However, this is significantly less than ideal due to the general nature of our query generation and also yields significantly horrific sql queries if you throw up profiler.

I would like to be able to accomplish something like:

query.Include(x => x.Discussions.Where(actingUser.GenerateSecurityFilterFor<Domain.Discussion>())) .OrderBy(x => x.Discussions.Count()) .ThenBy(x => x.Name);

I realize that this is not supported in EF5 (or any version for that matter) but there has to be a way to accomplish constraining the result set through Linq without delving into anonymous type select statements.

I have attempted doing something to the tune of:

query.GroupJoin(discquqery, x => x.ID, x => x.Event.ID, (evt, disc) => evt.Discussions = disc.Where(actingUser.GenerateSecurityFilterFor<Domain.Discussion>())).ToList();

However you cannot have assignment inside a lambda expression and selecting an anonymous type here causes the same dilemma that it does using the select.

I guess I cannot comprehend why EF does not provide a way (that I can find) to generate:

SELECT --Properties FROM Event e LEFT OUTER JOIN Discussions d ON e.ID = d.EventID AND --Additional constraints WHERE --Where conditions ORDER BY --Order Conditions

It is so simple to constrain the join in SQL there HAS to be a way to do it through Linq as well.

PS: I have searched stack, MSDN, experts-exchange, etc. Please realize this is not a duplicate. Anything even touching on this subject either has a cop-out "It can't be done" answer or no answer at all. Nothing is impossible... including this.

Well, here is an "it can be done" answer: stackoverflow.com/a/13904825/861716 — Gert Arnold
– Gert Arnold, Commented Feb 6, 2013 at 23:08
There are ways to extend the Query generation engine in EF. So it isn't impossible, and if that's the only route then the answer is the extension to the EF query engine to allow the INNER / OUTER JOIN to contain additional filters. NOTHING is impossible... if I have to recompile EF then I will... It not having an existing answer means the answer is building it yourself (which I currently and even when I asked the question, planned on doing) — VulgarBinary
– VulgarBinary, Commented Feb 7, 2013 at 20:10

Ladislav Mrnka · Accepted Answer · 2013-02-07 09:09:20Z

Anything even touching on this subject either has a cop-out "It can't be done" answer or no answer at all. Nothing is impossible... including this.

Sure. It is possible. You can download EF source code and add this feature yourselves. It will be great contribution to open source project and the community. I believe EF team will gladly help you with your effort.

With the current version "it can't be done" is the answer. You can either use projection to anonymous or special unmapped type as you have described in the beginning of your question. Other options are separate explicit query to load related entities for single parent or separate query to load related entities for all parents.

Load relations for single parent:

context.Entry(event) .Collection(e => e.Discussions) .Query() .Where(d => ...) .Load();

Load relations for all parents (requires lazy loading to be turned off):

// load all parents var events = query.Where(e => ...).ToList(); // load child filtered by same condition for parents and new condition for children childQuery.Where(d => e.Event ... && d.Something ...).Load();

The second solution requires child to have navigation property back to parent (for constructing same query condition used initially to loads parent). If you have everything correctly configured and entities are attached EF should automatically fix your relations (collections) in parent entities (but it will not mark collection in dynamic proxy as loaded so that is the reason why you cannot use this together with lazy loading).

then apparently I'm extending the EF5 core. Do you know if there is a way to extend instead of modifying and recompiling the entire DLL?
You will have to recompile the DLL. You can visit EF codeplex site and discuss this contribution with EF team directly - they may have already some high level design for this feature and share it with you.

Collectives™ on Stack Overflow

EntityFramework 5 filter an included navigation property

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related