Combining functions conditionally to create Linq filter

Question

I have a test entity repository and test entity filter that I want to query conditionally.

Test entity filter looks like the following:

public class TestEntityFilter
{
    public string Name { get; set; } = null!;
    public string Type { get; set; } = null!;
    public Status? Status { get; set; }
    public int Take { get; set; }
    public int PageNumber { get; set; }
}

I want the query to return everything when Name, Type and Status are null and filter using the given values when they are not. If one of them is null it will be ignored and the other two will be used to filter.

For this conditional query, I have tried to create functions conditionally and combine them based on the values, but my repository method seems not to be working. What is the best way to approach this kind of problem?

public async Task<IEnumerable<TestEntity>> GetTestEntitiesAsync(TestEntityFilter testEntityFilter, CancellationToken cancellationToken)
{
    var combinedFilter = BuildFilers(testEntityFilter);
    return await _dbContext.TestEntity
        .Where(testEntity => combinedFilter(testEntity))
        .ToListAsync(cancellationToken);
}
    
public Func<TestEntity, bool> BuildFilers(TestEntityFilter testEntityFilter)    
{   
    Func<TestEntity, bool> mainFilter = testEntityFilter => true;
    Func<TestEntity, bool> filterByName = string.IsNullOrEmpty(testEntityFilter.Name) ? 
        (TestEntity j) => true :
        (TestEntity j) => testEntityFilter.Name == j.Name;

    Func<TestEntity, bool> filterByType = string.IsNullOrEmpty(testEntityFilter.Type) ?
        (TestEntity j) => true :
        (TestEntity j) => testEntityFilter.Type == j.Type; 

    Func<TestEntity, bool> filterByStatus = testEntityFilter.Status is null?
        (TestEntity j) => true :
        (TestEntity j) => testEntityFilter.Status == j.Status;

    Func<TestEntity, bool> combinedFilter = (TestEntity j) => mainFilter(j) && filterByName(j) && filterByType(j) && filterByStatus(j);
}

Running the above method with filter throws the following exception:

'The LINQ expression 'DbSet<TestEntity>()
    .Where(j => Invoke(__combinedFilter_0, j))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Answer 1

You can try this:

public async Task<IEnumerable<TestEntity>> GetTestEntitiesAsync(TestEntityFilter testEntityFilter, CancellationToken cancellationToken)
    {
        var combinedFilter = BuildFilers(testEntityFilter);

        var data = BuildFilers(_dbContext.TestEntity.AsQueryable(), testEntityFilter);

        return await data.ToListAsync(cancellationToken);
    }



    public IQueryable<TestEntity> BuildFilers(IQueryable<TestEntity> entities, TestEntityFilter testEntityFilter)
    {
        if (string.IsNullOrEmpty(testEntityFilter.Name))
        {
            entities = entities.Where(j => testEntityFilter.Name == j.Name);
        }

        if (string.IsNullOrEmpty(testEntityFilter.Type))
        {
            entities = entities.Where(j => testEntityFilter.Type == j.Type);
        }

        if (testEntityFilter.Status.HasValue)
        {
            entities = entities.Where(j => testEntityFilter.Status == j.Status);
        }

        return entities;
    }