c# - Ranking Search Results With LINQ/.NET MVC

Question

I have a task where I need to rank the search results based on which column the search term was found.

So for example, if the search term is found in column A of table 1, it ranks higher than if it was found in column A of table 2.

Right now, I have a linq query that joins multiple tables and searches for the search term in certain columns. I.E.

var results = db.People
    .Join(db.Menu, p => p.ID, m => m.PersonID, (p, m) => new { p = p, m = m })
    .Join(db.Domain, m => m.m.DomainID, d => d.ID, (m, d) => new { m = m, d = d })
    .Where(d => searchTermArray.Any(x => d.m.p.p.Name.Contains(x)) || searchTermArray.Any(x => d.m.p.p.Biography.Contains(x)) || searchTermArray.Any(x => d.d.domain.Contains(x)))
    .Select(p => p).Distinct();

So if the search term is found in db.People, column Name, that row/Person will rank higher than if found in db.People, column Biography, which will rank higher than if found in db.Domain, column domain.

Answer 1

This will order your result by the "rank". You can manipulate the query further if you also want to return the rank and not only the aggregate:

var results = db.People
    .Join(db.Menu, p => p.ID, m => m.PersonID, (p, m) => new { p = p, m = m })
    .Join(db.Domain, m => m.m.DomainID, d => d.ID, (m, d) => new { m = m, d = d })
    .Select(d => new
            {
                rank = searchTermArray.Any(x => d.m.p.p.Name.Contains(x)) ? 3 : searchTermArray.Any(x => d.m.p.p.Biography.Contains(x)) ? 2 : searchTermArray.Any(x => d.d.domain.Contains(x)) ? 1 : 0,
                m = d
            })
    .Where(a => a.rank > 0)
    .OrderByDescending(a => a.rank)
    .Select(a => a.m).Distinct();

Note: I take no responsibility for poor performance, that's LINQ after all.