I have a dataset that I need to filter on multiple columns (six in total). Not every column will be filtered, i.e. a user can filter on column 1 and 3, but not 2,4,5, or 6. I can filter successfully when there is only a single value in each column. However once a column has multiple filters it fails.
Use a scenario where I want to filter on multiple POCs
Here is my code:
IEnumerable<IssueDataModel> filteredIssues = issueList;//Passed in dataset
var pocitemsArray = Array.Empty<string>();
var notificationitemsArray = Array.Empty<string>();
if (filterColumnPOCs == "Y")//This indicates the user has selected the POC Filter
{
pocitemsArray = filterItemsPOCs.Split(';');//This is the string of users to filter on
foreach (string currentItem in pocitemsArray)//Checking for each POC
{
filteredIssues = filteredIssues.Where(s => s.PocName1.Contains(currentItem) || s.originatorPocName.Contains(currentItem));
}
}
if (filterColumnNotification == "Y")
{
notificationitemsArray = filterItemsNotification.Split(',');
foreach (string currentItem in notificationitemsArray)
{
filteredIssues = filteredIssues.Where(s => s.notificationType.Contains(currentItem));
}
}
The issue is that when I filter the first POC, the second pass thru there won’t be any matches for the second POC, so there are no records returned.
If I try matching on the initial dataset (issuelist) it works, but then I can’t cascade one filter to the next filter. I.e filter the dataset for POC and then take that filtered dataset to Notification for filtering. Keep in mind have have 6 total filter loops but not all may be used at the same time.
The problem is that you’re using individual Wheres for all values in each filter: consecutive Where statements are equivalent to doing condition1 && condition2
In effect, the column would have to have all values at the same time in order to not get removed. This is why you’re getting results for when there’s one possible value in the filter, but not when there’s multiple values in the filter. It’d be equivalent to if (x == values[0] && x == values[1])
You can instead rewrite the filtering to use one Where, and Any to match against any possible value, instead of using a Where per value:
IEnumerable<IssueDataModel> filteredIssues = issueList;//Passed in dataset
var pocitemsArray = Array.Empty<string>();
var notificationitemsArray = Array.Empty<string>();
if (filterColumnPOCs == "Y")//This indicates the user has selected the POC Filter
{
pocitemsArray = filterItemsPOCs.Split(';');//This is the string of users to filter on
filteredIssues = filteredIssues.Where(issue => // For every issue
pocitemsArray.Any(filterValue => // For ANY value in the filter
issue.PocName1.Contains(filterValue) // Match
|| issue.originatorPocName.Contains(filterValue)));
}
if (filterColumnNotification == "Y")
{
notificationitemsArray = filterItemsNotification.Split(',');
filteredIssues = filteredIssues.Where(issue => // For every issue
notificationitemsArray.Any(filterValue => // For ANY value in the filter
issue.notificationType.Contains(filterValue))); // Match
}
1