How to apply WHERE condition to EF .Include() when building up EF query

Question

I have the following 2 classes:

public class Rule
{
    public int Id { get; set; }
    public string RuleValue { get; set; }
    public bool IsActive { get; set; }
    public SharedRuleType RuleType { get; set; }
    public List<Exclusion> Exclusions { get; set; }
}

public class Exclusion
{
    public int Id { get; set; }
    public int InstanceId { get; set; }
    public int SiteId { get; set; }
    [ForeignKey( "RuleId" )]
    public int RuleId { get; set; }
    public Rule Rule { get; set; }
}

I then have an EF query that brings back 'all active' Rules, and I need it to .Include Exclusions for each Rule (if there are any) BUT only Exclusions that have been assigned the specified InstanceId. So the filtering is being done against the Exclusions property, rather than filtering out Rules.

I also have a few conditions as I build up my EF query that I need to take into consideration.

Here is my query at the moment:

public async Task<List<Rule>> GetRules(int instanceId, SharedRuleType ruleType, string searchTerm)
{
    using ( var context = new MyDbContext() )
    {
        var query = context.Set<Rule>()
            .Include( r => r.Exclusions ) // *** Currently returns ALL exclusions but I only want ones where InstanceId == instanceId(param) ***
            .Where( r => r.IsActive );

        if ( !string.IsNullOrEmpty( searchTerm ) )
        {
            query = query.Where( r => r.RuleValue.Contains( searchTerm ) );
        }

        if ( ruleType != SharedRuleType.None )
        {
            query = query.Where( r => r.RuleType == ruleType );
        }

        return await query.ToListAsync();
    }
}

I tried applying a .Where within the .Include() in an attempt to only include the relevant Exclusions (based on instanceId) but found out you can't do that. I hunted around and found some examples where people had used an anonymous type, but I couldn't get this working when building up the query piece by piece as I'm doing here.

So, I don't know how I can accomplish this as I really don't want to be returning 'every' Exclusion for each Rule, when I don't need every Exclusion returned.

Answer 1

The Include method cannot use a filter like you tried.

Solution #1

Disclaimer: I'm the owner of the project Entity Framework Plus

EF+ Query IncludeFilter feature allows filtering related entities.

public async Task<List<Rule>> GetRules(int instanceId, SharedRuleType ruleType, string searchTerm)
{
    using ( var context = new MyDbContext() )
    {
        var query = context.Set<Rule>()
            .IncludeFilter( r => r.Exclusions.Where(x => x.InstanceId == instanceId))
            .Where( r => r.IsActive );

        // ... code ...

Wiki: EF+ Query IncludeFilter

Solution #2

Another technique is to use projection (which is what my library do under the hood)

public async Task<List<Rule>> GetRules(int instanceId, SharedRuleType ruleType, string searchTerm)
{
    using ( var context = new MyDbContext() )
    {
        var query = context.Set<Rule>()
            .Where( r => r.IsActive );

        if ( !string.IsNullOrEmpty( searchTerm ) )
        {
            query = query.Where( r => r.RuleValue.Contains( searchTerm ) );
        }

        if ( ruleType != SharedRuleType.None )
        {
            query = query.Where( r => r.RuleType == ruleType );
        }


        // ToListAsync has been removed to make the example easier to understand
        return  query.Select(x => new { Rule = x,
                                        Exclusions = x.Exclusions.Where(e => e.InstanceId == instanceId)
                    })
             .ToList()
             .Select(x => x.Rule)
             .ToList();
    }
}

EDIT: Answer Sub-Questions #1

How to use ToListAsync with the previous example

You simply have to await for the first list

return  (await query.Select(x => new { Rule = x,
                                Exclusions = x.Exclusions.Where(e => e.InstanceId == instanceId)
            })
     .ToListAsync())
     .Select(x => x.Rule)
     .ToList();

EDIT: Answer Sub-Questions #2

How to perform Skip, Take, OrderBy on Rule

You do the same as you normally do

return  (await query.Take(15)
                    .Skip(5)
                    .OrderBy(x => x.RuleId)
                    .Select(x => new { Rule = x,
                                            Exclusions = x.Exclusions.Where(e => e.InstanceId == instanceId)
                                })
     .ToListAsync())
     .Select(x => x.Rule)
     .ToList();