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

Answer 2

You can't use p.Keywords.Split(' '). LINQ-to-SQL doesn't support it. And why are you ordering by a list anyway?

Answer 3

You can retrieve all the data from SQL-land and do the string-splitting in C#-land:

public JsonResult Find(string q)
{
    var k = q.Split(' ');

    return Json(_dataContext.Jobs
        // Select all the columns we need, including Keywords
        // (still in SQL-land)
        .Select(p => new { p.Title, p.IsFullTime, p.Location, p.Category,
                           p.Url, p.Id, p.Keywords })
        // Move into C#-land
        .AsEnumerable()
        // Do the sorting here in C#-land
        .OrderBy(p => p.Keywords.Split(' ').Count(n => k.Contains(n)))
        // Finally, remove the Keywords column we no longer need
        .Select(p => new { p.Title, p.IsFullTime, p.Location, p.Category,
                           p.Url, p.Id }),
        JsonRequestBehavior.AllowGet);
 }

However, this is going to be slow because it is going to retrieve the entire Jobs table every time, even if you add a .Take(n) at the end to get only the top n entries.