c# - LINQ to Entities: Orderby statement involving extension method -
i'm constructing simple search function site. aim allow user find 'swimmers' , add them list via search box, using query "bob staff".
the first part of decided tackle allowing user search via group name (in database swimmers part of group). following code have @ moment.
[httpget] public jsonresult searchforswimmers(string q) { //designed return [{name='foo',id='bar'}] string[] queryterms = q.split(' '); //all search terms sep. " " var groupresults = _db.swimgroups.where(g => g.name.containsany(queryterms)) .orderbydescending(g => g.name.startswithany(queryterms) ? 1 : 0) .thenby( g => g) .select(g => new { name = g.name, id = g.id }); return json(groupresults,jsonrequestbehavior.allowget); } on line 8, there method invoked called startswithany. extension method defined in following file:
public static class stringutils { public static boolean startswithany(this string str, params string[] fragments) { foreach (string fragment in fragments) { if (str.startswith(fragment)) { return true; } } return false; } } the idea if name starts 1 of terms should ranked higher in relevancy. recognize logic naïve , has flaws thought example illustrate problem i've having. code compiles when searchforsimmers invoked in cshtml page following: (using tokeninput library)
<script type="text/javascript"> $(document).ready(function () { $("#demo-input-local").tokeninput("/admin/searchforswimmers"); }); </script> i 500 internal server error. error message follows:
linq entities not recognize method 'boolean containsany(system.string, system.string[])' method, , method cannot translated store expression. the containsany method
public static boolean containsany(this string str, list<string> fragments) { foreach (string fragment in fragments) { if(str.contains(fragment)) { return true; } } return false; } i've had around couldn't find solution problem. appreciated, cheers.
check out blog create search extension method iqueryable.
updated: have created new blog post showing extension method required acheive goal
http://www.ninjanye.co.uk/2013/04/generic-iqueryable-or-search-for.html
http://jnye.co/posts/8/generic-iqueryable-or-search-for-multiple-search-terms-using-expression-trees
the problem linq not know how translate code sql.
by adding following extension method:
public static class queryableextensions { public static iqueryable<t> search<t>(this iqueryable<t> source, expression<func<t, string>> stringproperty, params string[] searchterms) { if (!searchterms.any()) { return source; } expression orexpression = null; foreach (var searchterm in searchterms) { //create expression represent x.[property].contains(searchterm) var searchtermexpression = expression.constant(searchterm); var containsexpression = buildcontainsexpression(stringproperty, searchtermexpression); orexpression = buildorexpression(orexpression, containsexpression); } var completeexpression = expression.lambda<func<t, bool>>(orexpression, stringproperty.parameters); return source.where(completeexpression); } private static expression buildorexpression(expression existingexpression, expression expressiontoadd) { if (existingexpression == null) { return expressiontoadd; } //build 'or' expression each property return expression.orelse(existingexpression, expressiontoadd); } private static methodcallexpression buildcontainsexpression<t>(expression<func<t, string>> stringproperty, constantexpression searchtermexpression) { return expression.call(stringproperty.body, typeof(string).getmethod("contains"), searchtermexpression); } } this allow write following lambda:
[httpget] public jsonresult searchforswimmers(string q) { //designed return [{name='foo',id='bar'}] string[] queryterms = q.split(' '); //all search terms sep. " " var groupresults = _db.swimgroups.search(g => g.name, queryterms) .orderbydescending(g => g.name.startswithany(queryterms) ? 1 : 0) .thenby( g => g) .select(g => new { name = g.name, id = g.id }); return json(groupresults,jsonrequestbehavior.allowget); } 
