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

He throws out:

The 'System.String [] Split (Char [])' method does not support SQL translation.

It is supposed to order the results using shared words between qand Keywordsfor each line, so the more common words you have, the more you order.

Thank.

BTW: If you can use Lucene.NET to improve this code, I would be glad to see a short example :)

Answer 1

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

, . String.Split() SQL.

Linq-to-Sql. L2S, < > , .

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

, . JobKeywords (int JobId, varchar Keyword) . sql:

 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

SQL-land # -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);
 }

, , .Take(n) , n .

Answer 3

You cannot use p.Keywords.Split(' '). LINQ-to-SQL does not support it. And why do you order the list anyway?