LINQ to SQL query help (string contains any string in string array)

Question

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

Answer 1

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.