LINQ To SQL exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains operator

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?

Replace the usages of Any with Contains in your query. eg:

searchTerms.Contains(c.Email)

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

c.addressLine1.Contains(...)

Something like PredicateBuilder can be helpful for this.

Just a thought (not directly related to the question, but it might help other viewers):

I was getting the same error message as you, even though I was using the Contains() method correctly, and it took me quite a while to figure out that the root of my problem was returning an IEnumerable to something that needed to further filter the results of the L2S query. Once I changed the function's return type to an IQueryable, the problem went away. It makes sense, seeing that an IEnumerable can't be further filtered, but an IQueryable can.

I was getting the same error, but none of the solutions posted above worked for me.

What worked for me was to cast db.Custs into a list first, as so:

List<Cust> =db.Custs.ToList<Cust>.Where(...

I have no idea why it worked, but it did.

Essentially, the error is saying that you are doing a join between two collections, one of which is a database table, and the other is an array. LINQ isn't designed to handle that -- either both need to be in the database, or both in-memory (with a special case exception made when using Contains)