How to implement the sql "%" in EF?

Question

What I want to do is :

var TheQuery = db.Conventions.Where(p => p.Categories.Contains("%"));

but it nothing is returned from database!

possible duplicate of: http://stackoverflow.com/questions/1033007/like-operator-in-entity-framework — jessehouwing, Mar 23 '12 at 10:22
That query should return all rows that have a `%` in them. Could you explain your use case a little better if that's not what you're expecting...? — Joachim Isaksson, Mar 23 '12 at 10:23
are you trying to find conventions with the '%' character somewhere in their Category field? — paul, Mar 23 '12 at 10:23
Do you want to search for the category named "%" or for any category (e.g. SELECT * FROM CATS WHERE CATS.NAME LIKE %). In the latter case you can use p.Categories.Any() — jessehouwing, Mar 23 '12 at 10:23
I have a search form with multiple fields, what i want to do is what to assigne to variable if it's an empty string. `if(Request["Categorie"]!="") categorie = Request["Categorie"]; else' categorie = "%"; //select all categories var TheQuery = db.Conventions.Where(p => p.Categories.intituleCategorie.Contains(categorie) ); — M. ELOUALI, Mar 23 '12 at 10:34

jessehouwing · Accepted Answer · 2012-03-23T10:42:17.257

Based on your comment to DaveShaw I think this is what you should do:

IQueryable<Convention> query = db.Conventions;
if (!string.IsNullOrWhiteSpace(inputCategory))
{
    query = query.Where(p => p.Categories.Contains(inputCategory));
}
if (!string.InNullOrWhiteSpace(inputName))
{
    query = query.Where(p=> p.Name == inputName);
}

etc.

Now if you want to make sure that your Convention has at least one category, you can instead use .Any()

if (string.IsNullOrWhiteSpace(inputCategory))
{
    query = query.Where(p => p.Categories.Any());
}

or if it should have any category with a specific name

if (!string.IsNullOrWhiteSpace(inputCategory))
{
    query = query.Where(p => p.Categories.Any(c => c.Name.Contains(inputCategory));
}

The actual query isn't executed until you call ToArray, ToList or ToEnumerable on it (or any other method that requires client side evaluation). As long as you stick to pure LINQ calls on the Query object, you can continue to stick where clauses to it until you're satisfied.

The problem is that I need to do that for multiple fields (24 other fields) — M. ELOUALI, Mar 23 '12 at 10:37
So add 24 if's. it's better than sending an overly complex query to the database. — jessehouwing, Mar 23 '12 at 10:40
Please notice (from my practice) .Any() generate a huge db request. Be careful with it. — Evgeniy Labunskiy, Mar 23 '12 at 10:51

score 0 · Answer 2 · answered Mar 23 '12 at 10:25

The SQL "%"? Do you mean when using with a LIKE? If so just use .Contains("Products") to bring back Categories such as:

carProducts
houseProducts2

i.e. The contains will bring back results where the Categories property/field "contains" the string, regardless of what is either side of it.

Or did you mean you would like the Categories to contain the character '%'? (You are successfully doing that now)

How to implement the sql "%" in EF?

2 Answers2