CODEWITHSUNDEEP
c#linq
Daniel
Daniel

Reputation: 3374

search the database for the words within a string

Imagine that a user entered a sentence and I need to search for the subjects that consist of words within the entered sentence. These are the code that I thought they could solve the case.

var result = from x in dataBase.tableName 
             select x;

string[] words = enteredString.Split(); 

foreach(string word in words) 
            {
                result = result.Where(x => x.subject.Contains(word));
            }

it shows only the search result with the last word in sentence, but I thought the result must be narrowed down each time a word is used in the where line.

Upvotes: 0

Views: 1684

Answers (4)

J. Tihon
J. Tihon

Reputation: 4459

I hade some success by inverting the logic like this:

string[] words = enteredString.Split();

var results = from x in database.TableName
              where words.Any(w => x.subject.Contains(w))
              select x;

-- Edit A more generic approach, for this kind of queries, would be:

class SearchQuery
{
    public ICollection<string> Include { get; private set; }
    public ICollection<string> Exclude { get; private set; }
}

[...]
SearchQuery query = new SearchQuery
{
    Include = { "Foo" }, Exclude = { "Bar" }
}

var results = from x in database.Table
              where query.Include.All(i => x.Subject.Contains(i)) &&
                    query.Exclude.All(i => !x.Subject.Contains(i))
              select x;

This assumes that all words in query.Include must occur in Subject, if you want to find any subjects that have at least one of the words query.Include.All should be query.Include.Any

I've tested this with Entity Framework 4. Which will create a SQL query that applies all criteria in the database rather than in memory.

Upvotes: 2

Ankur
Ankur

Reputation: 33637

Here you go:

var result = from x in dataBase.tableName 
             select x;

string[] words = enteredString.Split(); 
result.Where(r => words.Any(w => r.Subject.Contains(w));

Upvotes: 1

Yahia
Yahia

Reputation: 70369

it can't do the thing - since with every word you are overwriting the previous result - you need to do something similar to:

List<object> AllResults = new List<object>();

foreach(string word in words) 
{
  var temp = word;
  AllResults.AddRange (result.Where(x => x.subject.Contains(temp)).ToList());
}

Not sure what type your result type is hence the List<object>...

Upvotes: 0

Paul Phillips
Paul Phillips

Reputation: 6259

Try this:

foreach(string word in words) 
{
  var temp = word;
  result = result.Where(x => x.subject.Contains(temp));
}

This is called (by ReSharper at least) "access to modified closure" - lambda expressions don't capture the value, they capture the entire variable. And the value of the variable word is changing with each iteration of the loop. So, since the Where() method is lazy-evaluated, by the time this sequence is consumed, the value of word is the last one in the sequence.

Upvotes: 5

Related Questions