110

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?

Phuc Thai
  • 718
  • 7
  • 17
areller
  • 4,800
  • 9
  • 29
  • 57
  • 5
    Check the SQL statement. Most likely, they are identical. `.StartsWith` gets translated to `LIKE 'a%'`. *Contains* on the other hand will be converted to `LIKE '%a%'`, which is a very bad idea. `LIKE 'a%'` can take advantage of indexes, since it's essentially a range search. `LIKE '%a%'` though has to process all rows to find if the pattern matches – Panagiotis Kanavos Aug 16 '17 at 08:33
  • 2
    Yes, thats the reason – Sir Rufo Aug 16 '17 at 08:42
  • 1
    Yeah, that what i was suspecting, it would've been a lot better if they provided an extension method for it, c.Name.Like(...) – areller Aug 16 '17 at 08:46
  • If you do not provide character case for "like" it does search for only the character or word as you provided. Also contains do the same. but to avoid character case you may use like below: string k = "I like your style"; StringComparison stringComparison = StringComparison.CurrentCultureIgnoreCase; Console.WriteLine(k.IndexOf("your", stringComparison)); – Kadir Lüzumlar Aug 16 '17 at 08:47
  • 1
    It's just a higher flexibility. It is easier to read `.Like('abc%def%ghi')` then trying to compose the same with tree different methods. – Oliver Aug 16 '17 at 08:53
  • Does it also respect Server's collation? I had a problem with StartsWith/Containts functions on CI_AI databases – llouk Aug 16 '17 at 09:24

2 Answers2

107

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)

adiga
  • 34,372
  • 9
  • 61
  • 83
  • 2
    Also consider reading [#474 Query: Improve translation of String's StartsWith, EndsWith and Contains](https://github.com/aspnet/EntityFramework/issues/474) thread for other than flexibility reasons behind this decision. – Ivan Stoev Aug 16 '17 at 11:38
  • As Tseng said, there are implications, in the translations as well, for instance, **.Contains** is translated to **CHARINDEX** for SqlServer and is collation aware, but for Sqlite it is translated in **INSTR** and unlike **LIKE** , INSTR does not care about collation **NOCASE** ( so filtering through efcore for sqlite keeps case sensitive, even if it was configured to not, unless you use EF.Functions.Like) – Marco Medrano Sep 22 '22 at 21:05
  • For this instance, what if c.City is null for a row? Do we need to do a null check? – Lee Jan 11 '23 at 03:32
103

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.

Tseng
  • 61,549
  • 15
  • 193
  • 205
  • 26
    Which one is faster? – Chris Go May 14 '20 at 23:20
  • 1
    @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. – revobtz May 01 '21 at 03:16
  • 5
    @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) – Tseng May 01 '21 at 15:48
  • 1
    @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. :) – revobtz May 01 '21 at 19:48
  • Right, for instance as Tseng said **.Contains** is translated to **CHARINDEX** for SqlServer, for Sqllite is translated to **INSTR** and this function unlike to **LIKE** does not care about collection **NOCASE** – Marco Medrano Sep 22 '22 at 20:50