DataEngine for .NET Standard | ComponentOne
Walkthrough / Creating Queries using QueryFactory
In This Topic
    Creating Queries using QueryFactory
    In This Topic

    This walkthrough depicts a scenario where the user is provided with a user interface to select the table, table fields and operators, which would be used to create the query and display the query results using the client side controls. Note that the C1DataEngine library includes a class named QueryFactory, which provides methods to create a C1DataEngine Query from JSON string or RuntimeQuery objects. This is helpful when we need to choose the query fields and operators at runtime. The selected values can be used to create the query using the QueryFactory class.

    The GIF below depicts the DataEngine in action while extracting the count of animals (species-wise) from a dataset of Pet Licenses issued for the city of Seattle.

    query execute

    To learn how to create queries using QueryFactory in detail, follow the steps given below:

    1. Create a new ASP.NET Core Web Application using C#.NEt.
    2. Make sure that the project is configured with the following settings:


    3. Install C1.DataEngine and C1.DataEngine.Api packages using the Nuget Package manager.
    4. Generate the license key for the application using the following link:
    5. Apply the generated license to the application. Here is the sample snippet:

      C1.DataEngine.LicenseManager.Key = “XXXXXX”;

    6. Add the json files from where the DataEngine will fetch the data. For this sample, you can copy paste the two json files namely “seattle-pet-licenses.json” and “washington-zip-codes.json” that can be found in the “DataEngineDesigner” product sample located at the following location: "Documents\ComponentOne Samples\DataEngine\CS\DataEngineDesigner".
    7. Create two new classes namely “PetLicenses” and “Locations”. These classes are created to read the appropriate data from the json files to create the tables in the DataEngine. Add the following code to Startup.cs file for defining the classes:
      public class PetLicense
          [JsonProperty("License Issue Date")]
          public DateTime IssueDate { get; set; }
          [JsonProperty("License Number")]
          public string LicenseNumber { get; set; }
          [JsonProperty("Animal's Name")]
          public string AnimalName { get; set; }
          public string Species { get; set; }
          [JsonProperty("Primary Breed")]
          public string PrimaryBreed { get; set; }
          [JsonProperty("Secondary Breed")]
          public string SecondaryBreed { get; set; }
          [JsonProperty("ZIP Code")]
          public int? ZipCode { get; set; }
      public class Location
          public int Zip { get; set; }
          public string City { get; set; }
          public string County { get; set; }
      public class ZipCodeConverter : JsonConverter
          public override bool CanConvert(Type objectType)
              return objectType == typeof(string);
          public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
              throw new NotImplementedException();
          public override Object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
              string zip = (string)reader.Value;
              if (zip.Length < 5) return null;
              return Convert.ToInt32(zip.Substring(0, 5));
    8. Replace the “Main” method in the Program.cs file with the following code used to set up the DataEngine Workspace and populate it with the tables created using the included json files:
      private static Workspace workspace;
      public static Workspace Workspace
          get { return workspace; }
      public static void Main(string[] args)
          // Apply license key
          C1.DataEngine.LicenseManager.Key = License.Key;
          // Create and initialize a new workspace folder relative to the project root
          workspace = new Workspace();
          workspace.KeepFiles = KeepFileType.Results;
          // Uncomment the following line to clear the workspace before each run
          // workspace.Clear();            
          // Import the main license table
          if (!workspace.TableExists("PetLicenses"))
              List<PetLicense> collection1 = JsonConvert.DeserializeObject<List<PetLicense>>(System.IO.File.ReadAllText("seattle-pet-licenses.json"));
              ObjectConnector<PetLicense> connector = new ObjectConnector<PetLicense>(workspace, collection1);
          // Import the secondary location table
          if (!workspace.TableExists("Locations"))
              List<Location> collection2 = JsonConvert.DeserializeObject<List<Location>>(System.IO.File.ReadAllText("washington-zip-codes.json"));
              ObjectConnector<Location> connector = new ObjectConnector<Location>(workspace, collection2);
    9. Add a new razor page named “DemoQuery” to the project.
    10. Design the UI of the application by adding the following HTML code to the DemoQuery.cshtml file:
      <form method="post"></form>
      <label>Select table</label><div id="theTables" style="margin-left:5px;"></div>
      <label>Enter Query name</label><input type="text" id="queryName" style="height:30px; margin-left:5px; margin-top:5px;" />
      <label>Choose Fields</label>
      <div id="fieldOperatorGrid"></div>
      <button onclick="ExecuteQuery()" style="margin-bottom:5px; margin-top:5px;">Execute Query</button>
      <button onclick="FetchResults()" style="margin-bottom:5px; margin-top:5px;">Fetch Results</button>
      <div id="gsFlexGrid" style="height:450px;"></div>
    11. The FlexGrid cells should display the list of fields and query operators for the user to select the query fields and operators. The default FlexGrid cell editor, that is a textbox must be replaced with ComboBox to display the list of fields and operators.
      var CustomGridEditor = /** @class */ (function () {
           * Initializes a new instance of a CustomGridEditor.
          function CustomGridEditor(flex, binding, edtClass, options) {
              var _this = this;
              // save references
              this._grid = flex;
              this._col = flex.columns.getColumn(binding);
              // create editor
              this._ctl = new edtClass(document.createElement('div'), options);
              // connect grid events
              flex.beginningEdit.addHandler(this._beginningEdit, this);
              flex.sortingColumn.addHandler(function () {
              flex.scrollPositionChanged.addHandler(function () {
                  if (_this._ctl.containsFocus()) {
              flex.selectionChanging.addHandler(function (s, e) {
                  if (e.row != s.selection.row) {
              // connect editor events
              this._ctl.addEventListener(this._ctl.hostElement, 'keydown', function (e) {
                  switch (e.keyCode) {
                      case wijmo.Key.Tab:
                      case wijmo.Key.Enter:
                          e.preventDefault(); // TFS 255685
                          // forward event to the grid so it will move the selection
                          var evt = document.createEvent('HTMLEvents');
                          evt.initEvent('keydown', true, true);
                          'altKey,metaKey,ctrlKey,shiftKey,keyCode'.split(',').forEach(function (prop) {
                              evt[prop] = e[prop];
                      case wijmo.Key.Escape:
              // close the editor when it loses focus
              this._ctl.lostFocus.addHandler(function () {
                  setTimeout(function () {
                      if (!_this._ctl.containsFocus()) {
                          _this._closeEditor(true); // apply edits and close editor
                          _this._grid.onLostFocus(); // commit item edits if the grid lost focus
              // commit edits when grid loses focus
              this._grid.lostFocus.addHandler(function () {
                  setTimeout(function () {
                      if (!_this._grid.containsFocus() && !CustomGridEditor._isEditing) {
              // open drop-down on f4/alt-down
              this._grid.addEventListener(this._grid.hostElement, 'keydown', function (e) {
                  // open drop-down on f4/alt-down
                  _this._openDropDown = false;
                  if (e.keyCode == wijmo.Key.F4 ||
                      (e.altKey && (e.keyCode == wijmo.Key.Down || e.keyCode == wijmo.Key.Up))) {
                      var colIndex = _this._grid.selection.col;
                      if (colIndex > -1 && _this._grid.columns[colIndex] == _this._col) {
                          _this._openDropDown = true;
                  // commit edits on Enter (in case we're at the last row, TFS 268944)
                  if (e.keyCode == wijmo.Key.Enter) {
              }, true);
              // close editor when user resizes the window (unless it's a touch)
              window.addEventListener('resize', function () {
                  if (_this._ctl.containsFocus() && !_this._ctl.isTouching) {
          Object.defineProperty(CustomGridEditor.prototype, "control", {
              // gets an instance of the control being hosted by this grid editor
              get: function () {
                  return this._ctl;
              enumerable: true,
              configurable: true
          // handle the grid's beginningEdit event by canceling the built-in editor,
          // initializing the custom editor and giving it the focus.
          CustomGridEditor.prototype._beginningEdit = function (grid, args) {
              var _this = this;
              // check that this is our column
              if (grid.columns[args.col] != this._col) {
              // check that this is not the Delete key
              // (which is used to clear cells and should not be messed with)
              var evt =;
              if (evt && evt.keyCode == wijmo.Key.Delete) {
              // cancel built-in editor
              args.cancel = true;
              // save cell being edited
              this._rng = args.range;
              CustomGridEditor._isEditing = true;
              // initialize editor host
              var rcCell = grid.getCellBoundingRect(args.row, args.col), rcBody = document.body.getBoundingClientRect(), ptOffset = new wijmo.Point(-rcBody.left,, zIndex = (args.row < grid.frozenRows || args.col < grid.frozenColumns) ? '3' : '';
              wijmo.setCss(this._ctl.hostElement, {
                  position: 'absolute',
                  left: rcCell.left - 1 + ptOffset.x,
                  top: - 1 + ptOffset.y,
                  width: rcCell.width + 1,
                  height: grid.rows[args.row].renderHeight + 1,
                  borderRadius: '0px',
                  zIndex: zIndex,
              // initialize editor content
              if (!wijmo.isUndefined(this._ctl['text'])) {
                  this._ctl['text'] = grid.getCellData(this._rng.row, this._rng.col, true);
              else {
                  throw 'Can\'t set editor value/text...';
              // start editing item
              var ecv = grid.editableCollectionView, item = grid.rows[args.row].dataItem;
              if (ecv && item && item != ecv.currentEditItem) {
                  setTimeout(function () {
                  }, 50); // wait for the grid to commit edits after losing focus
              // activate editor
              setTimeout(function () {
                  // get the key that triggered the editor
                  var key = (evt && evt.charCode > 32)
                      ? String.fromCharCode(evt.charCode)
                      : null;
                  // get input element in the control
                  var input = _this._ctl.hostElement.querySelector('input');
                  // send key to editor
                  if (input) {
                      if (key) {
                          input.value = key;
                          wijmo.setSelectionRange(input, key.length, key.length);
                          var evtInput = document.createEvent('HTMLEvents');
                          evtInput.initEvent('input', true, false);
                      else {
                  // give the control focus
                  if (!input && !_this._openDropDown) {
                  // open drop-down on F4/alt-down
                  if (_this._openDropDown && _this._ctl instanceof wijmo.input.DropDown) {
                      _this._ctl.isDroppedDown = true;
              }, 50);
          // close the custom editor, optionally saving the edits back to the grid
          CustomGridEditor.prototype._closeEditor = function (saveEdits) {
              if (this._rng) {
                  var grid = this._grid, ctl = this._ctl, host = ctl.hostElement;
                  // raise grid's cellEditEnding event
                  var e = new wijmo.grid.CellEditEndingEventArgs(grid.cells, this._rng);
                  // save editor value into grid
                  if (saveEdits) {
                      ctl.onLostFocus(); // commit any pending changes (TFS 323317)
                      if (!wijmo.isUndefined(ctl['value'])) {
                          this._grid.setCellData(this._rng.row, this._rng.col, ctl['value']);
                      else if (!wijmo.isUndefined(ctl['text'])) {
                          this._grid.setCellData(this._rng.row, this._rng.col, ctl['text']);
                      else {
                          throw 'Can\'t get editor value/text...';
                  // close editor and remove it from the DOM
                  if (ctl instanceof wijmo.input.DropDown) {
                      ctl.isDroppedDown = false;
                  this._rng = null;
                  CustomGridEditor._isEditing = false;
                  // raise grid's cellEditEnded event
          // commit row edits, fire row edit end events (TFS 339615)
          CustomGridEditor.prototype._commitRowEdits = function () {
              var grid = this._grid, ecv = grid.editableCollectionView;
              if (ecv && ecv.currentEditItem) {
                  var e = new wijmo.grid.CellEditEndingEventArgs(grid.cells, grid.selection);
                  setTimeout(function () {
          return CustomGridEditor;

      To accomplish the same, add a custom js file named “CustomGridEditor” to the js folder found under the wwwroot folder in the project’s root folder. Refer the code snippet below:

      <script type="text/javascript" src="~/js/CustomGridEditor.js"></script>

    12. Reference the newly created js file in the DemoQuery razor page:

      <script type="text/javascript" src="~/js/CustomGridEditor.js"></script>

    13. Further, reference the Wijmo control library used to display the DataEngine query results, by adding the following scripts:
    14. Add the following javascript code to DemoQuery.cshtml file, to perform the following operations:
      • The handler defined for the window onload event initializes and sets up the two FlexGrid controls, one will be used to display the table fields and operators, while the other would be used to display the query results.
      • The “ExecuteQuery” method formulates the query data based on the user selection in the FlexGrid as a json string and makes an Ajax call to the server side “OnPostAsync” method. This method would be used to create and execute the query.
      • The “FetchResults” method has been defined to fetch the query results from the server side and display in the FlexGrid control on the client side.
        <script type="text/javascript">
            var grid, theTables, PetLicenses_Fields, Locations_Fields, flex, Operators, fieldCombo;
            window.onload = function () {
                PetLicenses_Fields = ["AnimalName", "IssueDate", "LicenseNumber", "PrimaryBreed", "SecondaryBreed", "Species", "ZipCode"];
                Locations_Fields = ["City", "County", "Zip"];
                Operators = ["Sum", "Avg", "First", "Last", "Count", "Max", "Min", "VarP", "Var", "StdP", "Std"];
                //Populate combobox with tables list
                theTables = new wijmo.input.ComboBox('#theTables', {
                    itemsSource: ["PetLicenses", "Locations"],
                    selectedIndexChanged: function () {
                        if (theTables.selectedValue == "PetLicenses") {
                            fieldCombo._ctl.itemsSource = PetLicenses_Fields;
                        else if (theTables.selectedValue == "Locations") {
                            fieldCombo._ctl.itemsSource = Locations_Fields
                //create FlexGrid to load fields and operators
                flex = new wijmo.grid.FlexGrid('#fieldOperatorGrid', {
                    autoGenerateColumns: false,
                    itemsSource: getData(7),
                    columns: [
                        { header: 'Fields', binding: 'fieldname', width: '.4*' },
                        { header: 'Operator', binding: 'op', width: '*' },
                //custom editor to choose fields
                fieldCombo = new CustomGridEditor(flex, 'fieldname', wijmo.input.ComboBox, {
                    itemsSource: PetLicenses_Fields
                //custom editor to choose operator
                new CustomGridEditor(flex, 'op', wijmo.input.ComboBox, {
                    itemsSource: Operators
                //Add FlexGrid to display query results
                grid = new wijmo.grid.FlexGrid('#gsFlexGrid');
            //Add empty rows to field/operator gFlexGrid
            function getData(fieldCount) {
                var data = [];
                for (var i = 0; i < fieldCount; i++) {
                        fieldname: "",
                        op: null
                return data;
            //Ajax call to execute query on server side
            function ExecuteQuery() {
                var queryCols = [];
                for (var r = 0; r < flex.rows.length; r++) {
                    if (flex.getCellData(r, 0) != "") {
                        queryCols.push({ names: [theTables.selectedValue + "." + flex.getCellData(r, 0)], op: flex.getCellData(r, 1), alias: null })
                var testQuery = {
                    "name": $("#queryName").val(),
                    "tables": [theTables.selectedValue],
                    "columns": queryCols,
                    "range": [],
                    "joins": []
                    url: '/DemoQuery',
                    type: 'POST',
                    contentType: 'application/json; charset=utf-8',
                    headers: {
                    success: function (response) {
                        alert("Query executed successfully");
                    data: JSON.stringify(testQuery)
            //Ajax call to fetch query results
            function FetchResults() {
                    url: '/DemoQuery?handler=Data',
                    type: 'GET',
                    data: {
                        queryname: $("#queryName").val()
                    success: function (response) {
                        grid.itemsSource = response;
                    error: function (error) {
                        console.log("error: " + error);
    15. Define the following methods in the DemoQuery.cshtml.cs file to accomplish the server side DataEngine operations:

      The OnGetAsync method will be used to fetch the query results and send the same back to the client side as a JSON response:

      //Return the query results
      public async Task<JsonResult> OnGetAsync(string queryname)
          JsonResult results = null;
          await Task.Run(() =>
              if (queryname != null)
                  if (Program.Workspace.QueryExists(queryname))
                      string className = "TestClass" + queryname;
                      IDataList test = Program.Workspace.GetQueryData(queryname);                        
                      var list = ClassFactory.CreateFromDataList(test, className);
                      results = new JsonResult(list);
          return results;

      The OnPostAsync method will be used to handle the query data send from the client side. The JSON query object is handled as a RuntimeQuery object on the server side. The method would create the query on the server side and execute the same to create the result tables.

      //Fetch query json to create and execute RuntimeQuery
      public async Task<IActionResult> OnPostAsync([FromBody]RuntimeQuery query)
              await Task.Run(() =>
                  //The CreateQueryFromRuntimeQuery method of the QueryFactory class 
                  //is used to create the query
                  dynamic test = QueryFactory.CreateQueryFromRuntimeQuery(Program.Workspace, query);
              return StatusCode(200);
          catch (Exception e)
              return StatusCode(500, e.ToString());
    16. Configure the “DemoQuery” razor page as the start page for the application. Save and execute the project to see the DataEngine in action.