I was reading the announcement of entity framework core 2.0
It says that they added new Sql functions like EF.Functions.Like for performing the SQL LIKE operation.
I was wondering, what then would be the difference between EF.Functions.Like and string.Contains/StartsWith?
For example:
var customers = context.Customers.Where(c => c.Name.StartsWith("a")); // Version A
var customers = context.Customers.Where(c => EF.Functions.Like(c.Name, "a%")); // Version B
What would be the difference between the two versions?
EF already knows how to translate string.Contains/StartsWith to the corresponding SQL operations, doesn't it?
The only reason i can think of is that EF.Functions.Like would allow for more complex patterns like "a%b%" (although this one can be written as StartsWith("a") && Contains("b"))
Is this the reason?
2 Answers
The answer of @adiga is quite incomplete and covers just a part of the differences in usage.
However, .StartsWith(...), .Contains(...) and .EndsWith(...) are also translated differently into SQL then EF.Functions.Like.
For example .StartsWith gets translated as (string LIKE pattern + "%" AND CHARINDEX(pattern, string) = 1) OR pattern = '' where .Contains gets translated into (CHARINDEX(pattern, string) > 0) OR pattern = ''.
EF.Functions.Like however gets translated into string LIKE pattern [ESCAPE escapeChar].
This may also have implications on Performance. The above is valid for EF Core SqlServer provider. Other EF Core providers may translate it differently.
Like query supports wildcard characters and hence very useful compared to the string extension methods in some scenarios.
For ex: If we were to search all the 4 lettered names with 'ri' as the middle characters we could do EF.Functions.Like(c.Name, "_ri_");
or to get all the customers from cities which start with vowels:
var customers = from c in context.Customers
where EF.Functions.Like(c.City, "[aeiou]%")
select c;
(Please read @Tseng's answer on how they are translated differently into SQL queries)
