How can i execute filter from our JSON filter JSON?

Question

I have a vue3 datagrid and I want to fill the data in this grid with filter by API. At the same time, I want to send the filter fields in the grid to the API as JSON and execute them according to this filter on the API side. How can I do this with AutoQuery?

[Route("/GetConnectors", "POST")]
public class GetConnectors : QueryDb<Connector>
{
    public string Id { get; set; }
    public string PageParameterJson { get; set; }
}

public class Connector 
{
    [PrimaryKey]
    [AutoIncrement]
    public long PKey { get; set; }
    public string Id { get; set; }
    public string Name { get; set; }
}

public class PageParameters
{
    public string Field { get; set; }
    public string Operand { get; set; }
    public string Value { get; set; }
    public string Type { get; set; }
}

It's an example PageParameter JSON;

[
    {
        "Field":"Name",
        "Operand":"cn"//Contains
        "Value":"test",
        "Type":"string"
    },
    {
        "Field":"Id",
        "Operand":"eq"//Equal
        "Value":"2",
        "Type":"string"
    }
]

 public async Task<object> Any(GetConnectors query)
 {
     using var db = AutoQuery.GetDb(query, base.Request);
     var filters = query.PageParameters.FromJson<List<PageParameter>>();
     //How can I execute query with for CreateQuery?
     var q = AutoQuery.CreateQuery(query, Request, db);
     var sql = q.PointToSqlString();
     return await AutoQuery.ExecuteAsync(query, q, base.Request, dbConnection);
 }

Best Regards

i can't execute dynamic filters from server-side datagrid

Answer 1

The AutoQuery.CreateQuery returns OrmLite's Typed SqlExpression which has a number of filtering options inc .Where(), .And(), .Or(), etc.

So you should be able to populate it with something like:

foreach (var filter in filters)
{
    var type = filter.Type switch
    {
        "string" => typeof(string),
        _ => throw new NotSupportedException($"Type {filterType}")
    };
    var value = filter.Value.ConvertTo(type);
    if (filter.Operand == "eq")
    {
        q.And(filter.Field + " = {0}", value)
    }
    else if (filter.Operand == "cn")
    {
        q.And(filter.Field + " LIKE {0}", $"%{value}%")
    }
    else throw new NotSupportedException(filter.Operand);
}

Note: I've rewritten API to be async as you should never block on async methods.