Case Insensitive Search Filter in OData ASP.NET Core 2.0 Web API as an OWIN Middleware

• Download VS demo project - 9.2 KB

Introduction

Google, Bing and other search engines provide their results disregarding upper/lower case in the input box. OData services behave mostly in the same way. Configuring the database collation to ignore the case is the most productive solution. Is there no possibility to alter the database, e.g., due to lack of access rights or when a database is an external service, there is another recommendation, converting the database values to lower case before executing the database query.

This article doesn't explain how to configure Entity Framework and OData Services from scratch, however there is a working example attached to this article as a Visual Studio project.

In this article, I’ll show you how to alter the OData filter (or any other query string) in ASP.NET Core Web API using an OWIN middleware attached at the beginning of the pipe. The purpose of this handling is extending database query by the tolower function for ignoring upper/lower case.

Required Environment, Tools and NuGet Packages

To reproduce the scenario, we need the following software components.

ASP.NET Core 2.0 Web Application

The latest version of Visual Studio 2017 (15.5.x) provides templates for creating web applications and web services based on .NET Standard 2.0 and therefore on .NET Core 2.0 or .NET Framework 4.6.1.

If you barely know the difference between .NET Standard and .NET Core - .NET Standard is a "specification" whereas .NET Core and .NET Framework are "implementations" of this specification.

For further details, please visit the official Microsoft page concerning .NET Standard.

After you have Visual Studio installed, create a new "ASP.NET Core 2.0 Web Application". Then select the "Web API" template.

Entity Framework Core

I like the tandem Entity Framework and SQLite very much. It's easy to set up, it's portable and it can be used in small or even medium web sites. For using it, please install the following NuGet packages:

Microsoft.EntityFrameworkCore.Sqlite
Microsoft.EntityFrameworkCore.Tools

The second one is required for creating migrations and the database file.

OData ASP.NET Core

Actually (Jan-2018), the only possibility of using OData in ASP.NET Core provides the following package:

Microsoft.AspNetCore.OData

Since this is a beta yet, you should mark the checkbox "Include prerelease" to show it in the NuGet Package Manager.

Configuring the Components

Entity Framework

As the EF model, we use the Person class. Its Name property is the search target.

public class Person
{
  public int Id { get; set; }
  public string Name { get; set; }
  public DateTime Birthday { get; set; }
}

For setting up the DbContext and further settings of Entity Framework, please refer to the attached sample project.

It's worth noting that the sample project refers to its database file on drive "T:" which is virtual.

Setting up a common, virtual drive for all team members is valuable at least for two reasons:

1. All project configurations refer to the same location on each machine.
2. Long paths located deep in "My documents" are no more a problem.

You can set a common drive by opening the command line and executing the subst command.

subst.exe t: d:\temp

provided, the "d:\temp" exists.

OData

According to the OData Version 4.0 specification, data filtering is achieved with the OData function contains.

http://localhost:51192/odata/Persons?$filter=contains(Name,'dom')

The above http request provides all values from the database column Name containing the text "dom". However, values like "Dominik" or "KINGDOM" will not be found, unless they are converted to lower case using the tolower OData function.

http://localhost:51192/odata/Persons?$filter=contains(tolower(Name),'dom')

Recreating the ODataQueryOptions, modifying its Filter property or custom parsing the query string and rebuilding the database queries are complicated and error prone.

Therefore, we update HttpContext.Request.QueryString in the OWIN pipeline before other OWIN middlewares and action filters start processing it. Modifying the query string at a later time could be risky for the stability of the web application.

Regex

We replace the following fragment of the query string:

contains(Name,

with the following one:

contains(tolower(Name),

using the following regex:

Regex replaceToLowerRegex = new Regex(@"contains\((?<columnName>\w+),");
var s = replaceToLowerRegex.Replace(context.Request.QueryString.Value, @"contains(tolower($1),")

If you need to replace not only the database value but additionally the search value, use the following regex:

Regex replaceToLowerRegex =
         new Regex(@"contains\((?<columnName>\w+),.*(?<value>(\'|%27).+(\'|%27))\)");
var s = replaceToLowerRegex.Replace(context.Request.QueryString.Value,
         @"contains(tolower(${columnName}),tolower(${value}))");

It produces the following query string:

http://localhost:51192/odata/Persons?$filter=contains(tolower(Name),tolower('Dom'))

OWIN Middleware

For better readability, we wrap the regex algorithm in a custom OWIN middleware - ODataQueryStringFixer.

public class ODataQueryStringFixer : IMiddleware
{
  private static readonly Regex ReplaceToLowerRegex =
    new Regex(@"contains\((?<columnName>\w+),");

  public Task InvokeAsync(HttpContext context, RequestDelegate next)
  {
    var input = context.Request.QueryString.Value;
    var replacement = @"contains(tolower($1),";
    context.Request.QueryString =
      new QueryString(ReplaceToLowerRegex.Replace(input, replacement));

    return next(context);
  }
}

Then, we create a user friendly extension method UseODataQueryStringFixer to anchor the middleware in the OWIN pipeline.

public static class ODataQueryStringFixerExtensions
{
  public static IApplicationBuilder UseODataQueryStringFixer(this IApplicationBuilder app)
  {
    return app.UseMiddleware<ODataQueryStringFixer>();
  }
}

The last step is updating the Startup class.

Startup

In the ConfigureServices method, we need to declare ODataQueryStringFixer as a service using services.AddSingleton.

public void ConfigureServices(IServiceCollection services)
{
    // the database filename is stored in the appsettings.json
    var connectionString = Configuration.GetConnectionString(nameof(ApplicationDbContext));
    services.AddDbContext<ApplicationDbContext>(optionsAction =>
        optionsAction.UseSqlite(connectionString));

    // declare OWIN middleware as a service
    services.AddSingleton<ODataQueryStringFixer>();

    services.AddOData();

    services.AddMvc();
}

In the Configure method, our OWIN middleware should be declared before the OData middleware.

public void Configure(IApplicationBuilder app,
    IHostingEnvironment env, IServiceProvider serviceProvider)
{
    if (env.IsDevelopment())
    {
        app.UseDeveloperExceptionPage();
    }

    // create and populate DB
    serviceProvider.InitializeDb();

    // anchor OWIN middleware in the pipe
    app.UseODataQueryStringFixer();

    // configure OData model
    var builder = new ODataConventionModelBuilder(serviceProvider);
    builder.EntitySet<Person>("Persons").EntityType
        .OrderBy(
            nameof(Person.Name),
            nameof(Person.Birthday))
        .Filter(
            nameof(Person.Name));

    // configure OData routing
    app.UseMvc(routeBuilder =>
        routeBuilder.MapODataServiceRoute("OData", "odata", builder.GetEdmModel()));
}

That’s it. After starting the application, our OWIN middleware extends the query string with the tolower OData function.

Points of Interest

After reading some internet articles, I was focused on recreating the ODataQueryOptions in the action method of ODataController or overriding the EnableQueryAttribute.

However, none of the above is as simple as modifying the HttpContext.Request.QueryString just at the request beginning.

I’m impressed by the power and flexibility of ASP.NET Core 2.0 as I was impressed by the .NET 2.0 comparing to .NET 1.1 about 15 years ago...Go, Go Microsoft!

If you like this article, please rate it with 5. If not, please tell me about your objections in a comment below. ;-)

History

• 31-Jan-2018: Published