Entity framework EF.Functions.Like vs string.Contains

C#Entity Framework.Net CoreEntity Framework-Core

C# Problem Overview


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?

C# Solutions


Solution 1 - C#

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.

Solution 2 - C#

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)

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionarellerView Question on Stackoverflow
Solution 1 - C#TsengView Answer on Stackoverflow
Solution 2 - C#adigaView Answer on Stackoverflow