In version 3.1.1, why string contains methods are case-sensitive

[hzicong]

The issue

var query = _repository.GetAll().Where(p => p.Title.Contains(input.Title, StringComparison.CurrentCultureIgnoreCase))
or
var query = _repository.GetAll().Where(p => p.Title.Contains(input.Title)

Using the 2.2 with a lambda with a Where() and a Contains() filter generates the following SQL

SELECT `p`.`Id`, `p`.`CreationTime`, `p`.`EndTime`, `p`.`Icon`, `p`.`Intro`, `p`.`MobileAdvertBanner`, 
`p`.`MobileBanner`, `p`.`PCAdvertBanner`, `p`.`PCBanner`, `p`.`PriceIcon`, `p`.`ProductBuyCount`, 
`p`.`ProductCreationTime`, `p`.`SignUpEndTime`, `p`.`SignUpStartTime`, `p`.`StartTime`, `p`.`Title`
FROM `Product_Activity` AS `p`
WHERE (LOCATE(@__input_Title_0, `p`.`Mobile`) > 0) 
OR (@__input_Title_0 = ''))
ORDER BY `p`.`Id` DESC

The same query using the 3.1 provider generates the following SQL

SELECT `p`.`Id`, `p`.`CreationTime`, `p`.`EndTime`, `p`.`Icon`, `p`.`Intro`, `p`.`MobileAdvertBanner`, 
`p`.`MobileBanner`, `p`.`PCAdvertBanner`, `p`.`PCBanner`, `p`.`PriceIcon`, `p`.`ProductBuyCount`, 
`p`.`ProductCreationTime`, `p`.`SignUpEndTime`, `p`.`SignUpStartTime`, `p`.`StartTime`, `p`.`Title`
FROM `Product_Activity` AS `p`
WHERE ((LOCATE(CONVERT(LCASE(@__input_Title_0) USING utf8mb4) COLLATE utf8mb4_bin, LCASE(`p`.`Title`)) > 0) 
OR (@__input_Title_0 = ''))
ORDER BY `p`.`Id` DESC`

Further technical details

MySQL version: 5.7.22 or 8.0
Operating system: Win10 or Centos 8 docker
Pomelo.EntityFrameworkCore.MySql version: 3.1.1

[mguinness]

Likely duplicate of #996

[hzicong]

should I change it to EF.Functions?

[lauxjpn]

As @mguinness said, we already fixed this for #996. It was a regression bug:

The provider is back to using the casing that is defined by the collation of the column (currently only nightly build, but will be part of 3.2.0).

If your collation for a given column is utf8mb4_0900_as_cs for example, the casing used (if no explicit StringComparison parameter is specified) is case sensitiv.

If on the other hand a utf8mb4_0900_ai_ci collation has been defined for the column, case _in_sensitive comparisons will be used.

---

Hopefully, this will be a 3.x release?

Yes, this will be part of the next Pomelo release, which will be 3.2.0.

The fix for this is part of our current nightly builds.

---

should I change it to EF.Functions?

If you don't want to use the nightly build, you can use EF.Functions.Like() as a workaround.

[hzicong]

ok,thanks，which version i can use in 3.2.0-preview?

[lauxjpn]

It should show you the dependencies, when clicking on the package in your IDE before updating.
You can also take a look at the package in our nightly build feed:

Dependencies:

.NETStandard2.0
Microsoft.EntityFrameworkCore.Relational (≥ 3.1.3)
MySqlConnector (≥ 0.69.6 1.0.0)
Pomelo.JsonObject (≥ 2.2.1)

Also, the general versions are listed under Compatibility. Version 3.2.0 should be similar to 3.1.1.

[hzicong]

Thanks