How to filter with a list of keywords on a DBSet

user_9730836 0
Viewed 44

I have in my Controller :

var query = _context.Jobs.Where(x => x.Category.Equals(filterParams.Category) ||
filterParams.Query.Any(val => x.Title.Contains(val)));

And this is the FilterParams class

    public class FilterParams
    {
        public string[] Query { get; set; }
        public CategoryEnum Category { get; set; }
    }

The filter on the Category works fine, but the Title part doesn't. I tried a bunch of different ways but it never gives me the expected behavior which is:

Given the following filterParams.Query (string[]) : ['FOO', 'BAR'] I want it to return entities with title such as: foo alice bar ; foo bar bob

How can we filter based on an array ?

The error I get is :

System.InvalidOperationException: The LINQ expression 'val => EntityShaperExpression: 
    ynyn_be.Models.Job
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.Title.Contains(val)' 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'.

I don't understand because, after the filters, I'm doing my Skip() and Take() with a ToListAsync, so why is it asking me to do it again ? I'm confused.

entity-framework c#
1 Answers

I found a workaround which may be temporary until I find a cleaner way of doing it, still sharing it if it can help others

                IQueryable<Job> query = _context.Jobs;

                if (filterParams.Category != null)
                {
                    query = query.Where(x => x.Category.Equals(filterParams.Category));
                } 

                foreach (var key in filterParams.Query)
                {
                    query = query.Where(x => 
                        x.Title.ToLower().Contains(key) ||
                        x.Description.ToLower().Contains(key));
                }

I just iterate over my array of keywords and add them to my IQueryable.

user_97308360
Related