String searching in Linq to Entity Framework

Question

I am wanting to create a Where statement within my Linq statement, but have hit a bit of a stumbling block.

I would like to split a string value, and then search using each array item in the Where clause.

In my normal Sql statement I would simply loop through the string array, and build up there Where clause then either pass this to a stored procedure, or just execute the sql string. But am not sure how to do this with Linq to Entity?

( From o In db.TableName Where o.Field LIKE Stringvalue Select o ).ToList()

Answer 1

How about

(from o in db.Tablename
    where o.Field.Contains(Stringvalue)
    select o).ToList();

Answer 2

var fcs = from c in Contacts
          where c.FirstName.ToLower().StartsWith(e.value.ToLower())
          select c;

You can use any string functions like contains, startswith etc

Answer 3

EDIT: Misinterpreted your question, how about:

string filters = "a,b,c,d";
var result = from filter in filters.Split(',')
             from record In db.TableName
             where record.Field.Contains(filter)

Answer 4

To do dynamic construction it's probably best to use an expression tree. Give this a shot:

IQueryable<EntityType> query = db.Tablename;
Expression<Func<EntityType, bool>> expression = null;

foreach (string item in yourString.Split(","))
{
    string localItem = item; // so that we don't close over the loop variable

    Expression<Func<EntityType, bool>> exp = x => x.FieldName.Contains(localItem);

    if (expression == null)
    {
        expression = exp;
    }
    else
    {
        expression = Expression.Lambda<Func<int, bool>>(
            Expression.OrElse(expression.Body,  
            Expression.Invoke(exp,expression.Parameters.Cast<Expression>())), 
            expression.Parameters);
    }
}

var results = query.Where(expression).ToList();

Answer 5

Air code building on Adam's answer and using Union to chain the results together, emulating AND rather than OR.

EDIT I removed my recommendation to use Distinct because Union filters out duplicates.

var query = null; 

foreach(string item in yourString.Split(",")) 
{ 
    var newquery = db.Tablename.Where(x => x.FieldName.Contains(item));  
    if (query == null) {  
      query = query.union(newquery);    
    } else {  
      query = newquery;  
    }  
}  

var results = query.ToList();