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.