LINQ to Entities:涉及扩展方法的Orderby语句 [英] LINQ to Entities: Orderby statement involving extension method
问题描述
我正在为网站构建一个简单的搜索功能.目的是允许用户使用鲍勃法杖"之类的查询找到游泳者"并将其通过搜索框添加到列表中.
I'm constructing a simple search function for a site. The aim is to allow the user to find 'swimmers' and add them to a list via a search box, using a query like "bob staff".
我决定解决的第一部分是允许用户通过组名进行搜索(在数据库中,所有游泳者都是组的一部分).以下是我目前拥有的代码.
The first part of this I decided to tackle was allowing the user to search via a group name (In the database all swimmers are part of a group). The following is the code I have at the moment.
[HttpGet]
public JsonResult searchForSwimmers(string q)
{
//designed to return [{name='foo',id='bar'}]
String[] QueryTerms = q.Split(' '); //all the search terms are sep. by " "
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);
}
在第8行,调用了一个名为 StartsWithAny 的方法.这是我在以下文件中定义的扩展方法:
On line 8, there is a method invoked called StartsWithAny. This is an extension method I defined in the 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;
}
}
该想法是,如果名称以术语之一开头,则其相关性应排名更高.我认识到这种逻辑是幼稚的并且有缺陷,但是我认为这将是一个很好的例子来说明我所遇到的问题.但是,当在我的cshtml页面中使用以下命令调用searchForSimmers时,代码将进行编译:(使用tokenInput库)
The idea is that if a Name starts with one of the terms then it should be ranked higher in relevancy. I recognize that this logic is naïve and has flaws however I thought it would be a good example to illustrate the problem I've having. The code compiles however when searchForSimmers is invoked in my cshtml page with the following: (using the tokenInput library)
<script type="text/javascript">
$(document).ready(function () {
$("#demo-input-local").tokenInput("/Admin/searchForSwimmers");
});
</script>
我收到500个内部服务器错误.错误消息如下:
I get a 500 internal server error. The error message is as follows:
LINQ to Entities does not recognize the method 'Boolean ContainsAny(System.String, System.String[])' method, and this method cannot be translated into a store expression.
ContainsAny方法
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 a look around but couldn't find a solution to the problem. Any help would be greatly appreciated, cheers.
推荐答案
在我的博客中创建IQueryable的搜索扩展方法.
Check out my blog where I create a search extension method for IQueryable.
更新:我创建了一个新的博客文章,其中显示了实现您的目标所需的扩展方法
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
问题是linq不知道如何将代码转换为SQL.
The problem is linq does not know how to translate your code into SQL.
通过添加以下扩展方法:
By adding the 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 to 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 for 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);
}
}
这将允许您编写以下lambda:
This will allow you to write the following lambda:
[HttpGet]
public JsonResult searchForSwimmers(string q)
{
//designed to return [{name='foo',id='bar'}]
String[] QueryTerms = q.Split(' '); //all the search terms are sep. by " "
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);
}
这篇关于LINQ to Entities:涉及扩展方法的Orderby语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!