Queryoont 0.1.4

.NET 5.0 .NET Core 3.1
There is a newer version of this package available.
See the version list below for details.
dotnet add package Queryoont --version 0.1.4
NuGet\Install-Package Queryoont -Version 0.1.4
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Queryoont" Version="0.1.4" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Queryoont --version 0.1.4
#r "nuget: Queryoont, 0.1.4"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// Install Queryoont as a Cake Addin
#addin nuget:?package=Queryoont&version=0.1.4

// Install Queryoont as a Cake Tool
#tool nuget:?package=Queryoont&version=0.1.4

Queryoont

The Queryoont is a minimal framework to use SQL with ASP.NET Core API in a dynamic way, you can issue statement from the client using a POST request, specifying the SELECT and WHERE.

Use the QueryoontAttribute to decorate your controller action method to obtain the base SqlKata Query, next the Queryoont apply the body request filter to the query.

DISCLAIMER

This tool does not allow to specify joins and INSERT / UPDATE / DELETE statements for security reasons. Actually, you can ONLY specify the WHERE and the fields on SELECT.

Setup the Queries

Decorate the controller action method with HttpPost and QueryoontFilter

[HttpPost]
[QueryoontFilter]
public Query GetCustomer()
{
    return new Query("Customers");
}

Startup

On the startup configure the QueryBuilder class of SqlKata below is a configuration for the SqlServer.


// Configure SQL Server with SqlKata
var connectionString = Configuration.GetConnectionString("ConnectionStringName");
services.AddTransient<IDbConnection>((s) => new SqlConnection(connectionString));
services.AddTransient<QueryFactory>((s) =>
{
    var compiler = new SqlServerCompiler();
    return new QueryFactory(s.GetService<IDbConnection>(), compiler);
});

// Add Queryoont services
services.AddQueryoont();

Request Body on POST

This is a map of all possible conditions.

{
    "version": 1.0,
    // If you have more that one table use the prefix
    "select": [
        "table1.id",
        "table2.name",
        "table1.{title,note}" // SqlKata syntax
    ],
    "filter": [
        {
            "type": "WhereCondition", // This condition is translated to (Where prefix.Id > 2 OR prefix.Id < 5) between brakets
            "condition": [
                {
                    "type": "Where",
                    "field": "prefix.Id",
                    "oper": ">",
                    "value": "2"
                },
                {
                    "type": "OrWhere",
                    "field": "prefix.Id",
                    "oper": "<",
                    "value": "5"
                }
            ]
        },
        {
            "type": "Where",  // For AND condition repeat the Where
            "field": "prefix.Id",
            "oper": "<",
            "value": "2"
        },
        {
            "type": "OrWhere",
            "field": "prefix.Id",
            "oper": "<",
            "value": "2"
        },
        {
            "type": "WhereNot",
            "field": "prefix.Id",
            "oper": "<",
            "value": "2"
        },
        {
            "type": "OrWhereNot",
            "field": "prefix.Id",
            "oper": "<",
            "value": "2"
        },
        {
            "type": "WhereNull",
            "field": "prefix.Id"
        },
        {
            "type": "OrWhereNull",
            "field": "prefix.Id"
        },
        {
            "type": "WhereTrue",
            "field": "prefix.Id"
        },
        {
            "type": "WhereFalse",
            "field": "prefix.Id"
        },
        {
            "type": "WhereIn",
            "field": "prefix.Id",
            "values": [
                1,
                2,
                3,
                4
            ]
        },
        {
            "type": "WhereNotIn",
            "field": "prefix.Id",
            "values": [
                1,
                2,
                3,
                4
            ]
        }
    ]
}

Example

A simple example

CREATE TABLE Table1
(
    [ID] INT NOT NULL PRIMARY KEY,
    [NAME] VARCHAR(50),
    [EMAIL] VARCHAR(100)
)
[HttpPost]
[QueryoontFilter]
public Query GetCustomer()
{
    return new Query("table1");
}
{
    "version": 1.0,
    "select": [
        "table1.Name"
    ],
    "filter": [
        {
            "type": "Where",  
            "field": "table.Id",
            "oper": "<",
            "value": "20"
        }
    ]
}

This is translated into this SQL

SELECT 
    table1.Name
FROM
    table1
WHERE 
    table.Id < 20

And the corresponding json

[
     {
        "name": "name 1"
     },
     {
        "name": "name 2"
     }
]

Json Serialization

Adding services.AddQueryoont() configure the framework to use Json.NET to take care of Serialization and Deserialization. If you want to customize the process of Serialization/Deserialization you could implement the inserface IJsonSerializer of the framework and add it to the serviceCollection using the overload of services.AddQueryoont() or by remove the call and adding it to the serviceCollections.

Know issues with System.Text.Json

There are some issues when using System.Text.Json

Writing

If you use the System.Text.Json the default is to serialize on PascalCase the Dictionary keys (the result of SqlKata is a DapperRow that is a dynamic object that is a Dictionary<string,object>), if you want the camelCase with System.Text.Json you should configure the JsonOptions like this

services.AddControllers()
    .AddJsonOptions(o =>
        o.JsonSerializerOptions.DictionaryKeyPolicy = System.Text.Json.JsonNamingPolicy.CamelCase
    );

Reading

When deserialize object properties the System.Text.Json adds ValueKind to value property, you must take care of it See this GiHub issue

Product Versions
.NET net5.0 net5.0-windows net6.0 net6.0-android net6.0-ios net6.0-maccatalyst net6.0-macos net6.0-tvos net6.0-windows net7.0 net7.0-android net7.0-ios net7.0-maccatalyst net7.0-macos net7.0-tvos net7.0-windows
.NET Core netcoreapp3.1
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
0.3.0 150 11/9/2022
0.2.0 262 10/29/2021
0.1.4 216 10/12/2021

Initial release