Entity framework EF.Functions.Like vs string.Contains

Question

I read the announcement of the entity framework core 2.0 https://blogs.msdn.microsoft.com/dotnet/2017/08/14/announcing-entity-framework-core-2-0/

It says that they added new Sql functions, such as EF.Functions.Like to perform the SQL LIKE operation.

I was wondering what 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 is the difference between the two versions? EF already knows how to translate string.Contains / StartsWith to the appropriate SQL operations, doesn't it?

The only reason I can think of this is because EF.Functions.Like will allow more complex patterns like "a%b%" (although this can be written as StartsWith("a") && Contains("b") )

This is the reason?

Answer 1

Such a query supports wildcards and, therefore, is very useful compared to string expansion methods in some scenarios.

For example: if we were looking for all 4-letter names with 'ri' as middle characters, we could do EF.Functions.Like(c.Name, "_ri_");

or to get all customers from cities that 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 translate differently to SQL queries)

Answer 2

@Adiga's answer is quite incomplete and only covers some of the differences in usage.

However .StartsWith(...) , .Contains(...) and .EndsWith(...) also translate differently in SQL, then EF.Functions.Like .

For example, .StartsWith translates as (string LIKE pattern + "%" AND CHARINDEX(pattern, string) = 1) OR pattern = '' , where .Contains translates to (CHARINDEX(pattern, string) > 0) OR pattern = '' .

EF.Functions.Like however goes into string LIKE pattern [ESCAPE escapeChar] .

It may also have performance implications. The above is true for the EF Core SqlServer provider . Other EF Core vendors may translate it differently.