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.

Answer 1

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).