CODEWITHSUNDEEP
c#asp.netlinqwebforms
stats101
stats101

Reputation: 1877

Join columns in LINQ and run contains operator

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."

Upvotes: 3

Views: 1426

Answers (3)

stats101
stats101

Reputation: 1877

The contains method for whatever reason was not using the filtered results when it came through the loop a second time around. The approach that eventually worked was:

var SearchColumn = from qua in Qual
               join del in deliverables on qua.Q_ID equals del.Q_ID into left_j
                            from del in left_j.DefaultIfEmpty()
                            select new { qua.Name + " " + qua.Project + " " + qua.Summary + " " + del.Name + " " + del.Summary).ToLower() };

foreach (var f in searchFilter)
{
   var likestr = string.Format("%{0}%", f);
   SearchColumn = SearchColumn.Where(x => SqlMethods.Like(x.Search_Col, likestr));
}

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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);

Upvotes: 1

Amiram Korach
Amiram Korach

Reputation: 13286

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.

Upvotes: 1

Related Questions