Compare strings ignoring case

Question

I am trying to find entities (Tags) in the database via their Name property, where it is important to ignore the case (When database is searched and the Database contains a Tag with the name Database, that one should be returned). Right now, my code looks like this:

public IEnumerable<db.Tag> FindByNames(IEnumerable<string> tagNames)
{
    return this.DatabaseContext.Tags
        .Where(tag => tagNames.Contains(tag.Name));
}

Obviously, this one is case sensitive. I have tried to provide StringComparer.OrdinalIgnoreCase as comparer to the Contains method, but got a warning during execution that the expression could not be translated and would be executed in code rather than in the database (I don't remember the exact message, I will edit as soon as I am back on my development machine). I can live with that if I have to, but it would be nice to know how to let the database do the work in this case. Is it possible?

Please see ["Should questions include “tags” in their titles?"](http://meta.stackexchange.com/questions/19190/should-questions-include-tags-in-their-titles), where the consensus is "no, they should not"! Additionally, you might want to reconsider the `Contains`-operation, as this is going to be of linear (or worse) performance - If the collection contains many elements, inserting in a temp-table with an index and doing a join might be an option. — , Oct 29 '15 at 08:19

score 1 · Answer 1 · edited May 23 '17 at 12:08

No change should be necessary. SQLite's "LIKE" is already case-insensitive.

The default behavior of the LIKE operator is to ignore case for ASCII characters.

(cref https://www.sqlite.org/pragma.html#pragma_case_sensitive_like and Case sensitive and insensitive like in SQLite)

Of course, you can always use .FromSql() to get the exact query you want. Example:

context.Tags.FromSql("SELECT * FROM tags WHERE name LIKE '%{0}%'", tagName)

Compare strings ignoring case

1 Answers1