Dynamically Build A Sql Where Clause From C# Objects

I am creating filters in my HTML table binded with asp.net web API using HTTP POST request and using this jquery querybuilder to create filters. { 'condition': 'AND', 'rules': [

Solution 1:

You can use Dynamic Linq Query Builder

Some example: We are passing rules from jquery querybuilder, than Build query and retrieve relevant data from DB

    [HttpPost]
    public ActionResult Applay(FilterRule obj)
    {
        var messages = context.messages.BuildQuery(obj).ToList();
        return JsonContent(messages);
    }

Update:

obj is already deserialized json with filter rules (from jquery querybuilder) context.messages - is part of Entity Framework context

(DbSet<Message> Messages { get; set; })

In this approach you are building sql query by means of linq and EF translates it to SQL. If you are using ADO.NET than you can try to find relevant example on git hib project page.

Solution 2:

On the QueryBuilder site, the plugins section includes one that can emit SQL from the JavaScript library -

http://querybuilder.js.org/plugins.html

Would that help or do you want to receive json in c# and translate it manually (or with a NuGet package, though I'm not aware of any)? There may be security concerns with passing a SQL clause from the client code to the server to execute..