Click here to Skip to main content
13,737,312 members
Click here to Skip to main content
Add your own
alternative version

Stats

8.5K views
135 downloads
5 bookmarked
Posted 31 Jan 2018
Licenced CPOL

Case insensitive search filter in OData ASP.NET Core 2.0 Web Api as an OWIN middleware

, 31 Jan 2018
Rate this:
Please Sign up or sign in to vote.
How to replace the web api request query string to privide case insensitive OData search.

Introduction

Google, Bing and other search engines provide their results disregarding upper/lower case in the input box. OData services behave mostly 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.

Create ASP.NET Core Web API

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.

Include prerelease packages in 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

  • All project configurations refer to the same location on each machine.
  • 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pawel idzikowski
Software Developer (Senior) Polenter - Software Solutions
Germany Germany
I'm C# developer from Cologne, Germany. Here I owe a small software company. My hobby is general optimization - natural talent of all lazy people Wink | ;-)

You may also be interested in...

Pro

Comments and Discussions

 
QuestionGreat Article, quick question. Pin
wpearson2-Oct-18 11:14
memberwpearson2-Oct-18 11:14 
Questiondownload link and images are broken Pin
Mou_kol31-Jan-18 20:45
memberMou_kol31-Jan-18 20:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180920.1 | Last Updated 1 Feb 2018
Article Copyright 2018 by Pawel idzikowski
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid