.StartsWith in .Any uses Cast(CharIndex instead of Like 'TEXT%') which is a lot slower

[NicoJuicy]

I have created an example project on : https://github.com/NicoJuicy/EF-ContainsAndStartsWith ( watch out for the generated migration when checking for difference in unit testing time )

db.Products.Where(el => el.Category.Code.StartsWith("A")).ToList();
//Generates with LIKE 'A%'

var productsFromCat = db.Products.Where(el => categories.Any(dl => dl.StartsWith(el.Category.Code))).ToList();
//Generates a join with  ( CAST(CHARINDEX([Project1].[Code], N'A') AS int)) = 1

The second version is a lot slower, but i believe LIKE 'A%' can be used, which is a lot faster.

I had a generated query which slowed down my performance of loading a page from 1 second to 10 seconds, because of this ( just mentioning the impact).

Could this be altered to be further optimized?

[divega]

@NicoJuicy first of all, based on your expectation that this should translate to LIKE 'A%', I wonder if you actually meant the second query to be:

var productsFromCat = db.Products.Where(el => categories.Any(dl => el.Category.Code.StartsWith(dl))).ToList();

Besides that, the difference between the two queries in your post is that the expression el.Category.Code refers to values that are stored in a table in the server. We don't have a good way to know if the value can be safely used to form the pattern for LIKE (e.g. the value could already contain wildcard characters).

I think it is possible we could do better for cases like this, if it is safe to assume that the LIKE translation will evaluate to true in a strict superset of the cases the CHARINDEX translation will evaluate to true for any possible values of el.Category.Code, then we could AND both translations, i.e. filter using LIKE first and then filter further with CHARINDEX. If I remember correctly we are already applying this logic in EF Core (cc @maumar).

Other than that we have considered having a method in DbFunctions that actually represents LIKE so you can use it when you know you don't have wildcards in the values or when you want the wildcards to be treated as such. There is a pull request for that, which we will evaluate to include in 6.2: #36. The corresponding EF Core issue is dotnet/efcore#6159.

[NicoJuicy]

@divega , yes, the second query is correct. In the referenced project i'm using an new string() {"A"} which actually triggers the ( CAST(CHARINDEX([Project1].[Code], N'A') AS int)) = 1

I have added the generated code in the project, you can see it here: https://github.com/NicoJuicy/EF-ContainsAndStartsWith/blob/master/EF-ContainsAndStartsWith.Test/UnitTest1.cs .

• (1*) TestNoContains uses LIKE
• (2*) TestWithContains uses CHARINDEX
  (The naming could be better though, Contains should be Any)

Background information for additional context:

I had support for one category on a page, this means:

Eg. myshop.com/Show?Category=CatA //CatA is a string used in the ef query (1*)

Now i implemented support for returning products from multiple categories

Eg. myshop.com/Show?Category=CatA+CatB //CatA + CatB is now an array in the ef query (2*)

This led me to the slower query. So the previous example seemed to be correct. When i only used one category, it correctly translated with LIKE 'CatA%'. When i used multiple categories ( in the array), the query translated to usage of CHARINDEX, which caused the slower query.

The LIKE filter in DbFunctions would solve my problem,awesome ! Please consider it in the next release. Thanks for the quick reply

[rowanmiller]

Closing as we plan to take the Like pull request for the next release

[NicoJuicy]

Awesome commit with the 'Like Operator' :), thanks!

[divega]

Dupe of #241.

[dmitry-pavlov]

In case you are reading this to get true LIKE, there is LIKE operator is added in Entity Framework Core 2.0:

var query = from e in _context.Employees
                    where EF.Functions.Like(e.Title, "%developer%")
                    select e;

Comparing to ... where e.Title.Contains("developer") ... it is really translated to SQL LIKE rather than CHARINDEX we see for Contains method.

[divega]

@dmitry-pavlov FWIW, DbFunctions.Like was added as well in EF 6.2, thanks to a contribution from the community.

[proms2018]

I'm doing this:

var lst2 = from c in ent.TheDbSet where EF.Functions.Like("Estado", "%Sur") select c;
var res = lst2.ToArray();

And it translates into:

exec sp_executesql N'SELECT [c].[Id], [c].[Estado]
FROM [Estados] AS [c]
WHERE @__Like_0 = 1',N'@__Like_0 bit',@__Like_0=0

Any ideas why?
Thanks

[proms2018]

Testing:

var lst2 = dbCtx.Estados.Where(c => EF.Functions.Like("Estado", "%Sur"));
var res = lst2.ToArray();

Same results:

exec sp_executesql N'SELECT [c].[Id], [c].[Estado]
FROM [Estados] AS [c]
WHERE @__Like_0 = 1',N'@__Like_0 bit',@__Like_0=0

[proms2018]

Open new issue on this: #542

[byrnedo]

@proms2018 The first argument must be a reference to a property on the Estatdos object:

... c => EF.Functions.Like(c.Estado, "%Sur"));