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

Can you show how you'd do this in SQL, just to clarify your position as re-reading your question I'm no longer sure my answer satisfies your query. — Lazarus
– Lazarus, Commented Mar 2, 2010 at 16:42
Are you trying to look for values that match every single item in the array (AND), or any one (OR)? — MarkJ
– MarkJ, Commented Mar 2, 2010 at 17:39
For my SQL if I were just to write out a string and execute it I would write... dim Sql as string = " select fields from table where " For each s as string in Stringvalue.split(",") Sql += " fieldname like '%" & s & "%' or " end for remove the final 'or' from the string and run this sql — Tim B James
– Tim B James, Commented Mar 3, 2010 at 9:14

Lazarus · Accepted Answer · 2010-03-02 16:33:00Z

1

How about

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

answered Mar 2, 2010 at 16:33

Lazarus

43.2k4 gold badges47 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam Robinson · Accepted Answer · 2010-03-03 14:08:23Z

1

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

edited Mar 3, 2010 at 14:08

answered Mar 2, 2010 at 16:42

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

4 Comments

Tim B James Over a year ago

Thanks Adam This works, although only returns a list which match the final item in the string array. So if the string value was "a,b,c,d" it would only return those matching "d". Is there a way to join the query within the for loop so that the final query.ToList() contains query.where(x => x.fieldname.contains("a")) and query.where(x => x.fieldname.contains("b")) .... ?

MarkJ Over a year ago

You could use Union to chain the queries together, like my answer

Adam Robinson Over a year ago

@Jim: Give this edit a shot. My original answer was wrong in that it a) was designed to be an AND, when you really want an OR, and b) it closed over the loop variable (d'oh!), which is why it only matched the last condition.

Tim B James Over a year ago

Hi Adam, sorry for the delay. The code produces an error of: "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities."

Yvo · Accepted Answer · 2010-03-02 16:41:35Z

0

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

edited Mar 2, 2010 at 16:41

Yvo

19.4k11 gold badges75 silver badges91 bronze badges

answered Mar 2, 2010 at 16:37

ashish jaiman

3891 silver badge4 bronze badges

1 Comment

Yvo Over a year ago

Many people use the ToLower() trick, but you don't have to. StartsWith has an overload in which you can supply StringComparison.InvariantCultureIgnoreCase

Yvo · Accepted Answer · 2010-03-02 16:57:20Z

0

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)

edited Mar 2, 2010 at 16:57

answered Mar 2, 2010 at 16:40

Yvo

19.4k11 gold badges75 silver badges91 bronze badges

2 Comments

Adam Robinson Over a year ago

This is an equality comparison, not a LIKE operation.

Tim B James Over a year ago

This works when the text I am searching with is exact, but not for partial matches. But thanks for the code as it will come in handy down the line

MarkJ · Accepted Answer · 2010-03-17 09:26:27Z

0

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

edited Mar 17, 2010 at 9:26

answered Mar 3, 2010 at 12:44

MarkJ

30.4k6 gold badges72 silver badges114 bronze badges

3 Comments

Tim B James Over a year ago

hmm no this does not seem to work for me. I get the error: InnerException = {"The text data type cannot be selected as DISTINCT because it is not comparable. The text data type cannot be selected as DISTINCT because it is not comparable."}

MarkJ Over a year ago

I think that means the types don't implement IComparable? Does that sound right?

MarkJ Over a year ago

Actually I was wrong: you don't need to use Distinct, Union already filters out duplicates

Collectives™ on Stack Overflow

String searching in Linq to Entity Framework

5 Answers 5

Comments

4 Comments

1 Comment

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

4 Comments

1 Comment

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related