更新:现在工作 我能够最终得到它完成。一个工作 - 例如,详见下面的答案(我将能够标记过2天)。
在过去的3天,我一直在尝试在的 DBML的DataContext 使用code样品questions在此处发布 和 其它来源 以及.. 没有工作过的!
由于以下原因,我开始怀疑,如果框架3.5中使用该甚至有可能:
predicate生成器 注意事项框架4.0在其网站上。 一些answers这里 谈论的equivolent调用
在4.0版本(所以我的部分的希望在这里)。
...我可以继续下去,但你的想法。
我真的很茫然,似乎是抓在串......我需要对如何处理这方面的一些中肯的意见。
原始版本有SOME成功但只有当:
唯一一次我有一个端倪成功的数据来自式(所有6178行的话),但没有 WHERE子句
施。这是证明了的没有的任何 WHERE子句
施入 SQL
发现的 dataContext.GetCommand(查询).CommandText
。
其他版本#1失败: 而产生这个错误:方法'System.Object的DynamicInvoke(System.Object的[])没有支持转换为SQL
//版本1:
公共静态类predicateBuilder
{
公共静态防爆pression< Func键< T,布尔>>真正的< T>(){返回F =>真正; }
公共静态防爆pression< Func键< T,布尔>>假< T>(){返回F =>假; }
公共静态防爆pression< Func键< T,布尔>>或LT; T>(此例pression< Func键< T,布尔>>表达式1,防爆pression< Func键< T,布尔>>表达式2)
{
VAR invokedExpr =前pression.Invoke(表达式2,expr1.Parameters.Cast<防爆pression>());
返回前pression.Lambda< Func键< T,布尔>>(出pression.OrElse(expr1.Body,invokedExpr),expr1.Parameters);
}
公共静态防爆pression< Func键< T,布尔>>和< T>(此例pression< Func键< T,布尔>>表达式1,防爆pression< Func键< T,布尔>>表达式2)
{
VAR invokedExpr =前pression.Invoke(表达式2,expr1.Parameters.Cast<防爆pression>());
返回前pression.Lambda< Func键< T,布尔>>(出pression.AndAlso(expr1.Body,invokedExpr),expr1.Parameters);
}
公共静态防爆pression< Func键< T,布尔>>弦乐器< T>(出pression< Func键< T,字符串>>选择器,字符串模式)
{
VAR predicate = predicateBuilder.True< T>();
VAR部分= pattern.Split('%');
如果(parts.Length == 1)//不是'%'的标志
{
predicate = predicate.And(S => selector.Compile()(多个)==图案);
}
其他
{
的for(int i = 0; I< parts.Length;我++)
{
串P =零件[I]
如果(p.Length大于0)
{
如果(我== 0)
{
predicate = predicate.And(S => selector.Compile()(多个).StartsWith(对));
}
否则,如果(我== parts.Length - 1)
{
predicate = predicate.And(S => selector.Compile()(多个).EndsWith(对));
}
其他
{
predicate = predicate.And(S => selector.Compile()(多个)。载(对));
}
}
}
}
返回predicate;
}
}
//版本1:
公开名单< QuickFindResult> QueryDocuments(字符串searchText,串customerSiteId,列表和LT; INT> filterIds)
{
VAR那里= predicateBuilder.True< vw_QuickFindResult>();
VAR搜索=新的名单,其中,字符串>(searchText.Split(''));
searches.ForEach(产品名称=>
{
串状= productName.Replace('','%')
。更换('*', '%');
其中,= predicateBuilder.StringLike< vw_QuickFindResult>(X => x.DocumentName,等等);
});
VAR的结果= DocumentCollectionService.ListQuickFind(其中,NULL);
//这里做其他的东西...
返回结果;
}
//版本1:
公共静态列表< vw_QuickFindResult> ListQuickFind(前pression< Func键< vw_QuickFindResult,布尔>>其中,前pression< Func键< vw_QuickFindResult,布尔>>的OrderBy)
{
VAR的connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME);
名单< vw_QuickFindResult>结果= NULL;
使用(HostingEnvironment.Impersonate())
{
使用(VAR的DataContext =新ES_DocumentsDataContext(的connectionString))
{
IQueryable的< vw_QuickFindResult>查询= dataContext.vw_QuickFindResults;
查询= query.Where(其中);
结果= query.ToList();
}
}
返回结果;
}
其他版本#2失败: 而产生这个错误:方法布尔赞(System.String,System.String)不能在客户端上使用;它仅用于转换为SQL
//版本2:
公开名单< QuickFindResult> QueryDocuments(字符串searchText,串customerSiteId,列表和LT; INT> filterIds)
{
FUNC< vw_QuickFindResult,布尔>其中,= NULL;
FUNC<字符串函数功能:LT; vw_QuickFindResult,布尔>> buildKeyword predicate =样=> X => SqlMethods.Like(x.DocumentName,等等);
FUNC< Func键< vw_QuickFindResult,布尔>中Func键< vw_QuickFindResult,布尔>中Func键< vw_QuickFindResult,布尔>> buildOr predicate =(preD1,preD2)=> X => preD1(X)|| preD2(X);
//建立一个像子句的WHERE
VAR搜索=新的名单,其中,字符串>(searchText.Split(''));
searches.ForEach(产品名称=>
{
串状= productName.Replace('','%')
。更换('*', '%');
其中,=(其中== NULL)? buildKeyword predicate(像):buildOr predicate(其中,buildKeyword predicate(像));
});
VAR的结果= DocumentCollectionService.ListQuickFind(其中,NULL);
//这里做其他的东西...
返回结果;
}
//第2版:
公共静态列表< vw_QuickFindResult> ListQuickFind(前pression< Func键< vw_QuickFindResult,布尔>>其中,前pression< Func键< vw_QuickFindResult,布尔>>的OrderBy)
{
VAR的connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME);
名单< vw_QuickFindResult>结果= NULL;
使用(HostingEnvironment.Impersonate())
{
使用(VAR的DataContext =新ES_DocumentsDataContext(的connectionString))
{
变种查询= dataContext.vw_QuickFindResults.AsEnumerable();
查询= query.Where(其中);
结果= query.ToList();
}
}
返回结果;
}
解决方案
这是正确的ASWER
这里是工作版本对于那些谁需要它。这个问题是一个事物的组合。其中第一项是以下行设置为真
:
变种,其中= predicateBuilder.True< vw_QuickFindResult>();
这应该是假
...
变种,其中= predicateBuilder.False< vw_QuickFindResult>();
我不知道为什么...但还需要其他的变化也。
公共静态类predicateBuilder
{
公共静态防爆pression< Func键< T,布尔>>真正的< T>(){返回F =>真正; }
公共静态防爆pression< Func键< T,布尔>>假< T>(){返回F =>假; }
公共静态防爆pression< Func键< T,布尔>>或LT; T>(此例pression< Func键< T,布尔>>表达式1,防爆pression< Func键< T,布尔>>表达式2)
{
VAR invokedExpr =前pression.Invoke(表达式2,expr1.Parameters.Cast<防爆pression>());
返回前pression.Lambda< Func键< T,布尔>>(出pression.OrElse(expr1.Body,invokedExpr),expr1.Parameters);
}
公共静态防爆pression< Func键< T,布尔>>和< T>(此例pression< Func键< T,布尔>>表达式1,防爆pression< Func键< T,布尔>>表达式2)
{
VAR invokedExpr =前pression.Invoke(表达式2,expr1.Parameters.Cast<防爆pression>());
返回前pression.Lambda< Func键< T,布尔>>(出pression.AndAlso(expr1.Body,invokedExpr),expr1.Parameters);
}
}
公开名单< QuickFindResult> QueryDocuments(字符串searchText,串customerSiteId,列表和LT; INT> filterIds)
{
VAR通配符=新的String [] {*,\};
VAR那里= predicateBuilder.False< vw_QuickFindResult>();
VAR搜索=新的名单,其中,字符串>(searchText.Split('')); // TODO:< - 如果需要更复杂的搜索,我们将不得不使用RegularEx pressions
//搜索文本 - WHERE子句
searches.ForEach(产品名称=>
{
布尔hasWildCards =(productName.IndexOfAny(新的char [] {'','*'})!= -1);
如果(hasWildCards)
{
的Int32长度= productName.Length;
如果(长度大于1)
{
串像= productName.Replace(%,)
。更换(*, );
串第一= productName.Substring(0,1);
字符串最后= productName.Substring(长度 - 1);
//包含
如果(wildCards.Contains(第一)及和放大器; wildCards.Contains(最后一个))
其中,= where.Or(P => p.DocumentName.Contains(像)||
p.DocumentTitle.Contains(象));
//的endsWith
否则,如果(wildCards.Contains(第一))
其中,= where.Or(P => p.DocumentName.EndsWith(像)||
p.DocumentTitle.EndsWith(象));
// StartsWith
否则,如果(wildCards.Contains(最后一个))
其中,= where.Or(P => p.DocumentName.StartsWith(像)||
p.DocumentTitle.StartsWith(象));
//含有(默认)
其他
其中,= where.Or(P => p.DocumentName.Contains(像)||
p.DocumentTitle.Contains(象));
}
否则//只能执行包含
其中,= where.Or(P => p.DocumentName.Contains(产品名称)||
p.DocumentTitle.Contains(产品名称));
}
否则//只能执行包含
其中,= where.Or(P => p.DocumentName.Contains(产品名称)||
p.DocumentTitle.Contains(产品名称));
});
//过滤ID - WHERE子句
变种滤波器= GetAllFilters()其中(x => filterIds.Contains(x.Id))。了ToList()。
filters.ForEach(过滤=>
{
如果(!filter.IsSection)
其中,= where.And(X => x.FilterName == filter.Name);
});
VAR数据源= DocumentCollectionService.ListQuickFind(其中);
VAR收集=新的名单,其中,QuickFindResult>();
//其他不相关的事情发生在这里...
返回集合;
}
公共静态列表< vw_QuickFindResult> ListQuickFind(前pression< Func键< vw_QuickFindResult,布尔>>凡)
{
VAR的connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME);
名单< vw_QuickFindResult>结果= NULL;
使用(HostingEnvironment.Impersonate())
{
使用(VAR的DataContext =新ES_DocumentsDataContext(的connectionString))
{
VAR的查询= dataContext.vw_QuickFindResults.Where(在哪里).OrderBy(X => x.DocumentName).OrderBy(X => x.DocumentTitle);
结果= query.ToList();
}
}
返回结果;
}
UPDATE: It Is Now Working I was able to finally get it completed. A working-example is detailed in an answer below (which I will be able to mark-off in 2 days).
For the last 3 days, I have been trying to build a dynamic-where-clause on a DBML DataContext using code samples from questions posted here and from other sources as well...none have worked!
For the reasons below, I am beginning to wonder if this is even POSSIBLE using under Framework 3.5:
Predicate Builder notes Framework 4.0 on their site. Some answers here talk about an equivolentInvoke
versions in 4.0 (so I have some hope here).
...I could go on but you get the idea.
I am really at a loss and seem to be "grabbing at strings"...and I need some sound advice on how to approach this.
Original Version Had SOME Success But Only When:
The ONLY time I had a 'inkling' of success the data came-up (all 6178 rows of it) but no WHERE CLAUSE
was applied. This was evidenced by the lack of any WHERE CLAUSE
applied into the SQL
found in the dataContext.GetCommand(query).CommandText
.
Other Version #1 Fails: And generates this error: "Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL."
// VERSION 1:
public static class PredicateBuilder
{
public static Expression<Func<T, bool>> True<T>() { return f => true; }
public static Expression<Func<T, bool>> False<T>() { return f => false; }
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>(Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Func<T, bool>> StringLike<T>(Expression<Func<T, string>> selector, string pattern)
{
var predicate = PredicateBuilder.True<T>();
var parts = pattern.Split('%');
if (parts.Length == 1) // not '%' sign
{
predicate = predicate.And(s => selector.Compile()(s) == pattern);
}
else
{
for (int i = 0; i < parts.Length; i++)
{
string p = parts[i];
if (p.Length > 0)
{
if (i == 0)
{
predicate = predicate.And(s => selector.Compile()(s).StartsWith(p));
}
else if (i == parts.Length - 1)
{
predicate = predicate.And(s => selector.Compile()(s).EndsWith(p));
}
else
{
predicate = predicate.And(s => selector.Compile()(s).Contains(p));
}
}
}
}
return predicate;
}
}
// VERSION 1:
public List<QuickFindResult> QueryDocuments(string searchText, string customerSiteId, List<int> filterIds)
{
var where = PredicateBuilder.True<vw_QuickFindResult>();
var searches = new List<String>(searchText.Split(' '));
searches.ForEach(productName =>
{
string like = productName.Replace('"', '%')
.Replace('*', '%');
where = PredicateBuilder.StringLike<vw_QuickFindResult>(x => x.DocumentName, like);
});
var results = DocumentCollectionService.ListQuickFind(where, null);
// Do other stuff here...
return results;
}
// VERSION 1:
public static List<vw_QuickFindResult> ListQuickFind(Expression<Func<vw_QuickFindResult, bool>> where, Expression<Func<vw_QuickFindResult, bool>> orderBy)
{
var connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME);
List<vw_QuickFindResult> results = null;
using (HostingEnvironment.Impersonate())
{
using (var dataContext = new ES_DocumentsDataContext(connectionString))
{
IQueryable<vw_QuickFindResult> query = dataContext.vw_QuickFindResults;
query = query.Where(where);
results = query.ToList();
}
}
return results;
}
Other Version #2 Fails: And generates this error: "Method 'Boolean Like(System.String, System.String)' cannot be used on the client; it is only for translation to SQL."
// VERSION 2:
public List<QuickFindResult> QueryDocuments(string searchText, string customerSiteId, List<int> filterIds)
{
Func<vw_QuickFindResult, bool> where = null;
Func<string, Func<vw_QuickFindResult, bool>> buildKeywordPredicate = like => x => SqlMethods.Like(x.DocumentName, like);
Func<Func<vw_QuickFindResult, bool>, Func<vw_QuickFindResult, bool>, Func<vw_QuickFindResult, bool>> buildOrPredicate = (pred1, pred2) => x => pred1(x) || pred2(x);
// Build LIKE Clause for the WHERE
var searches = new List<String>(searchText.Split(' '));
searches.ForEach(productName =>
{
string like = productName.Replace('"', '%')
.Replace('*', '%');
where = (where == null) ? buildKeywordPredicate(like) : buildOrPredicate(where, buildKeywordPredicate(like));
});
var results = DocumentCollectionService.ListQuickFind(where, null);
// Do other stuff here...
return results;
}
// VERSION 2:
public static List<vw_QuickFindResult> ListQuickFind(Expression<Func<vw_QuickFindResult, bool>> where, Expression<Func<vw_QuickFindResult, bool>> orderBy)
{
var connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME);
List<vw_QuickFindResult> results = null;
using (HostingEnvironment.Impersonate())
{
using (var dataContext = new ES_DocumentsDataContext(connectionString))
{
var query = dataContext.vw_QuickFindResults.AsEnumerable();
query = query.Where(where);
results = query.ToList();
}
}
return results;
}
解决方案
THIS IS THE CORRECT ASWER
Here is the working version for those who need it. The issue was a COMBINATION of things. The first of which was the following line was set to True
:
var where = PredicateBuilder.True<vw_QuickFindResult>();
It should be False
...
var where = PredicateBuilder.False<vw_QuickFindResult>();
I don't know why...but other changes were needed also.
public static class PredicateBuilder
{
public static Expression<Func<T, bool>> True<T>() { return f => true; }
public static Expression<Func<T, bool>> False<T>() { return f => false; }
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>(Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
}
}
public List<QuickFindResult> QueryDocuments(string searchText, string customerSiteId, List<int> filterIds)
{
var wildCards = new string[] { "*", "\"" };
var where = PredicateBuilder.False<vw_QuickFindResult>();
var searches = new List<String>(searchText.Split(' ')); // TODO: <-- If more complex searches are needed we'll have to use RegularExpressions
// SEARCH TEXT - WHERE Clause
searches.ForEach(productName =>
{
Boolean hasWildCards = (productName.IndexOfAny(new char[] { '"', '*' }) != -1);
if (hasWildCards)
{
Int32 length = productName.Length;
if (length > 1)
{
string like = productName.Replace("%", "")
.Replace("*", "");
string first = productName.Substring(0, 1);
string last = productName.Substring(length - 1);
// Contains
if (wildCards.Contains(first) && wildCards.Contains(last))
where = where.Or(p => p.DocumentName.Contains(like) ||
p.DocumentTitle.Contains(like));
// EndsWith
else if (wildCards.Contains(first))
where = where.Or(p => p.DocumentName.EndsWith(like) ||
p.DocumentTitle.EndsWith(like));
// StartsWith
else if (wildCards.Contains(last))
where = where.Or(p => p.DocumentName.StartsWith(like) ||
p.DocumentTitle.StartsWith(like));
// Contains (default)
else
where = where.Or(p => p.DocumentName.Contains(like) ||
p.DocumentTitle.Contains(like));
}
else // Can only perform a "contains"
where = where.Or(p => p.DocumentName.Contains(productName) ||
p.DocumentTitle.Contains(productName));
}
else // Can only perform a "contains"
where = where.Or(p => p.DocumentName.Contains(productName) ||
p.DocumentTitle.Contains(productName));
});
// FILTER IDS - WHERE Clause
var filters = GetAllFilters().Where(x => filterIds.Contains(x.Id)).ToList();
filters.ForEach(filter =>
{
if (!filter.IsSection)
where = where.And(x => x.FilterName == filter.Name);
});
var dataSource = DocumentCollectionService.ListQuickFind(where);
var collection = new List<QuickFindResult>();
// Other UNRELATED stuff happens here...
return collection;
}
public static List<vw_QuickFindResult> ListQuickFind(Expression<Func<vw_QuickFindResult, bool>> where)
{
var connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME);
List<vw_QuickFindResult> results = null;
using (HostingEnvironment.Impersonate())
{
using (var dataContext = new ES_DocumentsDataContext(connectionString))
{
var query = dataContext.vw_QuickFindResults.Where(where).OrderBy(x => x.DocumentName).OrderBy(x => x.DocumentTitle);
results = query.ToList();
}
}
return results;
}