Join columns in LINQ and run contains operator

Question

I'm trying to create a search method where I'm wanting to check whether the keywords is contained within a any of number of different columns for a given record.

My Linq statement is as follows:

string[] searchFilter = {"john", "iceberg"};


var q = from qua in qual
                    join del in deliverables on qua.ID equals del.Q_ID
                    where searchFilter.All(s => (qua.Name + " " + qua.Project + " " + qua.Summary + " " + del.Name + " " + del.Summary).ToLower().Contains(s))
                    select qua;

I'm however getting an error message which states: "Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator."

Answer 1

Indeed you can't use the All method but just the Contains method:

var q = from qua in qual
        join del in deliverables on qua.ID equals del.Q_ID
        where searchFilter.Contains(qua.Name) ||  searchFilter.Contains(qua.Project)...
        select qua;

Of course you can combine your linq query dynamically with expressions (but it could be much more work), or use dynamic linq.

Answer 2

Don't think you have choice :

But as you stay in a "Queryable" world, this will generate only one sql request (ugly, but...)

var q = from qua in qual
        join del in deliverables on qua.ID equals del.Q_ID
        select new{ qua, del};

foreach (var filter in searchFilter)
     q = q.Where(v => (v.qua.Name + " " + v.qua.Project + " " + v.qua.Summary + " " + v.del.Name + " " + v.del.Summary).ToLower().Contains(filter));

var result = q.Select(p => p.qua);