Queryable.Intersect () with SQLite and Linq not yielding expected results

Question

I want to find a database of books by several keywords. The more keywords I provide, the narrower the search will be. Here is my code:

var words = text.Split(' '); IQueryable<Reference> query = null; foreach (string word in words) { var result = from r in _dbConnection.GetTable<Reference>() where r.Title.Contains(word) || r.ReferenceAuthor.Any(a => a.Person.LastName.Contains(word) || a.Person.FirstName.Contains(word)) || r.ReferenceCategory.Any(c => c.Category.Name.Contains(word)) || r.ReferenceKeyword.Any(k => k.Keyword.Name.Contains(word)) orderby r.Title select r; query = query == null ? result : query.Intersect(result); } query.OrderBy(r => r.Title); 

The problem is that the search does not actually narrow, the more keywords I provide. The results even vary depending on the order in which I provide the keywords. Also, this last call to OrderBy () does not work reliably if more than one keyword is involved. Is my idea wrong or a way to implement it?

Answer 1

You close the word variable and encounter access to the modified closure problem .

At each iteration of the loop, you write the string value from your words collection to the word variable. But LINQ uses deferred execution, and your query is not executed until the loop is completed, and at that moment the same word variable will be captured by all instances of your query - therefore, you will see that your results will vary depending on which search keywords are provided,

To fix this, take a local copy of the variable at each iteration of the loop.

 foreach (string w in words) { string word = w; ...