TranMinhSang.DynamicQueryExtension.EntityFrameworkCore 1.0.5

Dynamic Query Extension

Support

• Search
• Sort
• Filter with LHS Bracket syntax
• Pagination with offset pagination and Cursor Pagination

NOTE

⚠️ PostgreSQL unaccent requirement

If you are using PostgreSQL, you MUST enable the unaccent extension before running the application or applying migrations.

1 Enable unaccent in PostgreSQL (run first)

Run this SQL once per database:

CREATE EXTENSION IF NOT EXISTS unaccent;

2 After the extension is enabled, add the following code to your DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(
        typeof(PostgresDbFunctionExtensions).GetMethod(
            nameof(PostgresDbFunctionExtensions.Unaccent)
        )!
    );
}

The Search and Filter methods accept a DbContextProvider enum.

PostgreSQL works out of the box, but for other databases you must select the appropriate provider.

Supported providers: PostgreSQL, MySQL, and SQL Server

How to use

The First parameter is the key word The Second one is The Specific fields that You wanna search The Third is how deep this could search.

Using Database isn't PostgreSql, Pass your DbContextProvider.

dbContext.User
    .Search("rose",["name"], 1)
    .ToListAsync();

Filter

To do filter in this template, we use LHS Brackets.

LHS is the way to encode operators is the use of square brackets [] on the key name.

For example

GET api/v1/users?filter[dayOfBirth][$gt]="1990-10-01"

This example indicates filtering out users whose birthdays are after 1990/10/01

All support operations:

Some Examples:

GET /api/v1/users?filter[gender][$in][0]=1&filter[gender][$in][1]=2

GET /api/v1/users?filter[gender][$between][0]=1&filter[gender][$between][1]=2

GET /api/v1/users?filter[firstName][$contains]=abc

$and and $or operator:

GET /api/v1/users/filter[$and][0][firstName][$containsi]="sa"&filter[$and][1][lastName][$eq]="Tran"

{
  "filter": {
    "$and": {
      "firstName": "sa",
      "lastName": "Tran"
    }
  }
}

GET /api/v1/users/filter[$or][0][$and][0][claims][claimValue][$eq]=admin&filter[$or][1][lastName][$eq]=Tran

{
    "filter": {
        "$or": {
            "$and":{
                "claims": {
                    "claimValue": "admin"
                }
            },
            "lastName": "Tran"
        }
    }
}

For more examples and get better understand, you can visit

https://docs.strapi.io/dev-docs/api/rest/filters-locale-publication#filtering
https://docs.strapi.io/dev-docs/api/rest/filters-locale-publication#complex-filtering
https://docs.strapi.io/dev-docs/api/rest/filters-locale-publication#deep-filtering

I designed filter input based on Strapi filter

Using Database isn't PostgreSql, Pass your DbContextProvider as parameter to Filter method.

public string[] GetFilterQueries(string query, string filterKey)
{
    string[] queryParams = query[1..].Split("&", StringSplitOptions.TrimEntries);
    return
    [
        .. queryParams.Where(param =>
            param.StartsWith(
                filterKey,
                StringComparison.OrdinalIgnoreCase
            )
        ),
    ];
}

var query = httpContext?.Request.QueryString.Value;
string[] stringQuery = GetFilterQueries(query, "Filter");

List<QueryResult> queries =
        [
            .. StringExtension.TransformStringQuery(stringQuery),
        ];
object filter = StringExtension.Parse(queries);
await dbContext.User.Filter(filter).ToListAsync();

Sort

This example sorts users by name descending, then by age ascending when names are equal.

await dbContext.User.sort("name:desc, age").ToListAsync();

Pagination

Offset

   await dbContext.User.ToPagedListAsync(1, 10);

Cursor

• Before : The previous Cursor
• After : The next cursor
• Size : Size of page
• Sort : User sort request
• UniqueSort : the property that is unique to make the mechanism work property, the default is Id:asc

await dbContext.User.ToCursorPagedListAsync(request);