DynamicQueryBuilder

Table of Contents

1 What?

DynamicQueryBuilder(DQB) is a lightweight LINQ builder library that works dynamically with the given collection generic type.

2 Why?

The motivation behind DQB was to reverse the development cost of operations such as Filtering, Sorting, Paginating data from backend to clients. This allows client development to be more free and less time consuming for non-fullstack development workspaces.

3 Installation

You can get DQB from NuGet services with the command below or NuGet UI in visual studio.

DQB currently runs with .netstandard2.1 

Install-Package DynamicQueryBuilder

4 DynamicQueryOptions

This is the class that holds everything from filters to sorts and pagination options. DQB's has a function called ApplyFilters which converts this DynamicQueryOptions class into a LINQ expression. You can check out this object here.

4.1 Basic usage of DQB like below:

IQueryable<MyObject> myCollection = GetMyCollectionOfData();
var myOpts = new DynamicQueryOptions
{
    /*
      Filters,
      SortingOptions,
      Pagination
    */
};

IQueryable<MyObject> dqbResults = myCollection.ApplyFilters(myOpts);
return dqbResults.ToList();

5 Filters

Filters are the objects that hold your logical filters. You can see the object structure here.

5.0.1 Filter Value Conversion

Since DQB always boxes your data into an object the actual type conversion is being handled by DQB while transforming your filters into a LINQ expression. DQB also can handle null values as well.

5.0.2 Supported Filters

In,
Equals,
LessThan,
Contains,
NotEqual,
EndsWith,
StartsWith,
GreaterThan,
LessThanOrEqual,
GreaterThanOrEqual,
Any,
All

5.0.3 Supported Logical Operators

All logical operators are supported from Conditional to Bitwise. If you do not define a logical operator to a Filter, DQB will choose AndAlso as default Logical Operator.

5.0.4 Filter Examples

An example usage of Filter class with a flat object: 

var dqbOpts = new DynamicQueryOptions
{
    Filters = new List<Filter>()
    {
        new Filter
        {
            Value = "bar",
            PropertyName = "foo",
            Operator = FilterOperation.Equals
        }
    }
};

// LINQ Translation: myCollection.Where(x => x.foo == "bar");

An example usage of Filter class with a collection property: 

var dqbOpts = new DynamicQueryOptions
{
    Filters = new List<Filter>
    {
        new Filter
        {
            Value = new DynamicQueryOptions
            {
                Value = "some_value",
                Operator = FilterOperation.Equals,
                PropertyName = "bar"
            },
            Operator = FilterOperation.Any,
            PropertyName = "foo"
        }
    }
};

// LINQ Translation: myCollection.Where(x => x.foo.Any(y => y.bar == "some_value"));

An example usage of Filter class with a logical operator, combining two filters: 

var dqbOpts = new DynamicQueryOptions
{
    Filters = new List<Filter>
    {
        new Filter
        {
            Value = "123",
            PropertyName = "Fizz",
            Operator = FilterOperation.Equals,
            LogicalOperator = LogicalOperator.OrElse,
        },
        new Filter
        {
            Value = "321",
            PropertyName = "Fizz",
            Operator = FilterOperation.Equals,
        }
    }
};

// LINQ Translation: myCollection.Where(x => x.Fizz == "123" || x.Fizz == "321");

6 Sorting

Sorting is extremely easy with DQB. DQB currently does not support for custom sorting callbacks and uses default .NET's OrderBy, OrderByDescending, ThenBy and ThenByDescending functions. Sorting should be provided via SortOption class which you can check out here.

6.0.1 Sorting Examples

var dqbOpts = new DynamicQueryOptions
{
    SortOptions = new List<SortOption>()
    {
        new SortOption
        {
            SortingDirection = SortingDirection.Asc,
            PropertyName = "Foo"
        };

        new SortOption
        {
            SortingDirection = SortingDirection.Desc,
            PropertyName = "Bar"
        };
    }
};

// LINQ Translation: myCollection.OrderBy(x => x.Foo).ThenByDescending(x => x.Bar);

7 Accessing Nested Objects

DQB can access nested object with . delimeter like C# LINQ. 

public class MyNestedClass
{
    public int Age { get; set; }
}

public class MyClassToFilter
{
    public MyNestedClass MyNestedProperty { get; set; }
}

With the object structures above, we could utilize Filter and Sort operations like below:

  • Filter 

    new Filter
{
    Value = "27",
    Operator = FilterOperation.Equals,
    PropertyName = "MyNestedProperty.Age"
};

// LINQ Translation: myCollection.Where(x => x.MyNestedProperty.Age == 28);

  • Sort 

    new SortOption
{
    SortingDirection = SortingDirection.Asc,
    PropertyName = "MyNestedProperty.Age"
}

// LINQ Translation: myCollection.OrderBy(x => x.MyNestedProperty.Age);

8 Pagination

Pagination can be done by specifiynig options into the PaginationOptions member of DynamicQueryOptions class. You can check it out here. Pagination utilizes LINQ's Skip and Take functions.

8.1 Pagination Examples:

var paginationOption = new PaginationOption
{
    Count = 10,
    Offset = 0,
    AssignDataSetCount = true
};

// LINQ Translation: myCollection.Skip(0).Take(10);

8.1.1 How to access the filtered count of the query

if its required to access the total query result amount(whole set) you can access it via 

int totalDataSetCount = paginationOption.DataSetCount;

9 Web Development with DQB

Web development is actually where DQB shines the most. DQB comes with an ActionFilter that can parse HTTP queries into DynamicQueryOptions class.

9.1 Setting up DynamicQueryBuilderSettings

This is a singleton object that can hold static configurations for DQB like operation shortcodes, query resolution methods and data source case sensitivity. You can check out this object here.

It is usually best to create an instance of this class in your Web Projects Startup.cs and inject it as a singleton like below 

public class Startup
{
    public Startup(ILogger<Startup> logger, IConfiguration configuration)
    {
        this.Logger = logger;
        this.Configuration = configuration;
    }

    public ILogger<Startup> Logger { get; }

    public IConfiguration Configuration { get; }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        // .. other stuff

        var dqbSettings = new DynamicQueryBuilderSettings
        {
            // .. your settings(explained below)
        };

        services.AddSingleton(dqbSettings);

        // .. other stuff
    }
}

9.2 Query Delivery Methods

DQB can retrieve your encoded/non-encoded queries via options like below:

  • Request QueryString

Below, there is an example of configuring DQB to retrieve queries from query string 

string parameterToResolveFrom = "myparamtoresolve";
Func<string, string> decodeFunction = (encodedQuery) => magicDecode(encodedQuery);

new DynamicQueryBuilderSettings
{
    // Other configurations
    QueryOptionsResolver = new QueryStringResolver(parameterToResolveFrom, decodeFunction)
}

Tip: you can leave parameterToResolveFrom null to resolve your queries directly from the raw querystring.

  • Request HTTP Header

    Below, there is an example of configuring DQB to retrieve queries from HTTP Headers 

string httpHeaderName = "myhttpheadername";
Func<string, string> decodeFunction = (encodedQuery) => magicDecode(encodedQuery);

new DynamicQueryBuilderSettings
{
    // Other configurations
    QueryOptionsResolver = new HttpHeaderResolver(httpHeaderName, decodeFunction)
}

Tip: you can always leave decodeFunction null if your queries are not encoded.

9.3 HTTP Parameters

9.3.1 o Parameter

  • Refers to FilterOperation and LogicalOperator properties of Filter class.
  • Parameter formation should be FilterOperation|LogicalOperator using | as a delimeter between properties.
  • This parameter can be placed anywhere in the querystring.
  • This parameter should be forming a triplet with p and v parameters.

9.3.2 p Parameter

  • Refers to the PropertyName property of Filter class.
  • This parameter should be placed after the o parameter..
  • This parameter should be forming a triplet with o and v parameters.

9.3.3 v Parameter

  • Refers to the PropertyValue property of Filter class.
  • This parameter should be placed after the p parameter..
  • This parameter should be forming a triplet with o and p parameters.

9.3.4 s Parameter

  • Refers to the SortOption class.
  • This parameter can be placed anywhere in the querystring.
  • If this parameter occurs more than once, sorting will be done in the given order.

9.3.5 offset Parameter

  • Refers to the Offset property of PaginationOption class.
  • This parameter can be placed anywhere in the querystring.
  • If this parameter occurs more than once, the first occurence will be assigned.

9.3.6 count Parameter

  • Refers to the Count property of PaginationOption class.
  • This parameter can be placed anywhere in the querystring.
  • If this parameter occurs more than once, the first occurence will be assigned.

9.4 HTTP Query Examples

  • Valid Example: ?o=Equals&p=foo&v=bar

will be transformed into: 

var filter = new Filter
{
    Operator = FilterOperation.Equals,
    PropertyName = "foo",
    Value = "bar"
};

// LINQ Translation: myCollection.Where(x => x.foo == "bar");

or to apply multiple filters

  • Valid Example: ?o=Equals&p=foo&v=bar&o=Equals&p=fizz&v=buzz Since this query does not provide a logical operator, parser will choose AndAlso which is the default logical operator.

will be transformed into: 

var filterOne = new Filter
{
    Operator = FilterOperation.Equals,
    PropertyName = "foo",
    Value = "bar"
};

var filterTwo = new Filter
{
    Operator = FilterOperation.Equals,
    PropertyName = "fizz",
    Value = "buzz"
};

// LINQ Translation: myCollection.Where(x => x.foo == "bar" && x.fizz == "buzz");
  • Valid Example: ?o=Equals|OrElse&p=foo&v=bar&o=Equals&p=fizz&v=buzz

will be transformed into: 

var filterOne = new Filter
{
    LogicalOperator = LogicalOperation.OrElse,
    Operator = FilterOperation.Equals,
    PropertyName = "foo",
    Value = "bar"
};

var filterTwo = new Filter
{
    Operator = FilterOperation.Equals,
    PropertyName = "fizz",
    Value = "buzz"
};

// LINQ Translation: myCollection.Where(x => x.foo == "bar" || x.fizz == "buzz");
  • Valid Example with ascending sort and pagination: ?o=Equals&p=foo&v=bar&s=foo,asc&offset=0&count=10

DynamicQueryOptions Transform: 

var filter = new Filter
{
    Operator = FilterOperation.Equals,
    PropertyName = "foo",
    Value = "bar"
};

var sort = new SortOption
{
    PropertyName = "foo",
    SortingDirection = SortingDirection.Asc
};

var pagination = new PaginationOption
{
    Offset = 0,
    Count = 10
};

/* LINQ Translation:

myCollection.Where(x => x.foo == "bar")
            .OrderBy(ord => ord.foo)
            .Skip(0)
            .Take(10);

*/
  • Valid Example of Collection Member Querying ?o=any&p=foo&v=(o=Equals&p=fizz&v=buzz)

will be transformed into: 

var filter = new Filter
{
    Operator = FilterOperation.Any,
    PropertyName = "foo",
    Value = new DynamicQueryOptions
    {
        Operator = FilterOperation.Equals,
        PropertyName = "fizz",
        Value = "buzz"
    }
};

// LINQ Translation: myCollection.Where(x => x.foo.Any(y => y.fizz == "buzz"));
  • Valid Example with pagination: ?offset=0&count=10
  • Valid Example with descending sort: ?o=Equals&p=foo&v=bar&s=foo,desc
  • Valid Descending Sort Example without any filters: ?s=foo,desc

Tip: if you do not provide any sorting direction, DynamicQueryBuilder will sort the data in ascending order.

  • Valid Example with ascending sort without stating the direction: ?o=Equals&p=foo&v=bar&s=foo

9.5 Operation Shortcodes

DQB has default operation short codes for shorter HTTP queries which are below; 

{ "eq", FilterOperation.Equals },
{ "lt", FilterOperation.LessThan },
{ "cts", FilterOperation.Contains },
{ "ne", FilterOperation.NotEqual },
{ "ew", FilterOperation.EndsWith },
{ "sw", FilterOperation.StartsWith },
{ "gt", FilterOperation.GreaterThan },
{ "ltoe", FilterOperation.LessThanOrEqual },
{ "gtoe", FilterOperation.GreaterThanOrEqual }
{ "any", FilterOperation.Any }
{ "all", FilterOperation.All }

9.5.1 Custom Operation Shortcodes

You can change any operation shortcode to whatever you want in DynamicQueryBuilderSettings object's CustomOpCodes member like below. 

var mySettings = new DynamicQueryBuilderSettings
{
    CustomOpCodes = new CustomOpCodes
    {
        { "my_eq", FilterOperation.Equals },
        { "my_lt", FilterOperation.LessThan },
        { "my_cts", FilterOperation.Contains },
        { "my_ne", FilterOperation.NotEqual },
        { "my_ew", FilterOperation.EndsWith },
        { "my_sw", FilterOperation.StartsWith },
        { "my_gt", FilterOperation.GreaterThan },
        { "my_ltoe", FilterOperation.LessThanOrEqual },
        { "my_gtoe", FilterOperation.GreaterThanOrEqual },
        { "my_any", FilterOperation.Any },
        { "my_all", FilterOperation.All },
    }
};

9.6 Web Action Examples

DynamicQueryAttribute is the handler for parsing the querystring into DynamicQueryOptions class and has 3 optional parameters. 

DynamicQueryAttribute(
    // Declares the max page result count for the endpoint.
int maxCountSize = 100,
    // Declares the switch for inclusion of total data set count to *PaginationOptions* class.
bool includeDataSetCountToPagination = true,
    // Declares the behaviour when the requested page size exceeds the assigned maximum count.
PaginationBehaviour exceededPaginationCountBehaviour = PaginationBehaviour.GetMax,
    // Resolves the dynamic query string from the given query parameter value.
string resolveFromParameter = "")

  • The ResolveFromParameter

    This argument exists because some API's would want to send their queries inside of a HTTP parameter like below: 

https://foobar.com/results?dqb=%3Fo%3Deq%26p%3Dfoo%26v%3Dbar

So, you can set this parameter specifically for an endpoint with the DynamicQueryAttribute or you can set it in DynamicQueryBuilderSettings globally with QueryResolvers. Check out Query Delivery Methods.

  • PaginationBehaviour enum
public enum PaginationBehaviour
{
    // DynamicQueryBuilder will return maxCountSize of results if the *Count* property exceeds *maxCountSize*.
    GetMax,
    // DynamicQueryBuilder will throw MaximumResultSetExceededException if the *Count* property exceeds *maxCountSize*.
    Throw
}
  • Example with no pagination specified(default pagination options will be applied).
[DynamicQuery]
[HttpGet("getMyDataSet")]
public IActionResult Get(DynamicQueryOptions filter)
{
    IEnumerable<MyObject> myDataSet = _myRepository.GetMyObjectList();
    return Ok(myDataSet.ApplyFilters(filter));
}
  • Example with default pagination options for the endpoint specified.
[HttpGet("getMyDataSet")]
[DynamicQuery(maxCountSize: 101, includeDataSetCountToPagination: true, exceededPaginationCountBehaviour: PaginationBehaviour.GetMax)]
public IActionResult Get(DynamicQueryOptions filter)
{
    IEnumerable<MyObject> myDataSet = _myRepository.GetMyObjectList();
    return Ok(myDataSet.ApplyFilters(filter));
}

Author: Cem YILMAZ <cmylmzbm@outlook.com>

Created: 2021-01-26 Tue 11:10

Validate