c# 4.0 - How to use SQL wildcards in LINQ to Entity Framework

Question

I have a query that looks like this:

IQueryable<Profile> profiles = from p in connection.Profiles
    where profile.Email.Contains(txtSearch)
    select p;

I know that when this is converted to SQL it uses a LIKE '%<value of txtSearch>%' but if txtSearch = "jon%gmail.com" it converts it to `LIKE '%jon~%gmail.com%'. The ~ escapes the % in the middle that is a wild card. How do I get around that? I need to be able to put wild cards into my LINQ to EF searches.

Answer 1

I'm not sure that this is possible directly with linq because you can call only basic string functions like Contains, StartsWith or EndsWith. It is possible with Entity SQL so you can combine these approaches.

var query = new ObjectQuery<Profile>(
    @"SELECT VALUE p
      FROM CsdlContainerName.Profiles AS p
      WHERE p.Email LIKE '" + wildcardSearch + "'",
    context);

var result = query.AsQueryable().OrderByDescending(p => p.Name).ToList();

ESQL injection strikes back :)

Second version without injection vulnerability (I didn't try it but it should work):

var commandText =
    @"SELECT VALUE p
      FROM CsdlContainerName.Profiles AS p
      WHERE p.Email LIKE @search";

var query = new ObjectQuery<Profile>(commandText, context);
query.Parameters.Add(new ObjectParameter("search", wildcardSearch));

var result = query.AsQueryable().OrderByDescending(p => p.Name).ToList();