Conditional eager loading?

Question

I want to load an entity and it's children conditionally (I only want to eager load the children when the child.IsActive == true). How do I perform the following?

var parent = 
    from p in db.tblParents.Include("tblChildren") <-- where tblChildren.IsActive == true
    where p.PrimaryKey == 1
    select p;

NOTE: I do not want to return an anonymous type.

Thanks.

Morteza Manavi · Accepted Answer · 2010-09-15T14:23:00.280

9

One way for doing so is:

var parent = from p in db.tblParents where p.PrimaryKey == 1
             select new {
                 Parent = p,
                 Children = p.tblChildren.Where(c => c.IsActive == true)
             }.ToList();

However, you might not like the idea to return an anonymous type, then I would suggest to code it this way:

var parent = (from p in db.tblParents where p.PrimaryKey == 1).Single();
var childrens = ctx.Contacts.Where(c => c.ParentID == 1 && c.IsActive == true);
foreach (var child in childrens) {
   parent.tblChildren.Add(child);
}

edited Sep 15 '10 at 14:23

answered Sep 15 '10 at 14:13

Morteza Manavi

33,026
6
100
83

I neglected to mention that I do not want to return an anonymous type... I need to return an object (or collection) of type tblParent. – alex.davis.dev Sep 15 '10 at 14:18
Sure, I added another code snippet that gives you a strong typed Parent object which contains all the children who matched the criteria. Please have a look. – Morteza Manavi Sep 15 '10 at 14:25
1

This makes sense... however, we have moved out of the realm of eager loading. – alex.davis.dev Sep 15 '10 at 14:40
1

So I am still back at my original question... in the interim, I have just used Linq to filter out records in memory... – alex.davis.dev Sep 15 '10 at 16:06
3

I am afraid there is no way to have a "Conditional Eager Loading" with include. The API for Include() method just has not been designed that way. The work around is to leverage **"Filtered Projection"** or get it done by implement **"Two Tracked Queries"**, my code snippets covers these two methods respectively. It would have been nice if we could code something like this though: Include("tblChildren", c => c.IsActive == true). – Morteza Manavi Sep 15 '10 at 16:53
Fair enough. I had a feeling this was the case. Thanks. – alex.davis.dev Sep 15 '10 at 17:02
@MortezaManavi It could potentially by harmful to have an Include method that's conditional because then the Childrens collection would actually contain a subset of the available children, but if the parent entity is then passed on or returned to another method unknowingly of the fact that the collection now contains a subset of all children and not all children, it might lead to semantic bugs. In that light your first proposed solution is more explicit and less error prone, but with the exception that the projection changed to a regular class object that could be returned. – Kaveh Hadjari Sep 08 '16 at 07:19

score 2 · Answer 2 · answered May 28 '14 at 17:09

Entity Framework 6 introduces Interception http://entityframework.codeplex.com/wikipage?title=Interception which can be used to adjust the SQL to Filter the children.

Before executing your query add an interceptor and remove when it's not relevant:

var interceptor = new ActiveTagsInterceptor();
DbInterception.Add(interceptor);

documents = context.Documents
                .AsQueryable()
                .Include(d => d.Tags)

DbInterception.Remove(interceptor);

Sample Interceptor which adds "[Active] = 1 And" when loading Tags:

public class ActiveTagsInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        // [Tag] AS [Extent6] ON   => [Tag] AS [Extent6] ON [Extent6].[Active] = 1 And 
        const string pattern = "\\[Tag\\]\\sAS\\s\\[([\\w]+)\\]\\sON";
        const string replacement = "$& [$1].[Active] = 1 And ";
        command.CommandText = Regex.Replace(command.CommandText, pattern, replacement);
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }
}

While I don't really like manually modifying Entity's SQL, this works perfectly. With this I don't have to modify my query code at all after implementing soft deletion. +1 — Nathan, Jul 30 '15 at 21:06

Amir Chatrbahr · Answer 3 · 2016-06-15T22:41:20.030

To be able to apply filter, better option is using Explicitly loading along with Query() instead of Eager loading :

var parent = db.tblParents.Find(1);
db.Entry(parent).Collection(p => p.tblChildren).Query().
    Where(child => child.IsActive).Load();

The Query method provides access to the underlying query that the Entity Framework will use when loading related entities. Also you need to turn off lazy loading for the navigation property(remove Virtual keyword) otherwise the collection gets loaded automatically by the lazy loading which ignores your filter.

Conditional eager loading?

3 Answers3

Linked