181
Vote

Allow filtering for Include extension method

description

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.

comments

RoMiller wrote Jan 28, 2013 at 11:39 PM

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 7: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 6:03 PM

Sorting has also been proposed.

divega wrote Apr 17, 2013 at 12: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 7: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 6: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 10: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 17, 2013 at 11:43 PM

@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 10: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 
context.Entry(blog) 
    .Collection(b => b.Posts) 
    .Query() 
    .Where(p => p.Tags.Contains("entity-framework") 
    .Load();
http://msdn.microsoft.com/en-US/data/jj574232

chrisduff wrote Jul 4, 2014 at 4: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"))
.ToList();
(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"))
.Single();
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.

https://github.com/jbogard/EntityFramework.Filters

realstrategos wrote Jan 16 at 5:33 PM

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