1

I have implemented twitter's typeahead plugin for form auto completion on my application.

When querying the database for a match to the name entered in the input box, it would only match case sensitive names, however typeahead is supposed to match case insensitive.

example: "Kevin S" typeahead suggests Kevin Smith, but "kevin s" typeahead suggests nothing.

API code

// GET: /api/authors
public IHttpActionResult GetAuthors(string query = null)
{
    var authorsQuery = _context.Authors.ToList().Select(Mapper.Map<Author, AuthorDto>);

    if (!String.IsNullOrWhiteSpace(query))
        authorsQuery = authorsQuery.Where(c => c.Name.Contains(query));

    return Ok(authorsQuery);
}

I thought it had something to do with the .ToList() executing before querying the database with my .Where() call.

I changed my code to this and it now works

// GET: /api/authors
public IHttpActionResult GetAuthors(string query = null)
{
    var authorsQuery = _context.Authors.AsQueryable();

    if (!String.IsNullOrWhiteSpace(query))
        authorsQuery = authorsQuery.Where(c => c.Name.Contains(query));

    var authorsDto = authorsQuery.ToList().Select(Mapper.Map<Author, AuthorDto>);
    return Ok(authorsDto);
}

so when entering "kevin s" typeahead suggests "Kevin Smith"

I tried with AsEnumerable instead of AsQueryable() and it had the same effect as the original code

why is it that it works with AsQuerable but not the others? is it to do with query execution?

johnny 5
  • 19,893
  • 50
  • 121
  • 195
  • `AsQueryable` is probably using your databases default collation for the comparison, whereas the other methods run the code in the CLR. –  May 01 '18 at 19:17
  • 2
    Read, digest, return: https://stackoverflow.com/questions/17968469/whats-the-differences-between-tolist-asenumerable-asqueryable It's **highly** relevant. – spender May 01 '18 at 19:17
  • 1
    Filtering on `IQueryable` will end up changing the SQL that is being executed. If your database collation is case **in**sensitive, your query will be too. However, if you consume the `IQueryable` into a .NET collection, and then filter on that, you're running .NET code which doesn't abide by the same rules. – Lasse V. Karlsen May 01 '18 at 19:20

1 Answers1

3

I thought it had something to do with the .ToList() executing before querying the database with my .Where() call.

You're right. With the .AsQueryable(), your c => c.Name.Contains(query) is an expression tree. This means it's not translated to MSIL code that runs what you wrote, but it's translated to a description of what you wrote. Entity Framework can use that to translate it to SQL, and in SQL, this may be case insensitive even if it would be case sensitive in ordinary C# code.

The .Where resolves to Queryable.Where.

I tried with AsEnumerable instead of AsQueryable() and it had the same effect as the original code

With .AsEnumerable(), even though your query isn't immediately executed, c => c.Name.Contains(query) is compiled to regular C# code.

This is because Queryable.Where cannot be used (you don't have an IQueryable<T>), and Enumerable.Where is used instead. The latter doesn't accept expression trees.

  • Thank you this is a great answer. Thank you to the others too for explaining it in different ways and pointing me to a great resource. –  May 01 '18 at 21:05