c# - LINQ to SQL, how can I get a count of the search terms found in a field(s)? -

is there way write query in linq return count of search terms found in field(s)

basically, want work:

var matches = t in _db.books                           let score = getscore(t, searchterms)                           score >= 1                           orderby score descending                           select t;  public static int getscore(book b, params string[] searchterms)     {         int count = 0;         foreach (string term in searchterms)         {             if (b.title.contains(term))                 count++;         }         return count;     } 

but, of course, can't work. can little getscore function translated linq?

thanks.

edit: prefer have score accessible. ideally selecting results searchresults class (for view) contain book info , book's score query. update query, it'd this:

var matches = t in _db.books                           let score = getscore(t, searchterms)                           score >= 1                           orderby score descending                           select new searchresult                                                 {                                                     title = t.title,                                                     type = "book",                                                     link = "books/details/" + t.bookid,                                                     score = score                                                 }; 

i'm sorry wasn't more clear originally.

you can't want without issuing multiple queries database - 1 per search term. if happy that, here easy way it:

var terms = new [] { "s", "t", "r", "e", "b", "c", };  var ids =     term in terms     id in _db.books         .where(book => book.title.contains(term))         .select(book => book.id)     group term id gts     orderby gts.count() descending     select gts.key;  var selectedids = ids.take(50).toarray();  var query =     book in _db.books     selectedids.contains(book.id)     select book; 

i wrote ids return list of ids sorted match terms first. closely same kind of result wanted in question. decided use take(50) top 50 results. can change strategy suit needs, must end array of ids use in final query.

i hope helps.

---

edit: based on op's edit.

here's how query score included:

var terms = new [] { "s", "t", "r", "e", "b", "c", "l", "i", };  var idscores =     term in terms     id in _db.books         .where(book => book.title.contains(term))         .select(book => book.bookid)     group term id gts     select new     {         id = gts.key,         score = gts.count(),     };  var selectedids = idscores.select(x => x.id).take(50).toarray();  var selectedbooks =     book in _db.books     selectedids.contains(book.bookid)     select book;  var query =     b in selectedbooks.toarray()     join x in idscores on b.bookid equals x.id     orderby x.score descending     select new     {         title = b.title,         type = "book",         link = "books/details/" + b.bookid,         score = x.score,     };