LINQ Keyword search using orderby relevance based on count (LINQ to SQL)

Question

This is what I now have as a very simple search:

var Results = from p in dx.Listings select p; if (CategoryId > 0) Results = Results.Where(p => p.CategoryId == CategoryId); if (SuburbId > 0) Results = Results.Where(p => p.SuburbId == SuburbId); var OrderedResults = Results.OrderByDescending(p => p.ListingType); OrderedResults = OrderedResults.ThenByDescending(p => p.Created); 

I understand that I can add to .Contains () or the like and put the keywords from the keywords window (divided into separate elements), and this should get a list of results.

However, I need to order the results by primary relevance. This means that if record A contains 2 keywords (in the body field nvarchar (MAX)), it must be higher than record B, which corresponds to only one of the keywords. I don’t need a full count of each hit ... however, if this helps the eiser, it will be fine.

So, is there a way to get the number of hits directly in the order part, beautifully? I can manage it by getting the results and parsing, but I really don't want to do this, since parsing maybe thousands can crash the IIS machine, while SQL Server is a decently powerful cluster :)

If anyone has any ideas or advice, this will be a big help.

Answer 1

If you correctly understood that you want to call OrderyByDescending( p => p.Body ) , but you need to order it, how many times does a word use in p.Body?

Then you should be able to create a method that takes into account the entries and returns the account number, then you can just do OrderyByDescending( p => CountOccurences(p.Body) )

You can also create a BodyComparer class that implements IComparer , and then pass it OrderByDescending

EDIT: see the Enable Full Text Search link

Answer 2

Here is a simple example, if I understand correctly what you are looking for:

 var storedData = new[]{ new int[] {1, 2, 3, 4}, new int[] {1, 2, 3, 4, 5} }; var itemsFromTextBox = new[] { 3, 4, 5 }; var query = storedData.Where(a => a.ContainsAny(itemsFromTextBox)) .OrderByDescending(a => itemsFromTextBox.Sum(i => a.Contains(i)? 1:0)); 

With the following Extension ContainsAny:

 public static bool ContainsAny<T>(this IEnumerable<T> e1, IEnumerable<T> e2) { foreach (var item in e2) { if (e1.Contains(item)) return true; } return false; }