litedb

String case sensitive in LiteDB query


In various environment, the user name is case insensitive. We query admin equal to ADMIN. I have searched LiteDB called CompareTo to compare two objects that I can't find a point to make string compared as case insensitive.

The code in QueryEquals.cs

internal override IEnumerable<IndexNode> ExecuteIndex(IndexService indexer, CollectionIndex index)
{
    var node = indexer.Find(index, _value, false, Query.Ascending);

    if (node == null) yield break;

    yield return node;

    if (index.Unique == false)
    {
        // navigate using next[0] do next node - if equals, returns
        while (!node.Next[0].IsEmpty && ((node = indexer.GetNode(node.Next[0])).Key.CompareTo(_value) == 0))
        {
            if (node.IsHeadTail(index)) yield break;

            yield return node;
        }
    }
}

The propositional we can case insensitive

using (var db = new LiteRepository("lite.db"))
{
    db.Insert(new User { Name = "John" });
    var user = db.Query<User>()
        .Where(x => x.Name == "JOHN")
        .FirstOrDefault(); // proposal return John
    var fail = db.Query<User>()
        .Where(x => string.Equals(x.Name, "JOHN", StringComparison.OrdinalIgnoreCase))
        .FirstOrDefault(); // throw exception
}

Another consideration, it is a possible to execute lambda expression in LiteDB without conversion by visitor?


Solution

  • In LiteDB v4, you can use expressions to make sure that the index of username is stored as lowercase, then you can perform a "lowercase-compare"

    // Create index
    entity.EnsureIndex(e => e.Username, "LOWER($.Username)");
    ...
    // Find user
    collection.Find(e => e.Username == username.ToLower);
    

    If you dont want to use expressions, I guess that you could make sure the username is lowercase before saving in LiteDB or you can use an index less query (slower) by using collection.Where (not quite sure on that method though).