Like SQL Query in LINQ

Question

Hi,

My problem is the following:

I'm looking for a solution in LINQ which translates a LINQ expression to SQL LIKE query.

(I know in LINQ I can use Contains, StarsWidth, EndWidth instead of 'LIKE' but in my case is not a good solution, and if you check the generated sql script you will see it is not use LIKE)

I found a lot of article that use the System.Data.Linq.SqlClient.SqlMethods.Like methods so I wrote my query:

var query = from c in _context.prgCity where SqlMethods.Like( c.FullName, "%buda") select c.FullName + "|" + c.prgCountry.CountryName;

return query.ToList<string>();

But when query is running I get the following error message:

"LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method, and this method cannot be translated into a store expression."

Anybody can Help me what I do wrong?

Answer 1

You're trying to use SqlMethods.Like when from LINQ to Entities. As per the documentation:

This method is currently only supported in LINQ to SQL queries.

You haven't really explained why Contains/StartsWith/EndsWith don't work for you (and in my experience they do get translated into LIKE clauses).

Answer 2

You could write your query as:

var query = from c in _context.prgCity 
            where c.FullName.EndsWith("buda")
            select c.FullName + "|" + c.prgCountry.CountryName;

Answer 3

where c.FullName.Contains("buda")

Answer 4

I try to explain why the Startwidth method is not good for me.

I have a City table which contains 2401038 records. I have a jquery autocomplete textbox in my application that uses this query that retrieves the results filter by the input text of textbox.

I experienced that if I wrote 5 characters in the textbox I had to wait 5 seconds to the autocomplete combobox would be shown.

I checked the sql query which was generated by LINQ in Profiler and I saw the query used the CHARINDEX and not the LIKE.

I dont know why in my case if I use the charindex in the query the response time will be increased dramatically depending on the lenght of the parameter string.

for instance when I'm looking for "buda" in the City.FullName colums the query executing time is < 0 sec, but when I'm looking for "budap" the time is > 5 sec!!!

But if I make run ny query in sql manager width LIKE instead of the CHARINDEX I have no problems with the query executing time.

l.

Answer 5

Hello. Use Contains instead of SQLMethods.Like. It will work.