1

Is there a way to tell EF not to escape my wildcards?

My current solution looks like this:

 var movieNameWithProperWildcards = string.Format("%{0}%",    
 movieName.ToLower().Replace("*", "%"));

 var sqlParameter = new SqlParameter { ParameterName = "searchParameter", Value =   
 movieNameWithProperWildcards };

 List<Movie> movieEntities = MovieContext.Movies.SqlQuery("select * from Movies WHERE 
 Lower(title) like @searchParameter", sqlParameter).ToList();

But that would be much nicer: 

 List<Movie> movieEntities = MovieContext.Movies.Where(movie =>  
 movie.Title.ToLower().Contains(movieName));

br rene_r

rene_r
  • 936
  • 1
  • 6
  • 15
  • And what is the problem with this code? – Ladislav Mrnka May 27 '11 at 07:23
  • Because one of the advantages of an OR-Mapper is, that I can use LINQ-Queries and don't have to think about writing sql-queries. And its such a common thing so search with wildcards, so why do I have to create such a cumbersome workaround? – rene_r May 27 '11 at 07:30

2 Answers2

1

You want to use LIKE so you can use either your way, ESQL or Linq-to-entities and Contains canonical function. ORM tool is not responsible for translating wildcards from your representation to wildcards representation in the database - you must do it yourselves with tools provided by ORM.

Generally this should work:

var query = from m in ctx.Movies
            where m.Name.ToLower().Contains(movieName)
            select m;

Both String.ToLower and String.Contains are in the list of supported canonical functions.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • Well, Linq-to-Sql provides a like function, that can work with wildcards, nhibernate also provides such a function, so why there is no way with ef(by the way there are a lot of people who struggling with this issue/behavior). Of course Contains is working more or less for simple task, but how would you do such a search just with contains: searchTerm="*matr*I" ? br – rene_r May 27 '11 at 10:13
  • 1
    I would use ESQL or expose model defined function from EDMX. http://stackoverflow.com/questions/5425187/how-to-use-sql-wildcards-in-linq-to-entity-framework/5425764#5425764 – Ladislav Mrnka May 27 '11 at 10:26
0

Check that the query that gets executed is right. See here how you can use context's Log to do that http://www.thereforesystems.com/view-query-generate-by-linq-to-sql/

Mentor
  • 485
  • 1
  • 5
  • 18