Implementing a "like" operator multiple times in one Linq to Entities query

Question

We have a list of strings and we need to filter our results by that list. Example would be find all students who have SSNs that start with 465, 496, or 497 (plus x more)

List<string> list = GetPossibleStartsWithValues();
var qry = from student in entities.Students.WhereStartsWith(x=>x.SSN, list)
select new SearchedStudent
{
    Name = student.Name,
    SSN = student.SSN,
    ...
}

The code provided here is close to what we need, but we can't figure out how to impliment the StartsWith that we need using the Expression Class.

Answer 1

Well, you could try this:

public static IQueryable<T> WhereStartsWith<T>(this IQueryable<T> source,
    Expression<Func<T, string>> projection,
    List<T> list)
{
    return source.Where(x => list.Any(y => projection(x).StartsWith(y)));
}

That may well not work, but it would be worth trying before you go into anything more complicated.

EDIT: As you say, the above won't compile - you basically need to build an expression tree representing the bit within the Where clause. Oops. However, before you start doing that, it would be worth seeing whether it'll work in the end. Try this:

List<string> list = GetPossibleStartsWithValues();
var qry = from student in entities.Students
     .Where(student => list.Any(y => student.SSN.StartsWith(y)))
select new SearchedStudent
{
    Name = student.Name,
    SSN = student.SSN,
    ...
}

If that doesn't work, then making a more general method won't be any use :(

Answer 2

How about using a compound statement such as

var qry = from student in entities.Students.Where( 
             s => list.Where( x => s.StartsWith(x)).Count() != 0 )