Linq to order by number of matches

I have an array of strings, and a Linq query:

/// <summary>
/// Return all the search results
/// </summary>
public static GenericTagEntity[] getSearchResults(int SectionID, string[] Words)
{
    GenericTagEntity[] Recs;

    using (MainContext db = new MainContext())
    {
        var q = (
            from c in db.tblArticles
            join a in db.tblForumAuthors on c.AuthorID equals a.Author_ID
            join u in db.tblProfiles on c.AuthorID equals u.UserID
            where c.IsDeleted == false
            && c.SectionID == SectionID
            select new
            {
                c.Title,
                c.ID,
                c.Anchor,
                c.Date,
                c.Description,
                u.EmailAddress,
                u.UserID,
                a.Username,
                c.Body,
    开发者_Go百科            comments = (from f in db.tblComments where f.IsDeleted == false && f.Anchor == c.Anchor select new { f.ID }).Count()
            });

What I would like to do is modify the where so that it returns results where c.Title OR c.Body contain one or more of the words. I then need it ordered by total matches (most relevant first)!

This seems really difficult to me, any help is greatly appreciated.

I found this but it's only partially helpful, also a SO search didn't yield many results. http://msdn.microsoft.com/en-us/library/bb546166.aspx#Y200

Sad to break it to you but you're using wrong tool to solve this problem. Either create a full text index in db and invoke full text search from LINQ via a custom function or use 3rd party full text search system line Lucene.

If you really want to do it in sql then counting no. of instances of a word in a document would require you to change your db schema.

You need a words table (id, word) and a occurences table (wordid, articleid) and then you can do a query on the occurences table to get the results you want.

I believe this will do the trick

var occurrences = from e in q.Title.Split(new char[] { ' ' }).Concat(q.Body.Split(new char[] { ' ' }))
             join word in Words on e equals word
             group e by e into g
             select new {element = g, count = g.Count()} into result
             orderby result.count descending
             select result;

The query will produce a list of words that are either in the title or body ordered descending by number of occurrences.