Filter on Include() in EF

August 13, 2024

Is there a way to filter based on a property in EF

public async Task<Challenge?> GetByIdAsync(int id)
{
    var item = await _context.Challenges
    .Include(x => x.Upload) // Where Upload.DeletedOn == null Else null
    .FirstOrDefaultAsync(x => x.Id == id && x.DeletedOn == null);
    return item;
}

I know I can filter afterwards but I don’t want to do that and increase complexity as there’ll be 100+ APIs that require a somewhat similar filter.

Tried to go through this but this is not on a one-to-one relationship – filtered-include

>Solution :

You can accomplish this in two ways:

Using Global Query Filters:

Define a filter for both entities as follows:

modelBuilder.Entity<Challenge>().HasQueryFilter(x => x.DeletedOn == null);
modelBuilder.Entity<Upload>().HasQueryFilter(x => x.DeletedOn == null);

If your entities implement an interface or inherit from a base class, you can apply a query filter to all of them using a simple extension method, like this one: ApplyQueryFilter.

modelBuilder.ApplyQueryFilter<ISoftDelete>(e => e.DeletedOn == null);

Your query would then be straightforward:

public async Task<Challenge?> GetByIdAsync(int id)
{
    var item = await _context.Challenges
        .Include(x => x.Upload) 
        .FirstOrDefaultAsync(x => x.Id == id);
    return item;
}

Using Select:

public async Task<Challenge?> GetByIdAsync(int id)
{
    var item = await _context.Challenges
        .Where(x => x.Id == id && x.DeletedOn == null)
        .Select(x => new Challenge
        {
            Id = x.Id,
            // other properties
            Upload = x.Upload.DeletedOn == null ? x.Upload : null
        })
        .FirstOrDefaultAsync();
    return item;
}