Search query gives LINQ to SQL exception

For the following query I get a LINQ to SQL exception.

var terms = "bob town".Split(' ');
var q = from m in db.Monument
    where terms.All(t => new开发者_StackOverflow List<string>() {
        m.Name,
        m.Street,
        m.Owner }.Any(
            p => p.Contains(t)))
    select m;

The exception is:

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

How can I modify the query to compatible with LINQ to SQL?

---

Goal

The goal of the query is this. I have a list of search terms and a database with objects. An object should be returned if all search terms are substrings of at least one property.

For example. If there is an object o with o.name="creek mill" and the o.street="St. Petersroad", then a search on "mill petersroad" should return this object, but a search on "mill foobar" should not.

---

So, the actual problem is that Linq-To-SQL doesen't know how to convert the Terms part of your Linq to a valid SQL statement. So, you have to rejig the query to help it out.

I assume that we would like to use the Contains function to get a SQL statement that uses the SQL IN operator. Here is what I suggest.

var terms = "bob town".Split(' ');
var q = from m in db.Monument
    where 
            terms.Contains(m.Name)
        ||
            terms.Contains(m.Street)
        ||
            terms.Contains(m.Owner)
    select m;

I haven't tested this but it looks like it should work and should be converted by Linq-To-SQL.

---

I'm sure there is probably a way to do this with pure LINQ, but I am not so sure if it is actually a good idea: IMHO opinion people sometimes tend to overcook their LINQ queries for no particular reason other than LINQ being very cool and all, ending up with queries that are very hard to understand by just looking at them.

Why don't you implement an extension method that takes Monument entities and figures out if a certain string[] matches your criteria?

That way your LINQ expression couldn't get simpler:

var q = from m in db.Monument
where m.ContainsAllSearchTerms(terms)
select m; //readable and anyone understands right away what is going on here

Being ContainsAllSearchTerms(this Monument m, string[] terms) the relevant extension method.

---

what about the below. I haven't tried it but it gives you an idea about

var terms = "bob town".Split(' ');
        var q = from m in db.Monument
                where m.Name.Split(' ').Intersect(terms).Count() > 0 ||
                        m.Street.Split(' ').Intersect(terms).Count() > 0
                select m; 

Anyway I think for this kind of search you should think about using SQL Full-Text-search if you got a SQL db or ".net lucene" which fits really well in your context