Select where all the values ​​in the <int> list are.

Question

I have a column with identifiers separated by commas, I get this in a List in LINQ.

I only need to return DataRows, where all the values ​​in this list are present in another, I work if they are, but I need everything.

Code below:

results = results.Where( d => d["FilterIDs"] != null && // Check the row has FilterIds !filterValues.Except( d["FilterIDs"].ToString().Split(',').ToList(). // Tokenise the string Where(s => !String.IsNullOrEmpty(s)).ToList(). // Filter out bad tokens ConvertAll<int>(s => Convert.ToInt32(s)) // Convert all the tokens to ints ).Any()); } 

So for example, I have a row that has 1,2,3,4 in the column 'FilterIDs'

Then I have 1,2,3,4,5,6,7 in my list of "filterValues" - in this case this string will be returned.

The other line has 1,8,9 - only 1 match, so this will not be returned.

I spun around and lost my will with this, so any help is greatly appreciated.

Answer 1

It seems that you just changed your Except call - you used A.Except(B) when you mean B.Except(A) .

So, here is how I would write it:

 var query = from row in results let filterIds = row["FilterIDs"] where filterIds != null let numbers = filterIds.ToString() .Split(',') .Where(s => !string.IsNullOrEmpty(s)) .Select(int.Parse) where !numbers.Except(filterValues).Any() select row; 

Answer 2

I would use Enumerable<T>.All() :

 results = results.Where(d => d["FilterIDs" != null && d["FilterIDs"].ToString() .Split(',', StringSplitOptions.RemoveEmptyEntries) .Select(Int32.Parse) .All(f => filterValues.Contains(f))); 

Answer 3

How about this?

 results = results.Where( d => !d.IsNull("FilterIDs") && // Check the row has FilterIds d["FilterIDs"] .ToString() .Split(',') // Tokenise the string .Where(s=>!String.IsNullOrEmpty(s)) .ConvertAll<int>(s => Convert.ToInt32(s)) .All(i => filterValues.Contains(i) ) );