Allow filtering for Include extension method


This item was migrated from the DevDiv work item tracking system [ID=5892].

This work item originated from connect.microsoft.com. A member of the EF team at Microsoft should close the related Connect issue when closing this work item.
Closed Dec 5, 2016 at 11:31 PM by RoMiller
EF Team Triage: We are transitioning this project to GitHub (https://github.com/aspnet/EntityFramework6). As part of this transition we are bulk closing a large number of issues in order that our new issue tracker will accurately reflect the work that our team is planning to complete on the EF6.x code base.

Moving forwards, our team will be fixing bugs, implementing small improvements, and accepting community contributions to the EF6.x code base. Larger feature work and innovation will happen in the EF Core code base (https://github.com/aspnet/EntityFramework). Closing a feature request in the EF6.x project does not exclude us implementing the feature in EF Core. In fact, a number of popular feature requests for EF have already been implemented in EF Core (alternate keys, batching in SaveChanges, etc.).

This is a bulk message to indicate that this issue was closed and not ported to the new issue tracker. The reasons for not porting this particular issue to GitHub may include:
• It was a bug report that does not contain sufficient information for us to be able to reproduce it 
• It was a question, but sufficient time has passed that it's not clear that taking the time to answer it would provide value to the person who asked it 
• It is a feature request that we are realistically not going to implement on the EF6.x code base
Although this issue was not ported, you may still re-open it in the new issue tracker for our team to reconsider (https://github.com/aspnet/EntityFramework6/issues). We will no longer be monitoring this issue tracker for comments, so please do not reply here.


RoMiller wrote Jan 29, 2013 at 12:39 AM

EF Team Triage: We agree that this would be a good scenario to enable. Taking into account where we are in the EF6 release along with the size and the impact of this feature our team is not planning to implement it in EF6. Therefore, we are moving it to the Future release to reconsider in the next release.

vulgarbinary wrote Feb 8, 2013 at 8:18 PM

If someone provided this capability from the open source community would it be considered for the EF6 release? Filtering on Included navigation properties is huge and given the demand to not have to pull back all associated navigation properties to filter after query execution or being forced into a very inefficient anonymous type select is something I've seen more than any other EF question on stack.

The reason I ask is regardless of the answer I intend to implement this capability and would hate to have to be out of version with the EF dll longer than necessary.

PlexGear wrote Apr 13, 2013 at 7:03 PM

Sorting has also been proposed.

divega wrote Apr 17, 2013 at 1:50 AM

@vulgarbinary: We would certainly consider a contribution in this area and we could accept it if the changes met our design, quality and testing criteria. I have to point out that I believe implementing this feature would most likely require changes to a very complex area of the EF codebase and that such changes are inherently high risk, so the risk would need to be mitigated appropriately, e.g. with very thorough test coverage, isolating the change from existing supported scenarios, etc.

My recommendation would be to start by reviewing our contribution guidelines and completing the requisites and by initiating a thread in our discussion forums. We can the start talk over your ideas on how to implement it.

Also, could you please elaborate about the 'very inefficient anonymous type select'. Are you talking about the difficulty of writing/reading complex projections or about runtime inefficiencies?

chrisduff wrote Sep 27, 2013 at 8:00 AM

The absence of this feature is my company's biggest issue with EF. We deal with databases that support soft-deletion and want to be able to eagerly load multiple entity levels with 'active' record filtering applied to each level. At the moment we have two options:

(1) Use non-filtered Include(), then disable lazy loading and explicitly filter out inactive records in memory before communicating the results to the client tier. Obviously this isn't ideal for performance!

(2) Use the cumbersome projection/anonymous type workaround. This works fine in simple cases but falls down in more complex scenarios due to restrictions on what you can do inside these projections. For example you can’t specify further Includes and you can’t dynamically construct an anonymous type to achieve dynamic Inclusion (as far as I can tell).

As a result we are currently using workaround #1 and knowingly creating potential performance problems (each time marked in our code with a TODO). I’m hoping this feature will be delivered soon so that we can go back and fix those potential performance problems.

It would be great if the EF team could commit to providing this feature or else provide a better workaround than the current projection workaround.

lucacestola wrote Oct 2, 2013 at 7:56 PM

It could be very useful when you have localized information of a principal entity, so you could load the principal entity and only the current locale related translation.
Actually you can make a projection to achieve this result but it's a forced solution and it's a real pain if you have many. Last but not least Hibernate has it and I cannot tolerate it ;)

cjmackenzie40 wrote Oct 17, 2013 at 11:37 PM

There is a solution to the "soft delete" issue here: http://stackoverflow.com/questions/12698793/soft-delete-entity-framework-code-first/18985828#18985828 However, I would like to apply a global filter based on a user's organisation, and using a discriminator won't work for me in that scenario

chrisduff wrote Oct 18, 2013 at 12:43 AM

@cjmackenzie40 - Thanks for posting the StackOverflow link re: the simple discriminator "soft delete" solution. Unfortunately that simple approach won't work for us. We don't have a simple "IsDeleted" property. Instead we have "CreateTimestamp" and "DeleteTimestamp" properties and need to be able to interact with the database in a temporal way. E.g. I might want to 'Include' related entities 'as at' a specific date/time in the past. Filtered Include would make this simple.

snz wrote Jul 3, 2014 at 11:50 PM

While we wait for this feature to be implemented, there appears to be a work around for now:
var blog = context.Blogs.Find(1); 
// Load the posts with the 'entity-framework' tag related to a given blog 
    .Collection(b => b.Posts) 
    .Where(p => p.Tags.Contains("entity-framework") 

chrisduff wrote Jul 4, 2014 at 5:15 AM

@snz - Thanks for pointing out this workaround. Unfortunately it is only useful in simple cases. It has the following problems:

(1) It requires separate database calls which is bad for performance. Your example would result in two database calls rather than one. It's worse if it must be done N times. E.g. What if I want to query all active Blogs, loading their Posts that are Tagged with "entity-framework"? Something like (if filtered include were possible):
var blogs = context.Blogs.Where(b => b.IsActive)
.Include(b => b.Posts, p => p.Tags.Contains("entity-framework"))
(2) It only works at the immediate child level. E.g. What if for a Blog I want to load only active Posts and for those Posts load any Tags that start with "e"? Something like (if filtered include were possible):
var blog = context.Blogs.Where(b => b.Id == 1)
.Include(b => b.Posts, p => p.IsActive)
.Include(b => b.Posts.Select(p => p.Tags), t => t.StartsWith("e"))
FYI the most promising workaround I've seen for more complex scenarios appears to be EntityFramework.Filters (available via NuGet). This might involve temporarily enabling/disabling global filters targeting 'included' entities. Also note that it currently has a bug stopping it from working on entities involved in inheritance.


realstrategos wrote Jan 16, 2015 at 6:33 PM

Could this be achieved (behind the scenes) by using table variables and multiple active result sets?

chrisduff wrote May 26, 2015 at 12:42 AM

An FYI for developers looking for a workaround for this, take a look at:


I'm currently using it with success.