5

Is there any way to force LINQ to SQL to perform a case-insensitive Contains?

I'm working with PostgreSQL on Entity Framework Core.

wonea
  • 4,783
  • 17
  • 86
  • 139
SuperJMN
  • 13,110
  • 16
  • 86
  • 185
  • https://stackoverflow.com/questions/444798/case-insensitive-containsstring – Blue Granny Oct 19 '17 at 11:41
  • 4
    Most straightforward way is `ctx.Table.Where(c => c.Column.ToLower().Contains(searchTerm.ToLower())` – Evk Oct 19 '17 at 11:57
  • Possible duplicate of [Case insensitive 'Contains(string)'](https://stackoverflow.com/questions/444798/case-insensitive-containsstring) – Cristian Szpisjak Oct 20 '17 at 15:36
  • @Evk That solution works, but if that same string is indexed, it'll cause the DB to skip the indexes and perform a table lookup. Something to keep in mind. – aevitas May 26 '18 at 17:51
  • @aevitas not necessary. Postgesql allows to define index on expression, so you might have index on lower(YourColumn), and such index will be used for query above. Still worth to keep in mind of course. – Evk May 26 '18 at 17:54

1 Answers1

-1

Case insensitive name of tables and properties in Entity Framework 7

With reference to the answer above, no. There is no way to further load case sensitivity checks at an even lower level. You'll have to perform exactly what this person did:

How do I make contains case-insensitive in ef core 2?

Here's an example on the latest LINQ syntax.

var tickerObj = _unitOfWork.GetRepository<CurrencyPair>()
                .GetQueryable()
                // .Where() and .Include() omitted for brevity
                .SingleOrDefault(cp => string.Concat(
                    cp.PartialCurrencyPairs.FirstOrDefault(pcp => pcp.IsMain).Currency.Abbrv,
                    cp.PartialCurrencyPairs.FirstOrDefault(pcp => !pcp.IsMain).Currency.Abbrv)
                    .Equals(ticker, StringComparison.InvariantCultureIgnoreCase)
                && cp.CurrencySource.Abbreviation.Equals(exchangeAbbrv, StringComparison.InvariantCultureIgnoreCase));

Focus on the SingleOrDefault() segment:

cp.CurrencySource.Abbreviation.Equals(exchangeAbbrv, StringComparison.InvariantCultureIgnoreCase)
Nicholas
  • 1,883
  • 21
  • 39