Simple search with Linq To SQL

Question

What is the problem with this query and how can I fix it?

public JsonResult Find(string q)
{
    var k = new List<string>(q.Split(' '));

    return Json(_dataContext.Jobs
        .OrderBy(p => new List<string>(p.Keywords.Split(' ')).Where(n => k.Contains(n)).Count())
        .Select(p => new { p.Title, p.IsFullTime, p.Location, p.Category, p.Url, p.Id }),
        JsonRequestBehavior.AllowGet);
 }

It throws:

Method 'System.String[] Split(Char[])' has no supported translation to SQL.

It's supposed to order the results by shared words between q and the Keywords for each row so the more you have shared words, you are ordered higher.

Thanks.

BTW: If it's possible to use Lucene.NET to improve this code, I'd happy to see a short example :)

Answer 1

.OrderBy(p => new List(p.Keywords.Split(' ')).

Well, the message is faily clear. String.Split() cannot be translated into SQL.

There is no really good way to do that in a single Linq-to-Sql statement. I'd suggest pulling the data out using L2S, put it into a List<>, and then sort them there.

    var jobs  = from p in _dataContext.Jobs
    select new 
      {
        p.Title,
        p.IsFullTIme,
        p.Location,
        p.Category,
        p.Url,
        p.Id,
        p.Keywords
      }

      return Json(job.ToList()
            .OrderBy(p=>p.Keywords.Split(' ').Where(n=>k.Contains(n)).Count()),
             JsonRequestBehavior.AllowGet);

However, your real problem is that you have a really bad design. Proper third-normal form would have a JobKeywords table (int JobId, varchar Keyword) with one row for each keyword for a job. Then you could do it in one sql statement:

 return Json(from p in _dataContext.Jobs     
             order by p.Keywords.Intersect(k).Count()
             select new { p.Title, p.IsFullTime, p.Location, 
                          p.Category, p.Url, p.Id },     
        JsonRequestBehavior.AllowGet);