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();
An exception is raised:
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?
Best Answer
Replace the usages of Any with Contains in your query. eg:
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.
The AddressLine1 part won't work this way- you'll have to loop-generate the comparisons yourself with
Something like PredicateBuilder can be helpful for this.