OData accent-insensitive filter

Question

How can I apply a filter accent-insensitive? In OData the "eq" operator is case and accent sensitive. The case is easy to fix, because the "tolower" but relative to the accent I'm not getting a simple solution. I know contains is supposed to be accent-insensitive but if I use contains filtering by "São José" I am only getting these responses "São José" and "São José dos Campos", it is missing "Sao Jose".

The following example filtering by "Florianopolis" is expected to return "Florianópolis", but it does not:

url: api/cidades/get?$filter=contains(nome, 'Florianopolis')

[HttpGet] 
[EnableQuery] 
public ActionResult<IQueryable<CidadeDTO>> Get() 
{ 
    try
    {
        return Ok(_mapper.Map<IEnumerable<CidadeDTO>>(_db.Cidades)); 
    } 
    catch (System.Exception e) 
    { 
        return BadRequest(e.GetBaseException().Message);
    }
} 

It should bring aswell, like entity framework.

Answer 1

If your OData model was mapped directly to EF models AND an IQueryable<T> expression was passed into OK() then the query is explicitly passed through to the database engine as SQL:

SELECT * FROM Cidades WHERE nome LIKE '%Florianopolis%'

When that occurs, the Collation settings in the database connection will determine the comparison matching logic.

When your database collation is case and accent insensitive, but your data is still filtered as if it was not, then this is an indication that an IEnumerable<T> has been passed into OK() and the comparison logic is being evaluated in C# which by default in insensitive to both case and accent. Unfortunately this means that it is very likely that the entire data table has been loaded into memory first so that the filter can be applied.

In your case the OData model is mapped to DTO expressions that are mapped to the EF models via AutoMapper and that is where the generated query can break down. By calling Map() you are loading ALL records from the EF table and leaving the $filter criteria to be applied by the EnableQueryAttribute

For OData query conventions to be applied automatically you must return an IQueryable<T> from your method, or atleast pass an IQueryable<T> into the OK() response handler. With AutoMapper, you can use the Queryable Extensions to satisfy the IQueryable<T> requirement:

Queryable Extensions
When using an ORM such as NHibernate or Entity Framework with AutoMapper’s standard mapper.Map functions, you may notice that the ORM will query all the fields of all the objects within a graph when AutoMapper is attempting to map the results to a destination type.

...

ProjectTo must be the last call in the chain. ORMs work with entities, not DTOs. So apply any filtering and sorting on entities and, as the last step, project to DTOs.

In OData the last requirement (about ProjectTo) is still problematic because the EnableQueryAttribute will append the query options to the IQueryable<T> response, which will still end up materializing the entire table into memory first (IEnumerable<T>) and then apply the filter, which is still incredibly inefficient. It is this behaviour that is generally observed when someone complains about poor performance from an OData implementation, it is not always AutoMapper, but usually the pattern that the data source is loaded into memory in its entirety and then filtered. Following the default guidance for AutoMapper will lead you in this direction.

Instead we need to use an additional package: AutoMapper.Extensions.ExpressionMapping that will give us access to the UseAsDataSource extension method.

UseAsDataSource
Mapping expressions to one another is a tedious and produces long ugly code.

UseAsDataSource().For<DTO>() makes this translation clean by not having to explicitly map expressions. It also calls ProjectTo<TDO>() for you as well, where applicable.

This changes your implementation to the following:

[HttpGet] 
[EnableQuery] 
public ActionResult<IQueryable<CidadeDTO>> Get() 
{ 
    return Ok(_db.Cidades.UseAsDataSource().For<CidadeDTO>()); 
} 

---

Don't fall into the trap of assuming that AutoMapper is necessary or best practice for an OData API implementation. If you are not using the unique features that AutoMapper provides then adding an additional abstraction layer can end up over-complicating your solution.

I'm not against AutoMapper, I use it a lot for Integrations, ETL, GraphQL and non-DDD style data schemas where the DTO models are significantly different to the underlying EF/data storage models. But it is a maintenance and performance overhead that a simple DDD data model and OData API based solution can easily do without.

• Don't hire an excavator when a simple shovel will do the job.

AutoMapper is a convention based ORM that can be useful when you want to change the structure between implementation layers in your code, traditionally you might map Business Domain models that may represent aggregates or have flattened structures to highly normalised Database models.

OData is also a convention based ORM. It was designed to facilitate many of the same operations that AutoAmpper provides with the exception of Flattening and Unflattening models. These operations are deferred to the EF engine. The types exposed via OData mapping are DTOs

If your DTO models are the same relational structure as your EF models, then you would generally not use AutoMapper at all, the OData Edm mapping is optimised specifically to manage this type of workload and is designed to be and has been integrated directly into the serialization layer, making the Edm truely Data Transfer Objects that only exist over the wire and in the client.

Answer 2

This did the job

[HttpGet]
public ActionResult<IQueryable<PessoaDTO>> Get(ODataQueryOptions<Pessoa> options)
    {
        try
        {
            var queryResult = options.ApplyTo(_db.Pessoas);
            return Ok(queryResult);
        }
        catch (System.Exception e)
        {
            return BadRequest(e.GetBaseException().Message);
        }

    }