LINQ - How to ignore empty lists in where

Question

these are my first steps with LINQ. I have two lists for filters as parameters, which can also be empty. If I execute the code this way, I don't get any values back from an empty list.
What does the code have to look like if empty lists are to be ignored?

    public List<PersonDTO> GetPersons(int pageNumber, int pageSize, List<string> departments, List<string> locations, string filterText)
    {
        if (filterText == null)
        {
            filterText = "";
        }

        List<Person> personsList = _dbContext.Persons
            .Where(a => (a.firstName.ToLower().Contains(filterText.ToLower()) || a.lastName.ToLower().Contains(filterText.ToLower()))
                        && departments.Contains(a.department) 
                        && locations.Contains(a.location))
            .Skip(pageNumber * pageSize).Take(pageSize).ToList();

        return _mapper.Map<List<PersonDTO>>(personsList);
    }

Remove `&& departments.Contains(a.department)`. Assign the return value of the `Where` to a temp variable. Check the `Count` of `departments`. If it is anything but zero - use `tempVariable = tempVariable.Where(z => departments.Contains(z.department)`. Lather, rinse, repeat (for `locations`). Then later call `Skip` on the temp variable. — mjwills, Sep 05 '19 at 12:33
Can you post a [mcve] with Linq to object or even with good old loop? — aloisdg, Sep 05 '19 at 12:35

Tim Schmelter · Accepted Answer · 2019-09-05T12:52:08.270

Handle the case that they are empty not in the query but with if:

IEnumerable<Person> persons = _dbContext.Persons;
if(!string.IsNullOrEmpty(filterText))
{
    string lowerFilterText = filterText.ToLower();
    persons = persons
       .Where(p => p.firstName.ToLower().Contains(lowerFilterText) || a.lastName.ToLower().Contains(lowerFilterText));
}
if(departments.Any())
{
    persons = persons.Where(p => departments.Contains(p.department));
}
if(locations.Any())
{
    persons = persons.Where(p => locations.Contains(p.location));
}
List<Person> personList = persons.Skip(pageNumber * pageSize).Take(pageSize).ToList();

Due to LINQ's deferred execution this will execute the final query only once, at the final ToList.

If you add it, perfect. It seems to be initialized by stepping on the above — J4ime, Sep 06 '19 at 07:15

score 0 · Answer 2 · answered Sep 05 '19 at 12:50

See if negating Any() can help you. Example:

string[] arr = new string[10] {"a","b","c","d","e","f","g","h","i","j"};
List<string> vowels = new List<string>() {"a","e","i","o","u"};
List<string> empty = new List<string>();

arr.Where(letter => vowels.Contains(letter)); 
//yields "a","e","i"
arr.Where(letter => (!empty.Any() || empty.Contains(letter))); 
//yields "a","b","c","d","e","f","g","h","i","j"

Following your example, I'd chain .Where() expressions rather than putting it all into a big, single one.

score 0 · Answer 3 · answered Sep 05 '19 at 13:05

You need to wrap the lists in parenthesis and use a count validation to make it optional for each list, like so:

List<Person> personsList = _dbContext.Persons
    .Where(a => 
        (a.firstName.ToLower().Contains(filterText.ToLower()) || 
        a.lastName.ToLower().Contains(filterText.ToLower())) && 
        (departments.Count == 0 || departments.Contains(a.department)) && 
        (locations.Count == 0 || locations.Contains(a.location)))
    .Skip(pageNumber * pageSize)
    .Take(pageSize)
    .ToList();

This way you switch the array conditional to an optional state, so whenever there is an item in the array !(list.Count == 0) it then tries to evaluate the filter.

score 0 · Answer 4 · answered Sep 06 '19 at 07:13

You need something like this :

Why would you use Expression> rather than Func?

Expression<Func<Persons, bool>> expresionFinal = c => c.Active;

    if (departments.Any())
                {
                    Expression<Func<Persons, bool>> expresionDepartments = c => departments.Contains(p.department);
                    expresionFinal = PredicateBuilder.And(expresionFinal, expresionDepartments);
                }

IQueryable query = dataContext.Persons;
 query = query.Where(expresionFinal);

score -1 · Answer 5 · answered Sep 05 '19 at 12:38

-1

Try using .Where(s => !string.IsNullOrWhiteSpace(s)) to filter out null and empty strings entries in the list.

answered Sep 05 '19 at 12:38

Ivan B

84
8

aloisdg · Answer 6 · 2019-09-05T12:52:27.847

If you want to ignore empty list (departements and locations) in your where clause, you should be able to use Any():

public List<PersonDTO> GetPersons(int pageNumber, int pageSize, List<string> departments, List<string> locations, string filterText)
{
    if (filterText == null)
    {
        filterText = "";
    }

    List<Person> personsList = _dbContext.Persons
        .Where(a => (a.firstName.Contains(filterText, StringComparison.OrdinalIgnoreCase)
                      || a.lastName.Contains(filterText, StringComparison.OrdinalIgnoreCase))
                    && (!departments.Any() || departments.Contains(a.department))
                    && (!locations.Any() || locations.Contains(a.location)))
        .Skip(pageNumber * pageSize).Take(pageSize).ToList();

    return _mapper.Map<List<PersonDTO>>(personsList);
}

and the code for contains:

public static class StringExtensions
{
    public static bool Contains(this string source, string toCheck, StringComparison comp)
    {
        return source?.IndexOf(toCheck, comp) >= 0;
    }
}

source: https://stackoverflow.com/a/444818/1248177

score -1 · Answer 7 · answered Sep 05 '19 at 12:44

-1

There are several way to check if list is empty: 1. If(list.Count() >0) 2. The best way, is to use "Any" instead of "Where" ,this will return boolian result, if true so there is some data, else there is nothing.

answered Sep 05 '19 at 12:44

Khaled Alkasadi

17
1

Jay Jordan · Answer 8 · 2019-09-05T13:11:07.277

This should work. You should really build up your query before you call ToList() if you can. This will allow you to be able to do things in stages and make your application more efficient.

public List<PersonDTO> GetPersons(int pageNumber, int pageSize, List<string> departments, List<string> locations, string filterText = "")
{
    List<Person> personList = new List<Person>();

    if (!string.IsNullOrEmpty(filterText)) {
        personsList = _dbContext.Persons
            .Where(a => (a.firstName.ToLower().Contains(filterText.ToLower()) || a.lastName.ToLower().Contains(filterText.ToLower()))
                    && departments.Contains(a.department) 
                    && locations.Contains(a.location)).ToList();
    } else {
        personList = _dbContext.Persons.ToList();
    }

        personList = personList.Skip(pageNumber * pageSize).Take(pageSize).ToList();

    return _mapper.Map<List<PersonDTO>>(personsList);
}

Here is another example using IQueryable.

public List<PersonDTO> GetPersons(int pageNumber, int pageSize, List<string> departments, List<string> locations, string filterText = "")
    {
        IQueryable<List<Person>> personQuery = _dbContext.Persons.AsQueryable();

        if (!string.IsNullOrEmpty(filterText))
        {
            personQuery = personQuery
                .Where(a => (a.firstName.ToLower().Contains(filterText.ToLower()) || a.lastName.ToLower().Contains(filterText.ToLower()))
                        && departments.Contains(a.department)
                        && locations.Contains(a.location));
        }

        personQuery = personQuery.Skip(pageNumber * pageSize).Take(pageSize);

        return _mapper.Map<List<PersonDTO>>(personQuery.ToList());
    }

This is an example of how I did what you are trying to do.

public List<CourseSearchDetail> GetPaginated(SearchRequest searchRequest, bool admin, out int totalRecords,
        out int recordsFiltered)
    {
        var query = _courseRepo
            .GetDataTableQuery();

        if (!admin) query = query.Where(x => x.CourseDate > DateTime.Now);

        var courseList = query.ToList();

        totalRecords = courseList.Count();

        if (!string.IsNullOrEmpty(searchRequest.Search.Value))
            courseList = courseList.Where(x => x.CourseTitle.ToLower().Contains(searchRequest.Search.Value.ToLower())).ToList();

        recordsFiltered = courseList.Count();

        if (searchRequest.Order == null)
            courseList = courseList.OrderByDescending(x => x.CourseDate).ToList();
        else
            courseList = courseList.OrderResults(searchRequest);
        var skip = searchRequest.Start;
        var pageSize = searchRequest.Length;

        courseList = pageSize > 0
            ? courseList.Skip(skip).Take(pageSize).ToList()
            : courseList.ToList();

        return courseList;
    }

I can add snippets for SearchRequest class if you need it. – Jay Jordan Sep 05 '19 at 12:57 — Jay Jordan, Sep 05 '19 at 12:57

LINQ - How to ignore empty lists in where

8 Answers8

Linked