Multiple where clause using C# and SqlKata query engine

Question

I am working on a small Web API which will use SqlKata in the back end as the query engine to talk to SQL Server. In the documentation, SqlKata says the following in relation to multiple where clauses:

Multiple fields If you want to filter your query against multiple fields, pass an object that represents col/values.

var query = new Query("Posts").Where(new {
    Year = 2017 ,
    CategoryId = 198 ,
    IsPublished = true,
});

My intention is to use query strings to create the WHERE clauses in the back end, but I'm a bit stuck as to how to convert the Key/Value pairs of the query strings into properties of an object to use in the SqlKata query. The request will be highly dynamic in nature, so I can't really use a static object. I am wondering if anyone has any tips on how to construct an object to satisfy these requirements, being that the properties - both the property name and/or value - can be dynamic, as could the number of properties within the object. In my head I could imagine somehow converting the key / value pairs of the query strings into an object at run-time, but I'm stuck on how to achieve that.

I did try the following, but it seems each iteration through the loop the last key/value pair is replaced, so you end up with only the most recent WHERE clause being considered:

                    if (request.QueryStringParameters != null)
                    {
                        foreach (var element in request.QueryStringParameters)
                        {
                            CreateCloudWatchLog($"query string {element.Key} value {element.Value}", context, LogLevel.Trace, environmentLogLevel);

                            if (element.Key != "limit")
                            {
                                query = query.Where(element.Key, element.Value);
                            }
                            if (element.Key == "limit")
                            {
                                query = query.Limit(Convert.ToInt32(element.Value));
                            }
                        }
                    }

I have also tried this approach (query strings => json => dynamic object) but I get the error 'parameter count mismatch' (I am testing with one parameter passed in called 'storenumber' with a value of 399)

                        var json = JsonConvert.SerializeObject(request.QueryStringParameters, Formatting.Indented);
                        CreateCloudWatchLog($"Serialised query strings = {json}", context, LogLevel.Trace, environmentLogLevel);
                        var myobject = JsonConvert.DeserializeObject<dynamic>(json);
                        query = query.Where(myobject);

Debug logs:

[Trace] Serialised query strings =
{
    "storenumber": "399"
}

[Trace] Finished converting JSON to object
[Error] Parameter count mismatch.

Answer 1

Looking at the documentation, it looks like you can use the basic where method which takes a property and a value. For example, assuming you have a dictionary of key value pairs, you could do this:

var filters = new Dictionary<string, object>
{
    { "Year", 2017 },
    { "CategoryId", 198 },
    { "IsPublished", true },
}

var query = new Query("Posts");

foreach(var filter in filters)
{
    query = query.Where(filter.Key, filter.Value);
}