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

Question

I want to search a database of books by several keywords. The more keywords I provide the narrower the search is to 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 in fact get narrower the more keywords I provide. The results even vary depending on the order in which I provide the keywords. Also, that last OrderBy() call doesn't work reliably if more than one keyword is involved. Is my idea flawed, or the way I implement it?

Answer 1

You are closing over the word variable, and encountering an access to modified closure problem.

On each iteration of the loop, you are capturing the value of a string from your words collection into the variable word. But LINQ uses deferred execution, and your query does not execute until after the loop is complete, at which point the same word variable is captured by all instances of your query - hence you are seeing your results vary with the order of the search keywords provided.

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

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