Filtering a collection of items from contents of another collection.

Question

This used to work for me and then it failed. I want to return only those items that contain all the filters, not at least one filter as it is doing now. WHat is wrong here?

private IQueryable<IndexItem> FilteredIndex (IQueryable<IndexItem> index, IEnumerable<string> filters)

    {

        var filteredIndex= from f in filters.AsQueryable() 
               where f.Length>0
               from i in index 
               where i.FilterNames.Contains(f)
               select i;
        return filteredIndex;
   }

Answer 1

How about something like:

foreach(string s in filters) {
    if(s.Length == 0) continue;
    string tmp = s; // because of "capture" problem
    index = index.Where(i => i.FilterNames.Contains(tmp));
}
return index;

This applies a succession of Where clauses, covering all the filters - essentially AND.

Answer 2

Turn it around. What you want is those items in index where every item in FilterNames has a corresponding entry in filters. I'm not sure how performant it'd be, but a count comparison should do. Something like:

private IQueryable<IndexItem> FilteredIndex(IQueryable<IndexItem> index, IEnumerable<string> filter)
{
    var filteredIndex = from i in index
                        where (from s in i.FilterNames
                               where filter.Contains(s)
                               select s).Count() == i.FilterNames.Count
                        select i;
    return filteredIndex;
}

Answer 3

Straight forward. For a given item from index check that it is true for all filters that the given item contains the filter. With this just select all items from index for that the given condition is true.

index.Where(item => 
   filters.All(filter => item.FilterNames.Contains(filter)))

I am not sure if the check for length greater than zero is required, nut it is easily integrated.

index.Where(item => 
   filters.All(filter =>
      (filter.Length > 0 ) || (item.FilterNames.Contains(filter))))

It works with LINQ to Objects and I guess it does what you want, but I am not sure if it works with LINQ to SQL.