Order By Count in LINQ Query MVC

Question

Hello,

 

I was wondering if someone could please help me?

 

I have a list of keywords (entered by the user at run-time) that I need to search for within a joined table in a database (tblAppKeywords) but what I also want to do is to count the number of times the keyword occurs for each applicant code (an applicant code may be assigned against any number of keywords). I then need to order by the count result so that they applicants who match all keywords specified will be listed first and so on.

 

//Split multiple keywords entered by user and store each keyword in a list

string[] keywords = Applicant.ApplicantKeyword.Split(new Char[] { ',', '.', '-', '\n', '\t' });

List<string> keywordlist = new List<string>();

foreach (string keyword in keywords)

{

keywordlist.Add(CultureInfo.InvariantCulture.TextInfo.ToTitleCase(keyword));

}

//I then need to search the joined tblAppKeywords table for the matching keywords and order by the count

applicants = (from c in context.tblapplicants where c.Forename != null join e in context.tblapplicant_notes on c.ApplicantCode equals e.ApplicantCode join f in context.tblAppKeywords on c.ApplicantCode equals f.ApplicantCode where e.NoteDate.Value.Year >= 2015 orderby e.NoteDate descending select c).ToList();