current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

I am trying to build a search that accepts multiple keywords (space delimited or comma, that is not the issue). I currently have this

public IQueryable<Post> Search(string criteria, int x)
    {
        return
            (_db.Posts.Where(p => p.IsActive && 
                (p.PostText.Contains(criteria) || p.Weather.Contains(criteria) || p.Location.Contains(criteria))
            ).OrderByDescending(p => p.PostDate)).Take(x);
    }

but that would return only exact matches. How would I search for each keyword and return x results? It's supposed to be an OR by the way.

Thanks

share|improve this question

3 Answers 3

up vote 1 down vote accepted

Try this, I am using parallelism to speed up the search. I am checking in a series of strings, of any of the keys passed in the string[] match. In my example, I am searching for "test" and "1", and you will see 3 results.

static void Main(string[] args)
    {            
        List<string> list = new List<string>();
        list.Add("b lue number 1");
        list.Add("test number 234");
        list.Add("yello number 2334");
        list.Add("yes whippea number 324234");
        list.Add("test number asdf");  

        var results = Program.Search(list,"test","1");

        Console.ReadLine();
    }

To follow your example, you can do something like this.

public List<Post> Search(string criteria, int x)
    {
        // Split all the search keys by space, so if you have "Search Word", you will get
        // the occurances of [Search] and also [Word]
        List<string> searchKeys = criteria.Split(' ').ToList<string>();

        // Filter active
        _db = _db.Where(p => p.IsActive);

        // Go through each key
        foreach (string str in searchKeys)
        {
            _db = _db.Where(p => p.Location.Contains(str)
                    || p.PostText.Contains(str)
                    || p.Weather.Contains(str));
        }

        // Return number wanted - and items will only be extracted here on the ToList()
        return _db.OrderByDescending(p => p.PostDate).Take(x).ToList();
    }

I am looping the criteria and adding the conditions one by one. Criteria has to be a string of the search keys that you are doing and are split between spaces to get all occurrences.

share|improve this answer
    
@Odle I updated my answer. –  Mez Jun 12 '13 at 22:51
    
That's a bad way to do it, and way too much extraneous stuff. The .Any's aren't needed because none of those properties are DbSets. Doing the foreach on the split is bad for performance and doesn't translate well to LINQ over EF, SQL, or XML. –  Robert McKee Jun 13 '13 at 2:27
    
Also since this is a LINQ to SQL question, you first .Where.ToList will retrieve all records from the database and then filter them instead of letting the SQL Server do the filtering server side. Way too many .ToLists(), and the .ToStrings() are useless as well, those properties are already strings. –  Robert McKee Jun 13 '13 at 2:38
up vote 0 down vote

If you're fine with the keyword requiring an exact match then this will work:

var allCriteria = criteria.Split(',');

db.Posts.Where(p => allCriteria.Contains(p.PostText) || allCriteria.Contains(p.Weather)  || allCriteria.Contains(p.Location)
share|improve this answer
    
This doesn't work when looking for one or more pieces within one of the properties. –  Robert McKee Jun 13 '13 at 2:29
    
sorry @Ek0nomik, that's never returning any results. For example when i search for "York Boston" it should return at least 2 posts (that I know are in there) with results that have location "New York" and "Boston" –  odle Jun 14 '13 at 22:37
up vote -2 down vote 
public IQueryable<Post> Search(string criteria, int x)
{
  var allCriteria = criteria.Split(',');

  var result=db.Posts.Where(p => 
      allCriteria.Any(c=>p.PostText.Contains(c))
      || allCriteria.Any(c=>p.Weather.Contains(c))
      || allCriteria.Any(c=>p.Location.Contains(c))
    ).Where(p=>p.IsActive)
    .OrderByDescending(p => p.PostDate)
    .Take(x);
  return result;
}
share|improve this answer
    
getting an error: System.NotSupportedException: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator. –  odle Jun 14 '13 at 22:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.