Entity Framework Feature Suggestions

Allow filtering for Include extension method

There is no ablity to filter included objects in ObjectQuery<TSource>.Include method.
Allow filter predicate in Include method in Entity Framework.
I suppose this method of ObjectQuery<TSource> may have following signature:

/// <summary>
/// Includes related objects which meet to predicate
/// </summary>
/// <typeparam name="TRelation">Type of related object on another end of navigation property</typeparam>
/// <param name="relationSelector">Expression that returns set of related objects</param>
/// <param name="predicate">Predicate that has to be met</param>
/// <returns>Query</returns>
public System.Data.Objects.ObjectQuery<TSource> Include<TRelation>(Expression<Func<TSource, IEnumerable<TRelation>>> relationSelector, Expression<Func<TRelation, bool>> predicate);

1,227 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    Mike FlaskoMike Flasko shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    PKPK shared a merged idea: Allow predicates to be passed to .Include(..) keywords.  ·   · 
    PKPK shared a merged idea: Allow predicates to be passed to .Include(..) keywords.  ·   · 
    Diego VegaAdminDiego Vega (Development Lead, DataFx) responded  · 

    Reverting status from “under review” to “no status” since we aren’t currently working on this. We will still consider it for future releases.

    21 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Chris DuffChris Duff commented  ·   ·  Flag as inappropriate

        @Shannon - 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

      • Shannon SkinnerShannon Skinner commented  ·   ·  Flag as inappropriate

        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

      • George MohrGeorge Mohr commented  ·   ·  Flag as inappropriate

        This feature really couldn't be more important. I find myself building very inefficient queries and performing joins in memory due to the absence of this vital SQL function mapping. Please do add!!!

      • Erik SchierboomErik Schierboom commented  ·   ·  Flag as inappropriate

        This is the feature I miss the most when using EF instead of NHibernate. It is so incredibly useful that I really hope it will be added soon.

      • MikeMike commented  ·   ·  Flag as inappropriate

        You may be able to workaround this by flipping around your query for Linq to Entities, then flipping it back with Linq to Objects. Using the example above:

        var q = context.Products.Where(p => p.UnitPrice > 20 & p.Category.StartsWith("a")).Include(p => p.Category);

        var r = q.ToList().Select(p => p.Category).Distinct();

        Note: This would not work if you are expecting Categories that don't have products in your results.

      • craigcraig commented  ·   ·  Flag as inappropriate

        The suggestions below indicate that this can be with EF4. These comments are not correct, as the relationship depends specifically on change tracking. This can be illustrated as follows.

        var p = ParentTable.AsNoTracking().Select(o => new { Parent = o, Children = o.Children.Where(_ => _.Name.Length == "child")}).AsEnumerable().Select (_p => _p.Parent);

        does not work.

        var p = ParentTable.Select(o => new { Parent = o, Children = o.Children.Where(_ => _.Name.Length == "child")}).AsEnumerable().Select (_p => _p.Parent);

        Appears to work because a subset of the children happen to be change tracked.

      • PaulPaul commented  ·   ·  Flag as inappropriate

        This is something we really need. I have looked at some the alternatives mentioned below and as other comments have explained they are not adequate solutions.

      • DominikDominik commented  ·   ·  Flag as inappropriate

        Hi again,

        I think maybe I don't expressed myself very well. What I really need is to get those paged Categories having only products with UnitPrice greater than 20.

      • DominikDominik commented  ·   ·  Flag as inappropriate

        I need to perform something like this:

        context.Categories.Where(ca = ca.StartsWith("a")).Include(ca => ca.Products.Where(p => p.UnitPrice > 20));

      • PiersPiers commented  ·   ·  Flag as inappropriate

        Dan, the applying of filters mentioned in that post is when explicitly loading entities, not when Eagerly loading entities which is what Include would give.

        Moderator... this is the same suggestion as http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions/suggestions/1015345-allow-filtering-for-include-extension-method?ref=title so the scores should be added together!

      • PiersPiers commented  ·   ·  Flag as inappropriate

        Matthieu, your solution works if you know what you want to include at compile time... however the great thing about Include() is that it can be added to a query dynamically (e.g. within an if statement). Using a Projection means you lose that flexibility... doubly annoying as the introduction of a Projection also strips off any existing Include()s on the main query!!!

      • JakeJake commented  ·   ·  Flag as inappropriate

        Agreed, this doesn't solve the problem. This particular issue is a deal-breaker for our company and we'll have to switch back to LLBLGen Pro until this gets added to the framework.

      • PKPK commented  ·   ·  Flag as inappropriate

        OOO! i'd love to use Generics instead of strings anyday :) :) +1 +1 +1

      • Adrian HeskethAdrian Hesketh commented  ·   ·  Flag as inappropriate

        How about having a generic include, e.g. .Include<Users>().Include<Roles>() instead of passing a string too.

        Or something like Include<User>(u => u.Role) to include the role of a user instead of User.Include(Role).

      • PKPK commented  ·   ·  Flag as inappropriate

        @Matthieu - that's not acceptable. That's using a projection, which is not the same thing. We need to have predicates available to the Include .. so you can Filter, Take(x), Skip, etc...

        By your suggestion/blog post, it's an anonymous projection with properties. That's doesn't mean we can get right poco's with their aggregations filled appropriately. Imaging asking (aka. Including) all the audit history for _each_ user retrieved? instead of just the most recent 10? or the the most recent 11-20 audit records for each user.. u get the drift.

      Feedback and Knowledge Base