CODEWITHSUNDEEP
c#sqllinq
dhardy
dhardy

Reputation: 1003

select where all values present in List<int>

I have a column that has comma separated IDs, I get this into a List in LINQ.

What I need to do is only return DataRows where all values within this list are present in another, I've got it working if any are present but I need all.

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 the 1,2,3,4 in it's column 'FilterIDs'

I then have 1,2,3,4,5,6,7 in my List 'filterValues' - in this instance, this row would be returned.

Another row has 1,8,9 - only 1 matches so this wouldn't be returned.

I've been going round in circles and have lost the will with this so any help is greatly appreciated.

Upvotes: 4

Views: 376

Answers (3)

D Stanley
D Stanley

Reputation: 152594

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) )
      );

Upvotes: 0

Ani
Ani

Reputation: 113442

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

That said, here's how I would write this:

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;

Upvotes: 2

lc.
lc.

Reputation: 116528

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)));

Upvotes: 0

Related Questions