CODEWITHSUNDEEP
postgresqlasp.net-coreentity-framework-core
Lajith
Lajith

Reputation: 1867

EF Core postgresql string compare (case-insensitive)

I am using EF Core 5. Since Postgresql case-insensitive.

Below are 2 methods

Method #1:

 var existingEntity = await _unitOfWork.Banks.FirstOrDefautAsync(x => (x.Code.ToLower() == bank.Code.ToLower()));

Method #2:

 var existingEntity = await _unitOfWork.Banks.FirstOrDefautAsync(x => (EF.Functions.ILike(x.Code, bank.Code));

I need to check if a given code exists in table (case in-sensitive). Which of the above two methods is best / fastest for this, or is there any alternative in EF Core 5?

Thanks

Upvotes: 1

Views: 4702

Answers (1)

euler
euler

Reputation: 89

I recently ran into same problem with you. Here are some options i tried; 1-Case insensitive collations on database or on each property of your entity classes. 2-Citext type 3-ILIKE 4-ToLower() method on every linq query

1 has very downsides like you can't use various sql functions in your queries(ILIKE, LIKE, STRPOS(Contains()...). Thats because of you can't use these functions on a non-deterministic collation.

2 has some performance issues(you can read from docs, i readed a lot)

3 ILIKE isn't better than '=' check for performance

4 I personally choose this one because that was best suit for me. Also if you need to index your column that need to be case-insensitive, you can index with functions(LOWER(column_name))

Upvotes: 2

Related Questions