Search - Order By Keywords

Question

Is there any way to do something like this:

var keywords = SearchUtilities.FindKeyWords(q);

var j = (from p in _dataContext.Jobs
        orderby p.JobKeywords.Select(jobKeyword => jobKeyword.Keyword)
            .Intersect(keywords).Count())
            .Take(10).AsEnumerable();

The main idea here is to order search results by the count of the keywords that exists both in the search query and in the keywords that associated with the jobs.

I don't want to bring all the records from the SQL-land first, and then orderby, because it's very slow. When I try that code it throws:

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

Ideas?

Answer 1

If you are looking more performance on search, I suggest you to use Stored Procedure. In my opinion TSQL works faster than LinQ. because Linq gets all results, but Stored Procedure (TSql) doesn't take all records.