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.

Answer 1

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);
}

Answer 2

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)
    {
    }
}