Entity framework EF.Functions.Like vs string.Contains

Question

I was reading the announcement of 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 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?

Solution 1

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)

Solution 2

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.

Comments

[Ivan Stoev]

Also consider reading #474 Query: Improve translation of String's StartsWith, EndsWith and Contains thread for other than flexibility reasons behind this decision.

[Chris Go]

Which one is faster?

[revobtz]

@ChrisGo In my personal experience the equals operator (==) is the most fastest, followed by StartsWith and EndsWith which are close to the equals operator and contains is the most slower an inefficient since it's need to compare the whole string position by position for the length of the search term. When ever you can use the equal operator use it, second best option is the StartsWith for search and filtering purpose.

[Tseng]

@revobtz: Not sure you realized it, this is not about the C# string operations, but about SQL (and how Entity Framework Core expressions using these translate into SQL)

[revobtz]

@Tseng: I was talking about SQL and entity framework at all time... and even if this was talking about strings, it stills behaves pretty much the same but of course you won't see any performance difference when working with strings since sql needs to perform extra operations to do search filtering. I have worked with a lot of database engines Sql Server, Oracle, MySql, MariaDB, Postgress, MongoDB, as some of them and know how to tune them up. :)