CODEWITHSUNDEEP
c#linqlinq-to-sql
odle
odle

Reputation: 5272

Multiple keyword database search using c# (linq to sql)

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

Upvotes: 3

Views: 3417

Answers (3)

Mez
Mez

Reputation: 4726

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.

Upvotes: 3

Robert McKee
Robert McKee

Reputation: 21487

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;
}

Upvotes: -2

Justin Helgerson
Justin Helgerson

Reputation: 25521

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)

Upvotes: 0

Related Questions