Dynamic Query Builder for ASP.Net-MVC - Using jQuery QueryBuilder, DataTables, and dynamic-linq-query-builder

Question

I'm trying to accomplish the following Tasks:

1. Create a visual expression/ query builder for ASP.NET-MVC.
2. Pass the resulting query to DataTables.

This question is about Task 1 as that's where I'm stuck. I have posted Task 2 to provide more background information.

To achieve Task 1 I'm using the jQuery QueryBuilder - a jQuery plugin to create user friendly queries. On the QueryBuilder website there is a listing for .NET under the Backends section (https://querybuilder.js.org/#backends). They recommend to use the dynamic-linq-query-builder by Castle-it (https://github.com/castle-it/dynamic-linq-query-builder).

Here is my issue:

The dynamic-linq-query-builder all seems to be built with static classes. I want to retrieve the data from my database but from my research online I'm not able to initiate a dbcontext within a static class.

Dynamic-linq-query provides a PersonBuilder class to deserialize a JSON data and they include a TestData string:

 public static class PersonBuilder
            {
                public static List<PersonRecord> GetPeople()
                {
                    var result = new List<PersonRecord>();

                    var testData = TestData;

                    var personRecords = JsonConvert.DeserializeObject<List<PersonRecord>>(testData);

                    return personRecords;

                }

                private static string TestData
                {
                    get
                    {
                        return @"
                                [
            {
                ""FirstName"": ""Jane"",

                ""LastName"": ""Hansen"",
                ""Birthday"": ""1969-12-31T16:00:00-08:00"",
                ""Address"": ""P.O. Box 492, 4607 Tempus, Rd."",
                ""City"": ""Polatlı"",
                ""State"": ""Ankara"",
             ...
             ...
             ...

Then in the HomeController they are using the following to filter the query:

[HttpPost]
public JsonResult Index(FilterRule obj)
{
    var jsonSerializerSettings = new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver() };
    var people = PersonBuilder.GetPeople().BuildQuery(obj).ToList();

    return Json(people);


}

And here is their QueryBuilder implementation and jQuery logic to read out the results.

  <script type="text/javascript">
    $(function() {
        // Handler for .ready() called.
        var tableData = [];

        var filterDefinition = @Html.Raw(ViewBag.FilterDefinition);
        var customFilters = {
            condition: 'AND',
            rules: []
        };
        var jqueryQueryBuilder = $('#jquery-query-builder');
        var jqueryQueryBuilderDom = jqueryQueryBuilder.queryBuilder({
            plugins: ['bt-tooltip-errors', 'filter-description'],
            //allow_groups: 0,
            allow_empty: true,
            filters: filterDefinition,
            rules: customFilters,
            icons: {
                add_group: 'fa fa-plus-square',
                add_rule: 'fa fa-plus-circle',
                remove_group: 'fa fa-minus-square',
                remove_rule: 'fa fa-minus-circle',
                error: 'fa fa-exclamation-triangle',
                sortable: 'fa fa-exclamation-triangle'
            }
        });

        var convertArraysToCommaDelimited = function(obj) {
            if (obj != null) {
                if (obj.hasOwnProperty("value")) {
                    if( Object.prototype.toString.call( obj.value ) === '[object Array]' ) {
                        obj.value = obj.value.join(", ");
                    }
                }
                if (obj.hasOwnProperty("rules") && obj.rules != null) {
                    for (var i = 0; i < obj.rules.length; i++) {
                        convertArraysToCommaDelimited(obj.rules[i]);
                    }
                }
            }
        }
        var getRules = function() {
            try {
                var res =  jqueryQueryBuilder.queryBuilder('getRules');
                convertArraysToCommaDelimited(res);
                return res;
            } catch (ex) {
                //console.log(ex);
                return null;
            }
        }

        var buildTable;
        var filterData = function() {

            $.ajax({
                type: 'POST',
                url: "../Home/Index",
                data: JSON.stringify(getRules()),
                success: function (returnPayload) {
                    tableData = returnPayload;
                    buildTable();
                    console && console.log ("request succeeded");
                },
                error: function (xhr, ajaxOptions, thrownError) {
                    console && console.log ("request failed");
                },
                dataType: "json",
                contentType: "application/json",            
                processData: false,
                async: true
            });
        }



        $('#applyFilter').on('click', function() {
            filterData();
        });
        buildTable = function() {
            var tbody = $('#data-table tbody'),
                props = ["FirstName", "LastName", "Birthday", "Age", "Address", "City", "State", "ZipCode"];
            tbody.empty();
            $.each(tableData, function(i, reservation) {
                var tr = $('<tr>');
                $.each(props, function(i, prop) {
                    $('<td>').html(reservation[prop]).appendTo(tr);  
                });
                tbody.append(tr);
            });
        };

        filterData();

    });

</script>

You'll notice that they've created a buildTable function. Later I would like to replace this with a DataTable implementation.

What I've tried:

I have tried to initiate a dbcontext with LINQ in the PersonBuilder class. The issue is that this class was static. I simply removed the static definition of the PersonBuilder class. Here is my implementation:

public List<PersonRecord> GetPeople()
        {
            IQueryable<PersonRecord> query = DbContext.PersonRecord;


            var data = query.Select(asset => new Asset
            {
                data1 = PersonRecord.data1,
                data2 = PersonRecord.data2,
                ...
                ...

            }).ToList();

            return data;
        }

The issue I'm experiencing is that the HomeController is now throwing the following error:

CS0120: An object reference is required for the nonstatic field, method, or property 'member'

At the following line:

var people = PersonBuilder.GetPeople().BuildQuery(obj).ToList();

Not quite sure how to get around this since it seems the entire library is built with static classes?

What do you guys think?

Answer 1

The main problem is that you're defining GetPeople() as non-static method inside PersonBuilder class which marked as static. As noted in MSDN documentation, static classes must contain only static members, including static methods (see the reason here).

The CS0120 error indicates that you should use either static class with static method, or non-static class' instantiated constructor as object. If you want to use non-static method, the class should not marked as static, also the class constructor must be instantiated first before the method can be accessed:

public class PersonBuilder
{
    public List<PersonRecord> GetPeople()
    {
        IQueryable<PersonRecord> query = DbContext.PersonRecord;

        var data = query.Select(asset => new Asset
        {
           data1 = PersonRecord.data1,
           data2 = PersonRecord.data2,
           // other stuff

        }).ToList();

        return data;
    }
}

Usage

[HttpPost]
public JsonResult Index(FilterRule obj)
{
    var jsonSerializerSettings = new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver() };
    var personBuilder = new PersonBuilder(); // add constructor initialization first
    var people = personBuilder.GetPeople().BuildQuery(obj).ToList();

    return Json(people);
}

Related issue:

How shouldi avoid the CS0120 error in my code?