views:

501

answers:

2

This is what i have now as a very basic 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 in a .Contains() or similar and put in keywords from a keyword box (split into individual items) and that should get the list of results.

However i need to order the results by basic relevance. Meaning that if record A contains 2 of the keywords (in the 'Body' nvarchar(MAX) field) it should be higher than record B that only matches against 1 of the keywords. I don't need a full count of every hit... however if thats eaiser to manage that would be fine.

So is there any way to get the hit count directly in as part of the orderby nicely? I can manage it by getting the results and parsing however i really don't want to do that as parsing possibly thousands could chug the IIS machine, while the SQL Server is a decently powerful cluster :)

If anyone has any ideas or tips it would be a big help.

+2  A: 

If I understand you correctly you want to call OrderyByDescending( p => p.Body ) but it should be ordered by how many times a certain word appreas in p.Body ?

Then you should be able to create a method that counts the occurrences and returns the count number then you can simply do OrderyByDescending( p => CountOccurences(p.Body) )

You can alternatively create a BodyComparer class that implements IComparer and then pass it to OrderByDescending

EDIT: take a look a this link Enable Full Text Searching

Stan R.
If i create a custom function to add in there then it will parse each record locally on the Web server instead of one single SQL query correct?
White Dragon
right. You are trying to accomplish this with SQL via a LINQ OrderBy method, am I understanding you now?
Stan R.
Aye :) all i can think of so far is perhaps a set of dynamic subqueries one each per keyword to get the count to orderby... which seems messy heh
White Dragon
@White Dragon, I've been muddling around with LINQ trying to get this to work. Take a look at the link I posted.
Stan R.
Cool will do, almost 2am so i better crash for now before i code into a hole heh. I appreciate you looking into this for me! It all helps.
White Dragon
no problem, this is a pretty neat question
Stan R.
A: 

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

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 ContainsAny extension:

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;
}
Yuriy Faktorovich