How can I intersect more than two sets/lists of values?

Question

Here is an example that works in Linqpad. The problem is that I need it to work for more than two words, e.g. searchString = "headboard bed railing". This is a query against an index and instead of "Match Any Word" which I've done, I need it to "Match All Words", where it finds common key values for each of the searched words.

//Match ALL words for categories in index
string searchString = "headboard bed";

List<string> searchList = new List<string>(searchString.Split(' '));

string word1 = searchList[0];
string word2 = searchList[1];

var List1 = (from i in index
            where i.word.ToUpper().Contains(word1)
            select i.category.ID).ToList();         

var List2 = (from i in index
            where i.word.ToUpper().Contains(word2)
            select i.category.ID).ToList();             

//How can I make this work for more than two Lists?
var commonCats = List1.Intersect(List2).ToList();

var category = (from i in index
           from s in commonCats
           where commonCats.Contains(i.category.ID)
           select new 
           {
               MajorCategory = i.category.category1.description,
               MinorCategory = i.category.description,
               Taxable = i.category.taxable,
               Life = i.category.life,
               ID = i.category.ID
           }).Distinct().OrderBy(i => i.MinorCategory);

category.Dump();

Thanks!

cdhowie · Accepted Answer · 2011-09-06T17:13:14.647

3

Intersection of an intersection is commutative and associative. This means that (A ∩ B ∩ C) = (A ∩ (B ∩ C)) = ((A ∩ B) ∩ C), and rearranging the order of the lists will not change the result. So just apply .Intersect() multiple times:

var commonCats = List1.Intersect(List2).Intersect(List3).ToList();

So, to make your code more general:

var searchList = searchString.Split(' ');

// Change "int" if this is not the type of i.category.ID in the query below.
IEnumerable<int> result = null;

foreach (string word in searchList)
{
    var query = from i in index
                where i.word.ToUpper().Contains(word1)
                select i.category.ID;

    result = (result == null) ? query : result.Intersect(query);
}

if (result == null)
    throw new InvalidOperationException("No words supplied.");

var commonCats = result.ToList();

edited Sep 06 '11 at 17:13

answered Sep 06 '11 at 17:05

cdhowie

158,093
24
286
300

Thank you. I plugged this in to make it work for me, with very little effort, btw, and the query executes, but takes just over 7 mins and returns 75 items. I am expecting 4 items. Out of curiosity I modified my code(above) to handle the same three words. It took around 4 seconds. It looks like your code should work but I don't know why it takes so long and it increased the size of the sdf from 24.6 MB to 137 MBs. Any other ideas? FWIW, the index table has over 200,000 records. – Larry Pittman Sep 06 '11 at 19:04
I figured it out, at least what was needed to work and expeditiously. All I added was a .ToList() to the query in the foreach loop. By adding that the query now takes just around 4 seconds and the DB does not grow at all. Thank you for this great solution! – Larry Pittman Sep 06 '11 at 20:03
Try changing `result` to `IQueryable` and see if it performs better. If `index` represents a database table, this will move most of the logic into the database. I missed this possibility when writing my answer. – cdhowie Sep 06 '11 at 20:05
I just tried your suggestion, btw. I am a curious one. And it took over 12 mins before I killed it. The DB did not grow, however. These things are mysteries to me and I still have a lot to learn. Thanks to SO, yourself and cadrell0 I just learned something new. :-) – Larry Pittman Sep 06 '11 at 20:24

score 0 · Answer 2 · answered Sep 06 '11 at 17:33

0

To build on @cdhowie's answer, why use Intersect? I would think you could make it more efficient by building your query in multiple steps. Something like...

if(string.IsNullOrWhitespace(search))
{
    throw new InvalidOperationException("No word supplied.");
}

var query = index.AsQueryable();

var searchList = searchString.Split(' ');

foreach (string word in searchList)
{
    query = query.Where(i => i.word.ToUpper().Contains(word));
}

var commonCats = query.Select(i => i.category.ID).ToList();

answered Sep 06 '11 at 17:33

cadrell0

17,109
5
51
69

Thank you. See my notes to cdhowie for more info. I think your query should work as well, but it took over 12 mins and the DB got too big for it to finish. I just don't know why your alternative efforts are so much slower. – Larry Pittman Sep 06 '11 at 19:07

How can I intersect more than two sets/lists of values?

2 Answers2