Combining functions conditionally to create Linq filter-CodePudding

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 the other two will be used to filter. For this conditional query, I have tried to create a functions conditionally and combine them based on the values but my repository method does not look like it is not 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 throw 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'.

CodePudding user response：

You can just combine the Where conditions

var res = _db.TestEntity;
if (!string.IsNullOrWhiteSpace(filter.Name))
    res = res.Where(x => x.Name = filter.Name)
        
if (!string.IsNullOrWhiteSpace(filter.Status))
    res = res.Where(x => x.Status = filter.Status)
        
// .. rest
            
return await res.ToListAsync(cancellationToken);

CodePudding user response：

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