Linq to SQL for search query

c# entity-framework linq sql

Question

I need to do a simple search engine. I found this Microsoft example to help me start. Now, my problem is to write this function in linq to sql so I could use it with my dbcontext application.

/// <summary>
/// Search records from database.
/// </summary>
/// <param name="keywords">the list of keywords</param>
/// <returns>all found records</returns>
public List<Article> Search(List<string> keywords)
{
    // Generate a complex Sql command.
    StringBuilder sqlBuilder = new StringBuilder();
    sqlBuilder.Append("select * from [Articles] where ");
    foreach (string item in keywords)
    {
        sqlBuilder.AppendFormat("([Title] like '%{0}%' or [Content] like '%{0}%') and ", item);
    }

    // Remove unnecessary string " and " at the end of the command.
    string sql = sqlBuilder.ToString(0, sqlBuilder.Length - 5);

    return QueryList(sql);
}

I create my own function base on the previous:

public List<Record> Search(string[] keywords)
{
    var result = (from c in _context.Tenders
                    where keywords.Contains(c.Title) || keywords.Contains(c.Summary)
                    select new Record()
                    {
                        Id = c.Id,
                        Title = c.Title,
                        Content = c.Summary
                    }).ToList();

    return result;
}

But the search result is not the same. If I search for Contract in New contract in town I will not get the result. I will need to write the full sentence to get it. Could you help me to point what is missing in my linq to sql function to achieve that?

Thank you very much!!

1
1
1/2/2014 8:15:40 PM

Accepted Answer

You are close, the keywords.Contains(c.Title) is requesting an exact match to the Title. What you need to do is to flip it the other way around.

public List<Record> Search(string[] keywords)
{
    var result = (from c in _context.Tenders
                  where keywords.Any(w => c.Title.Contains(w)) || keywords.Any(w => c.Summary.Contains(w))
                  select new Record()
                  {
                      Id = c.Id,
                      Title = c.Title,
                      Content = c.Summary
                  }).ToList();

    return result;
}
2
12/19/2013 2:26:43 AM

Popular Answer

you can use condition as keywords.Any(w => c.Title.Contains(w)||c.Summary.Contains(w))

that will check Title or Summary contains any of the keyword

var result = (from c in _context.Tenders
             where keywords.Any(w => c.Title.Contains(w)||c.Summary.Contains(w))
             select new Record()
             {
                 Id = c.Id,
                 Title = c.Title,
                 Content = c.Summary
             }).ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow