Reputation: 2394
I've been tearing my hair out with this one. I've got an array of search terms and I'm trying to do a LINQ to SQL query to search field values against each item in the array.
I got this far..
var searchResults =
from x in SDC.Staff_Persons
where staffTermArray.Any(pinq => x.Forename.Contains(pinq))
|| staffTermArray.Any(pinq => x.Surname.Contains(pinq))
|| staffTermArray.Any(pinq => x.Known_as.Contains(pinq))
orderby x.Surname
select x;
... but then got
Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator
... and now I'm stuck.
If anyone can help I'd be very grateful. Thanks in advance.
Rob
Upvotes: 6
Views: 2878
Reputation: 172646
I'm not sure if this is the easiest solution, but this will work:
var filter = CreateFilter(staffTermArray);
var searchResults =
from person in SDC.Staff_Persons.Where(filter)
orderby person.Surname
select person;
private static Expression<Func<Staff_Person, bool>> CreateFilter(
string[] staffTermArray)
{
var predicate = PredicateBuilder.False<Staff_Person>();
foreach (var staffTerm in staffTermArray)
{
// We need to make a local copy because of C# weirdness.
var ping = staffTerm;
predicate = predicate.Or(p => p.Forename.Contains(ping));
predicate = predicate.Or(p => p.Surname.Contains(ping));
predicate = predicate.Or(p => p.Known_as.Contains(ping));
}
return predicate;
}
You will need the PredicateBuilder for this to work.
Upvotes: 5
Reputation: 12093
One option would be to do the filtering on the client rather than in SQL. You can force the where
to be evaluated on the client by calling AsEnumerable()
. However, this means that every row of the table is loaded into memory before being tested for a match, so it may be unacceptably inefficient if your search matches only a small number of results from a large table.
var allPersons =
from x in SDC.Staff_Persons
orderby x.Surname
select x;
var searchResults =
from x in allPersons.AsEnumerable()
where staffTermArray.Any(pinq => x.Forename.Contains(pinq))
|| staffTermArray.Any(pinq => x.Surname.Contains(pinq))
|| staffTermArray.Any(pinq => x.Known_as.Contains(pinq))
select x;
Upvotes: -1