Dynamic queries UI for ASP.NET Core projects with Entity Framework Core

Prerequisites

Let's suppose we have:

  • an ASP.NET Core project
  • where we use Entity Framework Core to works with some database

Pretty usual, yeah?

Objectives

We want:

  • to create a view/page where our users can build custom (not predefined) queries to that DB and ...
  • to run those queries over our DbContext to get the result sets.

Solution

Here is the point where EasyQuery comes to help. It allows you to add a new page with a query building UI and to setup a middleware which will process all requests from that new page, converts those request to the queries to your DB and return the result back for visualization.

Step 1: Add EasyQuery packages to your project

First, we are going to add EasyQuery to your ASP.NET Core backend project. You can do it directly in your .csproj file:

    <PackageReference Include="Korzh.EasyQuery.AspNetCore" Version="5.1.0" />
    <PackageReference Include="Korzh.EasyQuery.EntityFrameworkCore.Relational" Version="5.1.0" />
    <PackageReference Include="Korzh.EasyQuery.RazorUI" Version="5.1.0" />

The latest package is a Razor UI library which contains several ready-to-use pages or partial views for advanced search and data filtering.

Step 2: Add EasyQuery services

On the next step we a going to register necessary services in DI container:

public void ConfigureServices(IServiceCollection services)
{
    .    .    .    .   .   .
    services.AddEasyQuery()
        .UseSqlManager();
}

Here AddEasyQuery call registers all services necessary for EasyQuery work in the DI container. The the next call (UseSqlManager) sets the default EasyQuery manager - the main "engine" which will process all requests. Currently EasyQuery library contains 2 possible types of manager: EasyQueryManagerSql and EasyQueryManagerLinq. In the code above we set the first one as the default.

Step 3: Add EasyQuery middleware

Now we need to add the middleware which will catch all HTTP requests from the client-side, pass them to the manager, collect the result and prepare it for sending back to the client.

To avoid any conflicts with the MVC handler it's better to place EasyQuery middleware before it:

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
       .   .   .   .   .   .
    app.UseEasyQuery(options => {
        options.Endpoint = "/api/easyquery";
        options.UseDbContext<AppDbContext>();
        options.UsePaging(25);
    });
    app.UseMvc();
}

The code above defines the following main settings:

  1. The endpoint is set to /api/easyquery. This means that all requests from EasyQuery widgets will be started with this path (like /api/easyquery/models/{modelId} to load the model). Please note that this is the default value so you can omit this setting in your app.
  2. We use our DbContext class to get the model and to execute the generated queries.
  3. The last UsePaging call is options. It turns on the pagination for long result sets and sets the page size.

So, the server-side setup is finished. Now we need to setup the client-side part.

Step 4: Add the page with the query building UI

To simplify this task EasyQuery includes a Razor UI library package (Korzh.EasyQuery.RazorUI) that contains several predefined pages which you can use in your projects. All those pages are accessible under /EasyQuery path. For example, there is an "advaced search" page which is available via /EasyQuery/AdvancedSearch.

Done!

If everything was done right you can build and run your project. Then just open /EasyQuery/AdvancedSearch and enjoy the new functionality.

eq-aspcore-razor