考虑此LINQ To SQL查询.目的是采用字符串[]作为搜索字词,并将这些字词应用于SQL表上的一堆不同字段:
Consider this LINQ To SQL query. It's intention is to take a string[] of search terms and apply the terms to a bunch of different fields on the SQL table:
string[] searchTerms = new string[] {"hello","world","foo"}; List<Cust> = db.Custs.Where(c => searchTerms.Any(st => st.Equals(c.Email)) || searchTerms.Any(st => st.Equals(c.FirstName)) || searchTerms.Any(st => st.Equals(c.LastName)) || searchTerms.Any(st => st.Equals(c.City)) || searchTerms.Any(st => st.Equals(c.Postal)) || searchTerms.Any(st => st.Equals(c.Phone)) || searchTerms.Any(st => c.AddressLine1.Contains(st)) ) .ToList();引发异常:
除Contains()运算符外,不能在LINQ to SQL实现中使用本地序列
Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator
问题: 为什么会引发此异常,以及如何重写查询以避免此异常?
Question: Why is this exception raised, and how can the query be rewritten to avoid this exception?
推荐答案在查询中替换Any的用法.例如:
Replace the usages of Any with Contains in your query. eg:
searchTerms.Contains(c.Email)这应该得到您想要的结果.它向后看,但它是正确的-它将为包含搜索条件中所有元素的Contains内的每个字段生成一个IN运算符.
This should get the result you're looking for. It looks backwards, but it's correct- it'll generate an IN operator for each field inside a Contains with all the elements in searchTerms.
AddressLine1部分无法按这种方式工作-您必须自己用
The AddressLine1 part won't work this way- you'll have to loop-generate the comparisons yourself with
c.addressLine1.Contains(...)诸如 PredicateBuilder 之类的东西可能对此有所帮助.
Something like PredicateBuilder can be helpful for this.
更多推荐
LINQ To SQL异常:不能在LINQ to SQL实现的查询运算符中使用本地序列,但Contains运算符除外
发布评论