Linq to Sql Chained Intersect

Question

I can't figure out why this isn't intersecting all of the items in the loop, just the last 2. I think it has something to do with IQueryable

var outerquery = db.Employees.Where(x => x.Name = "Smith").Select(x => x.EmployeeID);

foreach(var name in nameList){
var innerQuery = db.Employees.Where(x => x.Name = name).Select(x => x.EmployeeID);
outerquery = outerquery.Intersect(innerQuery);
}

return outerquery.ToList();

EDIT - A more concrete example. The table has approx 35 million records.

The table has ID, ConceptID, Word. Words can have multiple ConceptIDs & there is 1 word per record. I was to intersect a search string 'shoulder pain chronic' and get all the ConceptIDs that share those 3 words. It should return:

Concept1234 - shoulder
Concept1234 - pain
Concept1234 - chronic

What I am getting (just the last 2):

Concept1234 - pain
Concept1234 - chronic

Doing an OR on 35 million records is rough even with this monster server I have & an intersect is the only way to do it in less than a second.

What I am trying to generate with LINQ to SQL (Entity Framework) is this -

SELECT ConceptID FROM WordTable WHERE Word = 'shoulder'
INTERSECT
SELECT ConceptID FROM WordTable WHERE Word = 'pain'
INTERSECT
SELECT ConceptID FROM WordTable WHERE Word = 'chronic'

Please provide more information: what do you expect and what did you get? Can you show us a concrete example? — Todd Li, Aug 22 '12 at 23:40

score 0 · Answer 1 · answered Aug 23 '12 at 00:13

0

You have outerquery inside the foreach loop which gets replaced in each iteration of the loop and you lose previous data.

answered Aug 23 '12 at 00:13

Bishnu Paudel

2,083
1
21
39

But shouldn't intersecting on the outerquery then setting it to the outerquery again keep the changes? – Alex Aug 23 '12 at 11:57

Linq to Sql Chained Intersect

1 Answers1