Click here to Skip to main content
Click here to Skip to main content

Membership+ Management System, Part III: Query Intelligence

, 27 May 2014 Apache
Rate this:
Please Sign up or sign in to vote.
Implementing a unified structured query system of service based relational data source with build-in intelligence.

GitHub hosted project:

Related Articles:

Note: Each article in the present series could has different data schema for the data service. For historic reasons, they live on different source control branches. Therefore the ones in earlier articles may not be synchronized with later ones. Care should be taken when one tries to re-use data services and sample data in other articles.

Note: (2014-05-06) The data service now run under .Net 4.5.1 and the Asp.Net Mvc 5 that contains significant expansion and improvements. One should replace the old one by the new one. The web application is upgraded to run under the latest libraries. Many new data sets are added to support future scaleout capable SignalR push notification features.

Contents

Sample Image

1. Introduction ^

After initiating and setting up some ground work for the development in articles I (see here) and II (see here), we are at a suitable position to describe one of the ways to meet one of the main challenges for a sufficiently large system, namely, how to effectively find and organize information in the system using query capabilities of the back-end data service.

This article is a little long. It is hoped that the table of content can help a reader to locate interested information.

2. Background ^

There are many ways to organize information in a structured system. From the more static ones like hierarchical file or directory systems, to the more dynamical ones like a search system. For members, the Membership+ system also supports member social networking which is another way that can be used to find humans more efficiently. The present article is dedicated to the search system while leaving other means to future articles.

Almost all management systems and sufficiently none-trivial content presentation systems are structured. In some sense, structuralization is a pre-condition for computation. A structured system, like the Membership+ system of interest here, contains highly structured information that can be formalized (in terms of data schema) in addition to unstructured ones like web pages, text based documents, etc., to represent and encode a certain domain of knowledge which is believed to be applicable to all problems that the system is designed to solve. For example, the Membership+ system is created to handle tasks of managing members and member interactions in an organization, most of its general elements and their relationship with each other are well known and can be formalized into structures. Due to the said structure, more efficient and accurate lookup of information can be achieved using certain form of structured query (SQ) derived from what's already known.

Structured systems most likely contain non-structured information due to many reasons. This is because the more formalization a system is, the more complex, expensive to (properly) analyse, build and maintain and the more inflexible and/or even presumptuous it becomes. It could be an art to get the right balance. For example, it is well known that the physical address of a member can be decomposed to be represented by a tree like structure having a collection of data belonging to a few sets, like ones in country set, state/province set, city set, ..., etc. But such a structure could make the database more complex to present here. Therefore we choose to represent addresses using unstructured string in Membership+ system at present since we don't need formalized geographic information in any other features than member physical addresses now. When the needs arises, it can be added without too much difficulties in our system. In addition, formalization of some type of information could be still pre-mature at the time due to the lack of standardized knowledge about them or due to a lack of quality in available data, like in member contributed articles, web pages, accumulated generations of old data, etc. or, a lack of technical or financial means to support large number of them at the time. One could consider the use of distributed document stores for them. Thus one has to balance the two depending on the overall design goals of the system. I had talked about the above view on information systems in a somewhat abstract form long times ago, which I think would fit the present more concrete article better (see the last few paragraphs here).

One of the well known systems of finding information in unstructured text data is the keyword indexing and search one (KS).

Technically, the back-end data service is capable of combining SQ and keyword search (SQ+KS) means in a unified way. What the present web application does is to provide its own query interface and customization base on the querying API opened by the data service to present its users a friendly query front-end to find member related information. This article describes, in as general way as possible, what are the possibilities and how it can be explored to make it happen. The knowledge gained here can be applied to any of our data services built according to certain custom data schema.

A pre-condition for an efficient use of SQ is that a user should first of all know the structure of the system to a certain degree so right questions can be formulated and then he/she should know how such a structure is represented so that the SQ can be correctly expressed. The first may not be a big problem for most users since the knowledge is assumed common. For example, almost every user of a membership system knows that it must have users, roles, etc. The problems sometimes are how they are represented. One may have questions like "how do I refer to a member set, what are the names of properties that a member have that can be searched against, etc.", to start with. In other words, a user must learn how to interact with the system even when he/she knows, roughly, what is the question.

Here is where the query intelligence system that the data service has comes into play to help. It can be used to guide a user to formulate correct SQ+KS expressions without having to know accurate information about the structure representation of the system. It can also greatly speed up the input of the query expression due to its auto-complete feature. The following figure illustrate the process of finding a set of members according to registered addresses of members using keyword matching KS embedded inside a SQ expression and then displaying detailed information about him/her. The UI guides the user to input complex sorting and filtering expressions, which means to find users who's physical addresses contains the keyword "unicons", take the first 333 matches and then sort the items found according to their user names in ascending order. It can in fact be composed after just a few key strokes (n,a,co,k,m,p,a,unicons,f,333), here "n" is auto-completed as "Name" (i.e. User name), "a" -> "ascending", etc.

Sample Image

Figure: The process of finding users.

Note that the query button above is enabled only when the expression is closed so that a user has no way of sending wrong or incomplete expressions to the data service.

3. An Overview ^

3.1 Query Intelligence ^

The data service provides a state machine interface for each data set that generates a list of next possible input tokens (options) base on the current query expression (syntactic context). To enable interaction with it at a client side, each service proxy for a data set has a pair of async methods (and their sync corresponding ones)

public async Task<TokenOptions> GetNextSorterOpsAsync(CallContext cntx, 
                                                      List<QToken> sorters)
{
    ...
}

that generates a list of possible sort tokens based on the current sort expression represented by sorters and

public async Task<TokenOptions> GetNextFilterOps(CallContext cntx, 
                                                 QueryExpresion qexpr, 
                                                 string tkstr)
{
    ...
}

that generates a list of possible tokens for the filter expression based on the current filter expression represented by qexpr and current partial input tkstr. The said proxy is of type <entity-name>ServiceProxy where <entity-name> is the type name of the entity of the corresponding data set. The state machine is designed to generate options that are not only complete but also correct (namely no extra options are generated). This is different from many implementations of the so called IntelliSense system many integrated development environments (IDE) have because they are not fully syntactic context sensitive, leading to extra (namely incorrect options) being generated or even missing of options.

In addition to the options provided, it also carries other information in the class

[DataContract]
public class TokenOptions
{
    [DataMember]
    public string Hint { get; set; }
 
    [DataMember]
    public string CurrentExpr { get; set; }
 
    [DataMember]
    public bool QuoteVal { get; set; }
 
    [DataMember]
    public bool CanBeClosed { get; set; }
 
    [DataMember]
    public List<QToken> Options { get; set; }
}

It contains information like options, input hints, whether or not the current expression can be closed, etc., that characterizes the current syntactic context for clients.

3.2 Application as service gateway ^

The data service provides complete and generic relational data manipulation API for clients. They should not be allowed to be directly invoked by outside users in most application scenarios. The calls should be processed by certain layers inside the web application, which adds enforcements of security policy, business logic and transformation/projection of data according to the requirements of the web application.

.

Figure: Web application layers through which query related calls are passed.

The light weighted "Data Proxy" layer is mainly responsible for the processing of those meta-information related requests that are generic to all queries, which is implemented in a WCF service contained in the MembershipPlusAppLayer45 project. The "Security + Business Logic" layer is implemented in two projects, namely the ArchymetaMembershipPlusStores project and the MembershipPlusAppLayer45 project. Some of the basic security features that are common to all Asp.Net applications are handled in the ArchymetaMembershipPlusStores project, other more complicated and application specific security features are implemented inside of the MembershipPlusAppLayer45 project.

3.3 Unified interface ^

The names and functionalities of above listed methods can be found in the service proxy types for all data sets in all data sources supported by our data services. More specifically the methods are specific to each data set and the CallContext, QToken, QueryExpresion and TokenOptions types are specific to a data source. They do contain specific information that need to be distinguished from each other inside of data services that the web application is based upon, which can be found out easily by an inspection of the source code.

So far we had being on the analytical path of ever growing complexity. However if one tries to find differences they make from the perspective of clients, he/she finds almost none because these methods and classes have components that have the same corresponding names. Clients who have delegated the handling of those heavy lifting jobs and dirty details to the services can now afford a losing of information irrelevant to their roles via a synthetic reduction process that is to be handled inside the web application using mapping, projection and routing methods. Greater simplicity could be achieved as a result.

.

Figure: All ducks quack alike albeit we know each one is unique.

It is found that we can actually establish a single interface for all of them for clients after the synthesis, leading to a kind of unification. Because this is a process that transcend the existing class hierarchy, it demands tedious labour to establish and maintain in pure strongly typed systems, especially when the there are large number of data sets and data sources to handle. However the dynamic type in .Net framework and the loose typing nature of JavaScripts make it easier because combined together, they support the so called duck typing that naturally backup our unification scheme, in which one interface is used to cover the data query aspects of all data sets and data sources that a web application is base upon.

3.3.1 The interface ^

The query related requests that are not application or page specific are sent to the web server via JavaScript calls to WCF web services hosted inside the web application, namely the "Data Proxy" layer, which are transformed and forwarded to proper API methods to handle. The WCF service implements the following async version of the IDataServiceProxy interface

[ServiceContract(Namespace = " ... ", SessionMode = SessionMode.Allowed)]
public interface IDataServiceProxy
{
    [OperationContract]
    [WebInvoke(Method = "POST", ...)]
    Task<string> GetSetInfo(string sourceId, string set);
 
    [OperationContract]
    [WebInvoke(Method = "POST", ...)]
    Task<string> GetNextSorterOps(string sourceId, string set, string sorters);
 
    [OperationContract]
    [WebInvoke(Method = "POST", ...)]
    Task<string> GetNextFilterOps(string sourceId, string set, string qexpr);
 
    [OperationContract]
    [WebInvoke(Method = "POST", ...)]
    Task<string> NextPageBlock(string sourceId, string set, string qexpr, 
                               string prevlast);
 
    [OperationContract]
    [WebInvoke(Method = "POST", ...)]
    Task<string> GetPageItems(string sourceId, string set, string qexpr, 
                              string prevlast);
}

It is given a generic name IDataServiceProxy. All the input parameters and return values are of type string. A string has no pre-established structure so their meaning will be depend on how it is parsed. The implementation of the interface will be responsible for routing the calls to a proper interpreter which handles the request and returns the corresponding object which will be flattened into string by the said implementation and return to caller who is responsible for interpretation of the result.

Since intended clients of the web application are JavaScript programs, the best way for the said implementation to accept and return values is in the form of serialized JSON objects.

Here is a brief description of the methods involved.

  • The GetSetInfo method returns overall information about a data set set. The parameter sourceId is used to identify which data source the request is addressed to so that the system can route the request to a proper handler, like what is done in a vertical search system of ours that can support any number of back-end data services similar to the current one (see the demo here). Since there is only one data source in the Membership+ system at the current stage, namely the Membership+ one, it is not used at present.

  • The GetNextSorterOps method returns a list of possible tokens used to specify sorting conditions for set set given the corrent sorting expression sorters.

  • The GetNextFilterOps method returns a list of possible tokens used to specify filtering conditions for set set given the corrent query (sorting and filtering) "expression" qexpr.

  • The NextPageBlock method returns a block of page frames for data set set given the query expression qexpr and the last item of the previous block of page frames prevlast. A page frame contains first and last items in a page.

  • The GetPageItems method returns all items in a page for data set set given the query expression qexpr and the last item of the previous page, if any prevlast.

3.3.2 Detour and business logic ^

Not all requests are routed to this interface since they are quite generic with little security checking and/or customization of results. Some requests are handled by methods inside of the "Security + Business Logic" layer to meet application and/or page specific requirements. For example the GetPageItems method is most likely not going to be called directly since each view aspect (page) of a certain entity set may require different sub-set of properties and related entities (namely, a selected portion of the entity graph) to be loaded so that pre- and post- processing of the data could be different. This is going to be detailed in the following sections.

4. The Implementations ^

4.1 The Models ^

4.1.1 Service side ^

The service side data models are contained inside the Model sub-directory of the MembershipPlusShared45 project.

Each data set has the following corresponding models:

  • <Entity>Set model. It corresponds to a set of entities of name <Entity>. For the user data set, the type name is UserSet. It contains properties characterizing the overall nature of the set.

  • <Entity>PageBlock model. It represents a block of page frames for paged entities of name <Entity> under certain sorting condition. For the user data set, the type name is UserPageBlock. The data services return multiple page frames when requested. The number of page frames to return is determined by the value of PageBlockSize property of the <Entity>Set type.

  • <Entity>Page model. It represents a page frame for paged entities of name <Entity>. For the user data set, the type name is UserPage.

  • <Entity> model. It represents an entity of name <Entity>. For the user data set, the type name is User.

There are also query related data models that is contained inside the Common sub-directory of the MembershipPlusShared45 project:

  • CallContext model. It represents a client of the data service.

  • QToken model. It represents a token in a query expression.

  • QueryExpresion model. It represents a query expression.

  • TokenOptions model. It represents the next possible query expression tokens under the current querying context.

They are shared by the data service and web applications using the data service.

4.1.2 Client side ^

Except for the CallContext model, which is exchanged between the data service and web applications using the data service, all other data models should have a corresponding JavaScript data model and (KnockoutJS) view model. There is a complete set of such models under the Scripts\DbViewModels\MembershipPlus sub-directory of the data service site that one can use.

However the KnockoutJS view models come with the data service most likely need to be modified slightly before it can be used in a web-application since

  1. The scripting environment at the web applications can be different from the data service so that some efforts might be needed to adapt them into the new environment. For instance, some calls to the server are routed through Business + Security layers rather than the Data Proxy layer.
  2. The complete data view models provided by the data service may need to be trimmed down somehow to hide more internal details for security reasons and also highlight what is really needed in a particular application page.

All of the JavaScript view models related to the present article is contained in a single file, namely the MemberSearchPage.js file under the Scripts\DataService sub-directory of the web application, in which the view models are almost rewritten. For example the User view model:

function User(data) {
    var self = this;
    self.Initializing = true;
    self.data = data.data;
    self.member = data.member;
    self.hasIcon = data.hasIcon;
    self.iconUrl = appRoot + 'Account/GetMemberIcon?id=' + self.data.ID;
    self.IsEntitySelected = ko.observable(false);
    self.more = ko.observable(null);
    self.LoadDetails = function (callback) {
        if (self.more() != null) {
            callback(true);
        }
        $.ajax({
            url: appRoot + "Query/MemberDetailsJson?id=" + self.data.ID,
            type: "GET",
            beforeSend: function () {
            },
            success: function (content) {
                if (content.hasDetails && content.details.hasPhoto) {
                    content.details.photoUrl = 
                         appRoot + 'Account/UserPhoto?id=' + self.data.ID;
                }
                self.more(content);
                callback(true);
            },
            error: function (jqxhr, textStatus) {
                alert(jqxhr.responseText);
                callback(false);
            },
            complete: function () {
            }
        });
    }
    self.Initializing = false;
}

which takes a JSON user entity data as input and map its properties to various properties of its own, on creation. Here there is almost no KnockoutJS observables because the views are read only. There is no editing involved.

4.2 The Actions ^

The implementation of the data service proxy is contained in a single file named DataServiceProxy.cs under the Proxies sub-directory of the MembershipPlusAppLayer45 project. Since there is only one data source in the present web application, the general structure of the implementation is

public Task<string> MethodName(string sourceId, string set, ...)
{
    // get typed set kind instance ...
    switch(type)
    {
        case EntitySetType.User:
            {
               ... call methods for the user set ...
            }
            break;
        case ...
        case ...
    }
    return null;
}

In general the above code should be wrapped inside code equivalent to a switch statement of sourceId. The kind of data set is recovered from JSON formatted parameter

JavaScriptSerializer jser = new JavaScriptSerializer();
dynamic sobj = jser.DeserializeObject(set) as dynamic;
EntitySetType type;
if (Enum.TryParse<EntitySetType>(sobj["set"], out type))
{
   ... handler of valid types shown above
}

It contains various parameters for the query set by the client JavaScripts, e.g. inside the MemberSearchPage.js file under the Scripts\DataService sub-directory of the web application that is relevant to the current article, namely

set: JSON.stringify({ 
                set: setName, 
                pageBlockSize: self.PageBlockSize(), 
                pageSize: self.PageSize_(), 
                setFilter: self.SetFilter 
          })

is one of the parameters passed to the AJAX call to the WCF service hosted inside of the web application. It defines valid parameters that can be extracted from the it. It has the name of the set in set, the number of pages in a page block in pageBlockSize, the number of rows in a page in pageSize and finally the sub-set of items interested are defined by the set filter expression contained in setFilter. The JSON object is serialized into string after processed by JSON.sringify before sending to the server.

What is a set filter and why it is needed?

4.2.1 Sub-set projection ^

Since the data service can be used by multiple applications, not all registered users inside of the data service is a member of the current web application. For that reason querying of users should involve a sub-set of records in User data set who is a member of the current web application. This can be a little complex to handle in normal ways. The data service however has a systematic mechanism of sub-setting that can be used to define a sub-set that satisfies certain filtering conditions. The sub-set can then be used as a querying parameter, in the same way as the whole set. It helps us to hide the complexities involved. For example when handling query requests to user data set the value

userSet.SetFilter = 'UserAppMember.Application_Ref.Name == "MemberPlusManager" && 
                     ( UserAppMember.SearchListing is null || UserAppMember.SearchListing == true )'

is set in the JavaScript initialization handler of the page after loading is completed (see the following). It is a valid filtering expression which means select only user records whose associated application membership records contain one that its application reference record has Name "MemberPlusManager and also who is not hidden from application level search". Here the value "MemberPlusManager" is defined inside of the Web.config configuration file. This object graph oriented expression, when translated to a SQL expression, would involve inner joining of three tables: Users, UserAppMembers and Applications for each query under the current application context, which is not that straightforward to follow even when dealing with simple queries, let alone complex ones to be shown in the following sections. The value is passed to various query methods which is then passed to the corresponding property of an instance of the set data model before sending to the back-end data service, e.g.

UserSet _set = new UserSet();
_set.PageBlockSize = int.Parse(sobj["pageBlockSize"]);
_set.PageSize_ = int.Parse(sobj["pageSize"]);
if (sobj.ContainsKey("setFilter"))
    _set.SetFilter = sobj["setFilter"];
... call remote query methods ...

This is all that is required to work on a sub-set of data in the current approach.

One may ask how do one know the above expression is the correct one when he/she try to construct an expression to represent statement in green above? The answer is: he/she don't have to remember it in the exact form to start with, the query intelligence of the user interface will help construct him/her as long as he/she has a syntactically fuzzy but semantically clear idea as to what he/she want, even in his/her own native language (see the query customization section below). Of course if one has no idea what he/she want, it can't help him/her at all. That been said, it is not this bad at all. The interactive nature of the system can help a user to get clearer and clearer idea what he/she want after engage with it for a while, like what one do in learning but with an accelerated pace. What about one don't know how to learn? Then this system can't do it for him/her, sorry for that.

4.2.2 Get set information ^

This method is invoked immediately after the client page is loaded. Amongst others only two structures are returned, namely the user count of the current web application and the initial sort options to the page to start with:

case EntitySetType.User:
    {
        string filter = null;
        if (sobj.ContainsKey("setFilter"))
            filter = sobj["setFilter"];
        UserServiceProxy svc = new UserServiceProxy();
        var si = await svc.GetSetInfoAsync(ApplicationContext.ClientContext, 
                                           filter);
        JavaScriptSerializer ser = new JavaScriptSerializer();
        string json = ser.Serialize(new { 
                                EntityCount = si.EntityCount, 
                                Sorters = si.Sorters 
                          });
        return json;
    }

Here the .Net object si of type UserSet returned from the call is turned into string using a one way JavaScriptSerializer serializer since only a few properties of the si is needed to return, in JSON formt. For the member query page interested in this article, namely the SearchMembers.cshtml page (under the Views\Query sub-directory of the web application), it is invoked through a chain of method calls, starting from the jQuery page loaded handler

<script type="text/javascript">
    appRoot = '@Url.Content("~/")';
    serviceUrl = appRoot + 'Services/DataService/DataServiceProxy.svc';
    dataSourceId = '';
    setName = 'User';
    appName = '@ViewBag.AppName';
    $(function () {
        window.onerror = function () {
            window.status = '...';
            return true;
        }
        userSet = new UserSet(serviceUrl);
        userSet.SetFilter = 
                'UserAppMember.Application_Ref.Name == "' + appName + '" && '
                + '( UserAppMember.SearchListing is null || UserAppMember.SearchListing == true )';
        userSet.GetSetInfo();
        ko.applyBindings(userSet);
        initsortinput(userSet);
        initfilterinput(userSet);
        $('.ui-autocomplete').addClass('AutoCompleteMenu');
    });
</script>

The global variable userSet is of type UserSet defined in the MemberSearchPage.js under the Scripts\DataService sub-directory of the web application. The page loaded event handler setup the sub-set and then calls the GetSetInfo method of UserSet after instantiation, which is

function UserSet(dataServiceUrl) {
    var self = this;
    self.BaseUrl = dataServiceUrl;
 
    ... other stuff ...
 
    self.GetSetInfo = function () {
        $.ajax({
            url: self.BaseUrl + "/GetSetInfo",
            type: "POST",
            dataType: "json",
            contentType: "application/json; charset=utf-8",
            data: JSON.stringify({ 
                           sourceId: dataSourceId, 
                           set: JSON.stringify({ 
                                         set: setName, 
                                         setFilter: self.SetFilter 
                                     }) 
                       }),
            beforeSend: function () {
            },
            success: function (content) {
                var r = JSON.parse(content.GetSetInfoResult);
                self.TotalEntities(r.EntityCount);
                self.CurrentSorters(new TokenOptions());
                for (var i = 0; i < r.Sorters.length; i++) {
                    var tk = r.Sorters[i];
                    if (tokenNameMap) {
                        if (tokenNameMap(tk, setName, false)) {
                            self.CurrentSorters().Options.push(tk);
                        }
                    } else {
                        self.CurrentSorters().Options.push(tk);
                    }
                }
                self.CurrentSorters().CanBeClosed = true;
                self.CurrentSorters().isLocal = false;
            },
            error: function (jqxhr, textStatus) {
                alert(jqxhr.responseText);
            },
            complete: function () {
            }
        });
    };
}

It calls the GetSetInfo method of the WCF service which is handled by the first code block shown in this sub-section, in a AJAX call. The total number of users of the web application is set in the KnockoutJS observable property TotalEntities of UserSet, which will be bound to a html element to be displayed. The initial list of sorter options is pushed into the CurrentSorters().Options KnockoutJS observable array that is bound to the sort input auto-complete box to be described in the following.

4.2.3 Sorting and filtering token options ^

The sorters parameter is deserialized into the .Net List<QToken> object using a serializer of type DataContractJsonSerializer and send to the remote service to obtain next available options:

public async Task<string> GetNextSorterOps(string sourceId, string set, 
                                           string sorters)
{
    switch (...)
    ...
    case EntitySetType.User:
        {
            var ser1 = new DataContractJsonSerializer(typeof(List<QToken>));
            var ser2 = new DataContractJsonSerializer(typeof(TokenOptions));
            System.IO.MemoryStream strm = new System.IO.MemoryStream();
            byte[] sbf = System.Text.Encoding.UTF8.GetBytes(sorters);
            strm.Write(sbf, 0, sbf.Length);
            strm.Position = 0;
            var _sorters = ser1.ReadObject(strm) as List<QToken>;
            UserServiceProxy svc = new UserServiceProxy();
            var result = await svc.GetNextSorterOpsAsync(
                                        ApplicationContext.ClientContext, 
                                        _sorters
                                   );
            strm = new System.IO.MemoryStream();
            ser2.WriteObject(strm, result);
            string json = System.Text.Encoding.UTF8.GetString(strm.ToArray());
            return json;
        }
    ...
}

A second serializer of the same type is used to serialize the result into a JSON string which is returned to the client. This is invoked by the jQuery UI auto-complete options handler for "source":

function (request, response) {
    if (!s.CurrentSorters() || !s.CurrentSorters().Options)
        return;
    var opts = s.CurrentSorters().Options;
    var arr = opts.filter(function (val) {
        return val.DisplayAs.toLowerCase().indexOf(
                          request.term.toLowerCase()) == 0;
    }).sort(tokenSortCmp);
    if (arr.length != 1 || deleting) {
        // having more than one options or is deleting? show the available list
        response($.map(arr, function (item) {
            return { label: item.DisplayAs == "this" ? 
                                item.TkName : item.DisplayAs, 
                     value: item.DisplayAs };
        }));
    } else {
        // found a unique match, push the current token, clear the input and 
        // call the service for the next available options.
        iobj.autocomplete("close");
        var tk = arr[0];
        s.SorterPath.push(tk);
        iobj.val("");
        ...
        s.GetNextSorterOps(function (ok) {
            if (ok) {
                iobj.removeAttr("disabled");
                ...
            }
            iobj.focus();
            iobj.css("cursor", "");
        });
        return false;
    }
}

which is contained inside the initsortinput global function inside the MemberSearchPage.js. Here iobj is the jQuery object corresponding to the input element inside of the web page and s is a global instance of (JavaScript) UserSet whose GetNextSorterOps method is

self.GetNextSorterOps = function (callback) {
    var qtokens = [];
    for (var i = 0; i < self.SorterPath().length; i++)
        qtokens.push(self.SorterPath()[i]);
    $.ajax({
        url: self.BaseUrl + "/GetNextSorterOps",
        type: "POST",
        dataType: "json",
        contentType: "application/json; charset=utf-8",
        data: JSON.stringify({ sourceId: dataSourceId, 
                               set: setName, 
                               sorters: JSON.stringify(qtokens) 
                   }),
        success: function (content) {
            // new options arrived, push the current options into stack
            self.SortersStack.push(self.CurrentSorters());
            self.CurrentSorters(new TokenOptions());
            // recover the JSON object
            var r = JSON.parse(content.GetNextSorterOpsResult);
            self.CurrentSorters().Hint = r.Hint;
            self.CurrentSorters().CurrentExpr(r.CurrentExpr);
            self.CurrentSorters().QuoteVal = r.QuoteVal;
            self.CurrentSorters().CanBeClosed = r.CanBeClosed;
            self.CurrentSorters().IsLocal = false;
            for (var i = 0; i < r.Options.length; i++) {
                var tk = new QToken();
                tk.CopyToken(r.Options[i]);
                if (tokenNameMap) {
                    // when token customization exists, customize it
                    if (tokenNameMap(tk, setName, false)) {
                        self.CurrentSorters().Options.push(tk);
                    }
                } else {
                    self.CurrentSorters().Options.push(tk);
                }
            }
            callback(true);
        },
        error: function (jqxhr, textStatus) {
            alert(jqxhr.responseText);
            callback(false);
        }
    });
};

The case for the filter options is similar and their implementations are all contained in the same corresponding files. It will not be repeated here.

4.2.4 Get page blocks ^

Paging in a SQ+KS system is the process of loading a limited number of entities at a time from a data source so that computational resources can be saved. Genuine paging under arbitrary sorting conditions without loading all rows first can be difficult to handle without assistances from underlying database (like the row_number() function in Microsoft SQL database). For a database agnostic or database-less solution like the current one, it can't however assume the existence of any of such non standard features. So the paging inside of the data service has to depend on complex query conditions derived from page boundaries information, which consists of the first and last entities of the page, contained in a page frame object.

The system retrieve entity list in two steps: first it gets a list of page frames under certain sorting conditions and then it loads entities belonging to a page on demand (like when the page is to be displayed) one page at a time.

public async Task<string> NextPageBlock(string sourceId, 
                                        string set, 
                                        string qexpr, 
                                        string prevlast)
{
        switch (type)
...
        case EntitySetType.User:
            {
                var ser1 = new DataContractJsonSerializer(typeof(QueryExpresion));
                var ser2 = new DataContractJsonSerializer(typeof(User));
                var ser3 = new DataContractJsonSerializer(typeof(UserPageBlock));
                var strm = new System.IO.MemoryStream();
                byte[] sbf = System.Text.Encoding.UTF8.GetBytes(qexpr);
                strm.Write(sbf, 0, sbf.Length);
                strm.Position = 0;
                var _qexpr = ser1.ReadObject(strm) as QueryExpresion;
                UserServiceProxy svc = new UserServiceProxy();
                UserSet _set = new UserSet();
                _set.PageBlockSize = int.Parse(sobj["pageBlockSize"]);
                _set.PageSize_ = int.Parse(sobj["pageSize"]);
                if (sobj.ContainsKey("setFilter"))
                    _set.SetFilter = sobj["setFilter"];
                User _prevlast = null;
                if (!string.IsNullOrEmpty(prevlast))
                {
                    strm = new System.IO.MemoryStream();
                    sbf = System.Text.Encoding.UTF8.GetBytes(prevlast);
                    strm.Write(sbf, 0, sbf.Length);
                    strm.Position = 0;
                    _prevlast = ser2.ReadObject(strm) as User;
                }
                var result = await svc.NextPageBlockAsync(
                                                 ApplicationContext.ClientContext, 
                                                 _set, 
                                                 _qexpr, 
                                                 _prevlast);
                strm = new System.IO.MemoryStream();
                ser3.WriteObject(strm, result);
                string json = System.Text.Encoding.UTF8.GetString(strm.ToArray());
                return json;
            }
        ...
}

For a given query condition, it caches the page frames already loaded in the PageBlocks array of the view model UserSet. If the block of page frame is not found in the cache, it makes an AJAX call to the WCF service hosted inside of the web application to load a block of page frames and save it in a local cache. The displayed page list is stored in the PageWindow observable array of the UserSet view model and is bound to the view.

This is invoked at the client side in a chain of method calls starting from two global methods showlist and nextPageBlock inside the MemberSearchPage.js. The first one is triggered by a user clicking the "Start querying" button after completing construction of a query expression and the second one is invoked when the next page block button is clicked and when the block is not loaded. These method will be described in more details in the next sub-section.

These two methods then call the NextPageBlock method of global userSet variable

self.NextPageBlock = function (qexpr, last, callback) {
    if (self.IsQueryStateChanged())
        self.ResetPageState();
    if (self.CurrBlockIndex() < self.PageBlocks().length) {
        callback(true, false);
        return;
    }
    $.ajax({
        url: self.BaseUrl + "/NextPageBlock",
        type: "POST",
        dataType: "json",
        contentType: "application/json; charset=utf-8",
        data: JSON.stringify({ 
                        sourceId: dataSourceId, 
                        set: JSON.stringify({ 
                                 set: setName, 
                                 pageBlockSize: self.PageBlockSize(), 
                                 pageSize: self.PageSize_(), 
                                 setFilter: self.SetFilter 
                             }), 
                        qexpr: JSON.stringify(qexpr), 
                        prevlast: last == null ? 
                                  null : JSON.stringify(last) 
                   }),
        success: function (content) {
            var data = JSON.parse(content.NextPageBlockResult);
            self.EntityCount(data.TotalEntities);
            self.PageCount(data.TotalPages);
            if (data.Pages.length == 0) {
                var lpb = self.LastPageBlock();
                if (lpb != null) {
                    lpb.IsLastBlock(true);
                    var lp = lpb.LastPage();
                    if (lp != null) {
                        lp.IsLastPage(true);
                        self.CurrBlockIndex(self.CurrBlockIndex() - 1);
                    }
                } else {
                    self.PagesWindow.removeAll();
                }
            }
            else {
                var idx0 = 0;
                for (var i = 0; i < self.CurrBlockIndex() ; i++) {
                    idx0 += self.PageBlocks()[i].BlockCount;
                }
                var pb = new UserPageBlock(idx0, data);
                pb.BlockIndex = self.PageBlocks().length;
                self.PageBlocks.push(pb);
                self.PagesWindow.removeAll();
                for (var i = 0; i < pb.Pages().length; i++) {
                    self.PagesWindow.push(pb.Pages()[i]);
                }
            }
            self.IsQueryStateChanged(false);
            callback(true, true);
        },
        error: function (jqxhr, textStatus) {
            ...
        }
    });
};

which calls the above mentioned method of the DataServiceProxy class.

4.2.5 Loading a page ^

The GetPageItems method is implemented in the DataServiceProxy class inside of the MembershipPlusAppLayer45 project. However it is most likely not used in the web application. The reason is that the said generic method only returns a list of User type in full details without, however, any other user related information.

In an application, depending on the purpose of the view, it is best to load a projected list user entities with only a selected sub-set of properties and information about some related entities being returned.

When a previously un-selected page number is clicked inside of the view, it calls the global loadpage method

function loadpage(index) {
    if (loadingPage) {
        return;
    }
    loadingPage = true;
    var p = null;
    var p0 = null;
    var blk = userSet.PageBlocks()[userSet.CurrBlockIndex()];
    for (var i = 0; i < blk.Pages().length; i++) {
        var _p = blk.Pages()[i];
        if (_p.Index_() == index) {
            p = _p;
        } else if (_p.IsPageSelected()) {
            p0 = _p;
        }
    }
    setWait(true)
    if (p != null) {
        if (!p.IsDataLoaded()) {
            p.GetPageItems(userSet, function (ok) {
                if (ok) {
                    updateCurrPage(p, p0);
                }
                loadingPage = false;
                setWait(false)
            });
        } else {
            updateCurrPage(p, p0);
            loadingPage = false;
            setWait(false)
        }
    } else {
        loadingPage = false;
        setWait(false)
    }
}

inside of the MemberSearchPage.js file, which calls the GetPageItems method of the UserPage view model if the items in the page has not been loaded, namely

function UserPage() {
    var self = this;
    ...
    self.GetPageItems = function (s, callback) {
        if (self.IsDataLoaded())
            return;
        var qexpr = getQueryExpr();
        var lastItem = null;
        var ipage = self.Index_();
        if (self.Index_() > 0) {
            var blk = s.PageBlocks()[s.CurrBlockIndex()];
            if (blk.Pages()[0].Index_() != ipage) {
                for (var i = 0; i < blk.Pages().length; i++) {
                    if (blk.Pages()[i].Index_() == ipage - 1) {
                        lastItem = blk.Pages()[i].LastItem();
                        break;
                    }
                }
            } else {
                var prvb = s.PageBlocks()[s.CurrBlockIndex() - 1];
                lastItem = prvb.Pages()[prvb.Pages().length - 1].LastItem();
            }
        }
        $.ajax({
            url: appRoot + "Query/GetMembers",
            type: "POST",
            dataType: "json",
            contentType: "application/json; charset=utf-8",
            data: JSON.stringify({ 
                            set: JSON.stringify({ 
                                        set: setName, 
                                        pageBlockSize: s.PageBlockSize(), 
                                        pageSize: s.PageSize_(), 
                                        setFilter: s.SetFilter, 
                                        appName: appName }), 
                            qexpr: JSON.stringify(qexpr), 
                            prevlast: lastItem == null ? 
                                               null : JSON.stringify(lastItem) 
                       }),
            beforeSend: function () {
                self.Items.removeAll();
            },
            success: function (content) {
                var items = JSON.parse(content);
                for (var i = 0; i < items.length; i++)
                    self.Items.push(new User(items[i]));
                self.IsDataLoaded(true);
                callback(true);
            },
            error: function (jqxhr, textStatus) {
                alert(jqxhr.responseText);
                callback(false);
            },
            complete: function () {
            }
        });
    }
}

Instead of call the corresponding method in the DataServiceProxy class, it invoke url appRoot + "Query/GetMembers" which is handled by the GetMembers method of the QueryController class (in the file Controllers\QueryController.cs). It delegate the task to the GetMembers method of the MemberViewContext class defined in the MemberViewContext.cs file of the MembershipPlusAppLayer45 project

public static async Task<string> GetMembers(string set, string qexpr, 
                                                        string prevlast)
{
    var jser = new JavaScriptSerializer();
    dynamic sobj = jser.DeserializeObject(set) as dynamic;
    var ser1 = new DataContractJsonSerializer(typeof(QueryExpresion));
    var ser2 = new DataContractJsonSerializer(typeof(User));
    var ser3 = new JavaScriptSerializer();
    System.IO.MemoryStream strm = new System.IO.MemoryStream();
    byte[] sbf = System.Text.Encoding.UTF8.GetBytes(qexpr);
    strm.Write(sbf, 0, sbf.Length);
    strm.Position = 0;
    var _qexpr = ser1.ReadObject(strm) as QueryExpresion;
    UserServiceProxy svc = new UserServiceProxy();
    UserSet _set = new UserSet();
    _set.PageBlockSize = int.Parse(sobj["pageBlockSize"]);
    _set.PageSize_ = int.Parse(sobj["pageSize"]);
    if (sobj.ContainsKey("setFilter"))
        _set.SetFilter = sobj["setFilter"];
    User _prevlast = null;
    if (!string.IsNullOrEmpty(prevlast))
    {
        strm = new System.IO.MemoryStream();
        sbf = System.Text.Encoding.UTF8.GetBytes(prevlast);
        strm.Write(sbf, 0, sbf.Length);
        strm.Position = 0;
        _prevlast = ser2.ReadObject(strm) as User;
    }
    var result = await svc.GetPageItemsAsync(Cntx, _set, _qexpr, 
                                                         _prevlast);
    var ar = new List<dynamic>();
    string appId = ApplicationContext.App.ID;
    UsersInRoleServiceProxy uirsvc = new UsersInRoleServiceProxy();
    foreach (var e in result)
    {
        var membs = svc.MaterializeAllUserAppMembers(Cntx, e);
        var memb = (from d in membs where 
                                    d.ApplicationID == appId select d
                   ).SingleOrDefault();
        ar.Add(new 
               { 
                  data = e, // there is no property projection made, 
                            // but it can be added
                  member = memb, // same as above 
                  hasIcon = memb != null && 
                               !string.IsNullOrEmpty(memb.IconMime) 
               }
           );
    }
    string json = ser3.Serialize(ar);
    return json;
}

Here not only the user entities are retrieved, but also the membership record for each user corresponding to the current web application is also retrieved and returned:

    foreach (var e in result)
    {
        var membs = svc.MaterializeAllUserAppMembers(Cntx, e);
        var memb = (from d in membs where 
                                    d.ApplicationID == appId select d
                   ).SingleOrDefault();
        ...
    }

If there are only a few applications inside of the data service, the above code is acceptable. Otherwise, it is not very efficient because it loads all membership records that a user has to the web application and filter it out locally, which could wast bandwidth and local memory. An alternative to the above is to use constrained query (see here)

    foreach (var e in result)
    {
        UserAppMemberServiceProxy mbsvc = new UserAppMemberServiceProxy();
        var cond = new UserAppMemberSetConstraints 
        { 
            ApplicationIDWrap = new ForeignKeyData<string> { KeyValue = appId }, 
            UserIDWrap = new ForeignKeyData<string> { KeyValue = e.ID } 
        };
        var memb = (await mbsvc.ConstraintQueryAsync(Cntx, 
                                                     new UserAppMemberSet(), 
                                                     cond, 
                                                     null)).SingleOrDefault();
        ...
    }
</string></string>

which load only one membership record, if any, for each user.

4.2.6 Loading a user ^

The global JavaScript method selectUser is called when one of the users in a page is selected, which, after some processing, calls the global updateEntityDetails method

function updateEntityDetails(user) {
    if (user.more() == null) {
        user.LoadDetails(function(ok){
            if (ok) {
                ... scroll to the user details
            }
        })
    } else {
        ... scroll to the user details
    }
}

which when the user details (more()) is not initialized, the LoadDetails method of the User (KnockoutJS) view model

function (callback) {
    if (self.more() != null) {
        callback(true);
    }
    $.ajax({
        url: appRoot + 
                  "Query/MemberDetailsJson?id=" + 
                  self.data.ID,
        type: "GET",
        success: function (content) {
            if (content.hasDetails &&  
                        content.details.hasPhoto) {
                content.details.photoUrl = appRoot + 
                                     'Account/UserPhoto?id=' + 
                                     self.data.ID;
            }
            self.more(content);
            callback(true);
        },
        error: function (jqxhr, textStatus) {
            alert(jqxhr.responseText);
            callback(false);
        }
    });
}

will be invoked. The AJAX call is handled by the MemberDetailsJson method of the QueryController class. That method delegates the task to the GetBriefMemberDetails method of the MemberViewContext class, which load a selected collection of records related to a user in one call to the service, namely by calling the LoadEntityGraphRecurs method of the UserServiceProxy class.

The following is partially extracted from the remarks section of its document and supplemented with additional information of this article

This method is designed to load a selected sub entity graph recursively from the data source in one call to the service starting with a given entity (id). It could be used to increase performance and to reduce client code complexity, sometimes significantly.

The selection is controlled by two parameters, namely excludedSets and futherDrillSets.

The excludedSets parameter is used to exclude a list of entity sets and all other sets that depend on it. This can be better understood if one has a look at the schematic view of the data set schema shown below, namely if one date set (node) is excluded then all the sets that it points to will not be reached through it, although some of them could still be reached following other routes.

There are many ways an entity sub-graph can be loaded, the present implementation is based on the rule to be given next. Namely, starting from the entry element, it loads all entities that depends on it, recursively downward (namely following the arrows in the schema view). It also loads all elements that any of the elements visited by the downward recursion depends upon, recursively upward (namely in the opposite direction of the arrows in the schema view), but never go downward again without explicit instruction.

The futherDrillSets parameter is used control when to go downward again, represented by the SetType member and the collection of data sets that depend on it, represented by the RelatedSets member, should be further drilled down, recursively.

Note that a data service has intrinsic limits that do not allow transmitting an entity graph that is too large in one call, so one has to select which part of the entire graph should be loaded in each call to the data service,

For a given entity, the entities that it depends upon are represented by the member objects corresponding to each foreign keys. However, the sets of entities that depend on the said entity are stored into the corresponding collection members having the "Changed" prefix (see the 2nd last section on entity graphs for details) and these entities do not have a back reference to the said entity to avoid circular references when doing serialization, Such back references can be added after the graph is materialized on the clients side, if needed.

.

Figure: Schematic view of the data schema.

According to the data schema, Announcement, Communication, EventCalendar, MemberNotification, UserAppMember, UserAssociation, UserAssocInvitation, UserDetail, UserGroupMember, UserProfile, UsersInRole and UsersRoleHistory data sets directly depend on or are associated with the User data set, some even multiple times. Of course we do not want to load all of them in user query view. According to the present view context, we can exclude all but the Communication, UserAppMember, and UserDetail data sets when building our data graph:

public static async Task<dynamic> GetBriefMemberDetails(string id)
{
    UserServiceProxy usvc = new UserServiceProxy();
    EntitySetType[] excludes = new EntitySetType[]
    {
        EntitySetType.Announcement,
        EntitySetType.EventCalendar,
        EntitySetType.MemberNotification,
        EntitySetType.UserAssociation,
        EntitySetType.UserAssocInvitation,
        EntitySetType.UserGroupMember,
        EntitySetType.UserProfile,
        EntitySetType.UsersInRole,
        EntitySetType.UsersRoleHistory
    };
    var cctx = Cntx;
 
    var graph = await usvc.LoadEntityGraphRecursAsync(cctx, id, 
                                                       excludes, null);
    // select only those items that belongs to the current application
    var member = (from d in graph.ChangedUserAppMembers 
                     where d.ApplicationID == ApplicationContext.App.ID 
                     select d).Single();
    var Details = (from d in graph.ChangedUserDetails 
                     where d.ApplicationID == ApplicationContext.App.ID 
                     select d).FirstOrDefault();
    var Communications = (from d in graph.ChangedCommunications 
                     where d.ApplicationID == ApplicationContext.App.ID 
                     select d).ToArray();
    dynamic obj = null;
 
    ... build the dynamic object to be converted to json on return base 
    ... on these values
    return obj;
}

The collection of records associated with the user are re-assembled next in a dynamic type so that it can be converted to JSON object on return.

4.3 The Views ^

The view described in this article is used to provide generic user search, it is located under the Views\Query sub-directory of the web application named SearchMembers.cshtml.

4.3.1 Query expression editor ^

Since data query is expected to be used in many parts of the web application and, as it is described above, it is independent of the data sets and data sources, the query expression editor is put inside of the Views\Shared sub-directory of the web application as a partial view named _QueryComposerPartial.cshtml. Any views that needs it can include it from there.

After setting up suitable KnockoutJS view models, the expression editor is in fact very simple. For example the filter expression editor is of the form

<!-- ko foreach: FilterPath -->
<div data-bind="text: DisplayAs, css: TkClass"></div>
<!-- /ko -->
<input id="filterOpts" />

Namely, it's just an array of <div>'s bound to the (KnockoutJS) observable array FilterPath of the UserSet view model. Each <div> is bound to the corresponding object of type QToken having css class determined by the TkClass property and content determined by the DisplayAs property. The value of TkClass will be set to reference a proper css class according to the type of the token so that we could have the effects of syntax highlighting or even more advanced styling.

This <div> series is followed by an <input> element that is bound to jQuery UI auto-complete control. It is used by a user to do token input. This is basically all there is to it on the view level.

The FilterPath is updated each time the user input or delete a token. Since it is an KnockoutJS observable array, the view will be updated as soon as the array changes.

The editors (one for sorter the other for filter) are initialized inside of the page loaded event handler

.

Figure: Query expression editor. Available option menu is opened.

$(function () {
    ...
    userSet = new UserSet(serviceUrl);
    userSet.SetFilter = '...';
    userSet.GetSetInfo();
    ko.applyBindings(userSet);
    initsortinput(userSet); // init sorter editor
    initfilterinput(userSet); // init filter editor
    ...
});

The two global methods, namely initsortinput and initfilterinput in CustomMembershipPlus.js file initialize and set up the jQuery UI auto-complete input controls and edit state related updates.

For example, the "source" function for the sorter is described here and function for "select" is

function (event, ui) {
    var tk = null;
    var opts = s.CurrentSorters().Options;
    for (var i = 0; i &tl; opts.length; i++) {
        if (opts[i].DisplayAs == ui.item.value) {
            tk = opts[i];
            break;
        }
    }
    if (tk != null) {
        // push the selected token into the SorterPath that 
        // will automatically displayed in the view
        s.SorterPath.push(tk);
        iobj.val("");
        iobj.attr("disabled", "disabled");
        iobj.css("cursor", "progress");
        if (tk == null || tk.TkName != "asc" && 
                              tk.TkName != "desc") {
            // incomplete, disable the query button
            // and hide the input box for the filter.            
            enableQuery(false);
            $("#filterOpts").hide();
        } else {
            enableQuery(true);
            $("#filterOpts").show();
            s.IsQueryStateChanged(true);
        }
        // load the next options
        s.GetNextSorterOps(function (ok) {
            if (ok) {
                ... update state, visuals, etc.
            }
            iobj.focus();
            iobj.css("cursor", "");
        });
        return false;
    }
}

When the query expression is in the state of being "closed", the "Start querying" button is enabled. When clicked, this button triggers a call to the global method showlist inside the MemberSearchPage.js file that loads the initial block of page frames starting with the first page.

function showlist(e) {
    if (!queryCompleted)
        return;
    var qexpr = getQueryExpr();
    if (userSet.IsQueryStateChanged()) {
        userSet.ResetPageState();
    }
    userSet.NextPageBlock(qexpr, null, function (ok, ch) {
        if (ch && userSet.CurrentPage() != null && 
                  !(typeof userSet.CurrentPage().Items === 'undefined')) {
            userSet.CurrentPage().Items.removeAll();
        }
        if (ok) {
            userSet.IsQueryInitialized(true);
            if (ch && userSet.PageBlocks().length > 0 && 
                      userSet.PageBlocks()[0].Pages().length > 0) {
                loadpage(0);
            }
        }
    });
}

4.3.2 The page window ^

The currently displaying page frames are bound to the PagesWindow (KnockoutJS) observable array of the UserSet view model. For simplicity only two buttons are added to change the content of the displaying page frames, as it is shown in the following

<ul class="pagination-sm">
    <!-- ko if: PrevBlock() != null -->
    <li>
        <a href="javascript:prevPageBlock()" 
                              title="Load previous page block ...">
            <span class="glyphicon glyphicon-chevron-left"></span>
        </a>
    </li>
    <!-- /ko -->
    <!-- ko foreach: PagesWindow -->
    <!-- ko if: IsPageSelected() -->
    <li class="active">
        <span class="selected" data-bind="text: PageNumber"></span>
    </li>
    <!-- /ko -->
    <!-- ko ifnot: IsPageSelected() -->
    <li>
        <a data-bind="attr: {href: PageLink}">
            <span data-bind="text: PageNumber"></span>
        </a>
    </li>
    <!-- /ko -->
    <!-- /ko -->
    <!-- ko if: MoreNextBlock() -->
    <li>
        <a href="javascript:nextPageBlock()" 
                                   title="Load next page block ...">
            <span class="glyphicon glyphicon-chevron-right"></span>
        </a>
    </li>
    <!-- /ko -->
</ul>
.

Figure: Pages window and item list. Shown at the bottom left is the number of matching items.

One button is used to load previous block of pages, which is visible only when there are previous blocks to load. It is handled by the global method

function prevPageBlock() {
    if (loadingPage) {
        return;
    }
    var idx = userSet.CurrBlockIndex();
    if (idx > 0) {
        userSet.CurrBlockIndex(idx - 1);
        userSet.PagesWindow.removeAll();
        var ipage = -1;
        for (var i = 0; 
             i < userSet.PageBlocks()[idx - 1].Pages().length; 
             i++) {
            var p = userSet.PageBlocks()[idx - 1].Pages()[i];
            userSet.PagesWindow.push(p);
            if (p.IsPageSelected()) {
                ipage = p.Index_();
            }
        }
        loadpage(ipage == -1 ? 0 : ipage);
    }
}

The other button is used to load the next block of pages, which is also visible only when there are next blocks to load. It is handled by the global method

function nextPageBlock() {
    if (loadingPage) {
        return;
    }
    var idx = userSet.CurrBlockIndex();
    if (idx < userSet.PageBlocks().length - 1) {
        userSet.CurrBlockIndex(idx + 1);
        userSet.PagesWindow.removeAll();
        var ipage = -1;
        for (var i = 0; 
             i < userSet.PageBlocks()[idx + 1].Pages().length; 
             i++) {
            var p = userSet.PageBlocks()[idx + 1].Pages()[i];
            userSet.PagesWindow.push(p);
            if (p.IsPageSelected()) {
                ipage = p.Index_();
            }
        }
        loadpage(ipage == -1 ? 0 : ipage);
    } else {
        idx = userSet.PageBlocks().length - 1;
        var b = userSet.PageBlocks()[idx];
        if (!b.IsLastBlock()) {
            userSet.CurrBlockIndex(idx + 1);
            var p = b.LastPage();
            if (p == null) {
                return;
            }
            var qexpr = getQueryExpr();
            userSet.NextPageBlock(qexpr, 
                          p.LastItem(), 
            function (ok, ch) {
                if (ok) {
                    if (userSet.PageBlocks().length > 0 && 
                        userSet.PageBlocks()[idx + 1].Pages().length > 0) {
                        loadpage(userSet.PageBlocks()[idx + 1].Pages()[0].Index_());
                    }
                }
            });
        }
    }
}

It checks if the block of page is already loaded. If so, the block of available ones are pushed into the PagesWindow of the global userSet variable. Otherwise, it loads the next page block from the web application.

Note that the maximum size of the PagesWindow is assumed identical to the maximum size of the page block downloaded from the web application. It is not necessarily has to be so. It's just that otherwise, the above two methods will be more complicated than the current one.

In the future, more advanced features like go to a page according to page number, go to first page block, go to last page block and other page block display means like smooth shifting of page frames window, etc., could be added.

4.3.3 The user list ^

When a currently un-selected page is click, it invokes the global method

function loadpage(index) {
    if (loadingPage) {
        return;
    }
    loadingPage = true;
    var p = null;
    var p0 = null;
    var blk = userSet.PageBlocks()[userSet.CurrBlockIndex()];
    for (var i = 0; i < blk.Pages().length; i++) {
        var _p = blk.Pages()[i];
        if (_p.Index_() == index) {
            p = _p;
        } else if (_p.IsPageSelected()) {
            p0 = _p;
        }
    }
    setWait(true)
    if (p != null) {
        if (!p.IsDataLoaded()) {
            p.GetPageItems(userSet, function (ok) {
                if (ok) {
                }
                loadingPage = false;
                setWait(false)
            });
        } else {
            updateCurrPage(p, p0);
            loadingPage = false;
            setWait(false)
        }
    } else {
        loadingPage = false;
        setWait(false)
    }
}

which is described above (see here). After the page items is obtained, it sets the CurrentPage (KnockoutJS) observable of the global userSet variable to the clicked page frame. Since the item list is bound to CurrentPage().Items of userSet:

<table class="gridview table-hover table-striped table-bordered">
    ...
    <tbody data-bind="foreach: CurrentPage().Items">
        <tr data-bind="css: {selected: IsEntitySelected()}, 
                             click: function(data, event) { 
                                        selectUser(data, event); 
                                    }">
            <td>
                <!-- ko if: hasIcon -->
                <img data-bind="attr: {src: iconUrl}" />
                <!-- /ko -->
                <!-- ko ifnot: hasIcon -->
                <span class="ion-person"></span>&nbsp;
                <!-- /ko -->
                <span data-bind="text: data.Username"></span>
            </td>
            <td style="width:25px; white-space:nowrap;">
                <a href="#" data-bind="click: function(data, event) { 
                              ShowUser(data, event); }" title="...">
                   <span class="ion-navicon"></span>
                </a>
            </td>
            <td>
         <span data-bind="text: member.Email"></span>
            </td>
            <td>
         <span data-bind="text: member.MemberStatus"></span>
            </td>
            <td>
         <span data-bind="localdatetime: data.LastLoginDate"></span>
            </td>
            <td>
         <span data-bind="localdatetime: member.LastActivityDate"></span>
            </td>
        </tr>
    </tbody>
</table>

the list is updated automatically.

4.3.4 The user details ^

When a row in the above list is clicked, it invokes the selectUser global method

function selectUser(data, event) {
    for (var i = 0; i < 
             userSet.CurrentPage().Items().length; i++) {
        var e = userSet.CurrentPage().Items()[i];
        if (e.IsEntitySelected() && e != data) {
            e.IsEntitySelected(false);
        }
    }
    userSet.CurrentSelectedUser(data);
    data.IsEntitySelected(true);
    userSet.CurrentPage().CurrentItem(data);
    updateEntityDetails(data);
    event.stopPropagation();
    return false;
}

the user details is loaded in the call to the updateEntityDetails global method, which is described above (see here).

The last part of the SearchMembers.cshtml page is used to display the details of an selected member:

<div id="user-details" data-bind="with: CurrentPage">
    <!-- ko if: typeof CurrentItem != 'undefined' -->
    <!-- ko if: CurrentItem() != null -->
    <div class="user-details" data-bind="with: CurrentItem">
        @Html.Partial("_MemberDetailsPartial")
    </div>
    <!-- /ko -->
    <!-- /ko -->
</div>

The included partial view _MemberDetailsPartial.cshtml is visible only when the CurrentPage observable of the global variable userSet is not null and the CurrentItem observable of the said CurrentPage observable is also not null. Partial view _MemberDetailsPartial.cshtml contains detailed layout of the user detail display shown below.

.

Figure: User details display.

It binds to the JSON object obtained from calling the GetBriefMemberDetails method of MemberViewContext class (see here) to the corresponding html elements inside of the _MemberDetailsPartial.cshtml file. Since the codes involved are quite lengthy, they will not be displayed here. Interested user can goto the referenced file to get the details.

4.4 The Styles ^

As it is described in the first article here, LESS system is used to create and maintain the final CSS style files.

The visual effects in the view described above would not be what it looks like now without proper CSS styling. In fact, a large portion of time used to create a functional SearchMembers.cshtml page was spent on tweaking the .less files interactively to get a look that feels good enough.

However, it is not going to be described in further details in this article because this article put its emphasis on the software aspect of things. The styling part could be an article of its own. An interested reader can also get his/her hand dirty to tweak his/her own styles.

4.5 And the Vocabulary ^

There are a few short comings in the querying vocabulary provided by data services

  1. They are generated from data schema according to certain fixed rules (see the 2nd last section section on entity graph) that apply to all data services which may looks too generic for a particular application. It can not reflect the applications particularity: context, nature, etc., and/or it can not provide a friendly interactive environment or experience for users.
  2. It could be that globalization is provided by the data service. A designer of a data system normally use a particular language pattern, like English, to design the data schema from which the query vocabulary is generated. However, that language may not be used effectively by at least some of the users of the system. Albeit globalization can be done at the data service level, it is best to delegate the task to applications, for the same reason as above.

4.5.1 Query token and expression ^

A query expression is consisted of a series of tokens. A token here is a structure has two key sets of attribute: 1) what is its value; and 2) what it is know as, as it is shown in the following.

function QToken(val) {
    var self = this;
    // -- its value ---
    self.TkType = "";
    self.TkName = val;
    // -- what it's known as ---
    self.DisplayAs = val;
    self.TkClass = "filternode";
    ...
}

The "value" part is used by computer and the "known as" part is used by human in understanding the meaning of the expression. Except for a few cases common to all data services (like {TkName='asc', DisplayAs='Ascending'} pair, {TkName='desc', DisplayAs='Descending'} pair, etc., which is also globalized}, DisplayAs is assigned the same value as the one assigned to TkName when the token is initially generated from the data service.

The what is called vocabulary here refers to the one for a human to interact with the system correctly. This is the part of the expression that can be customized inside of an application that uses a data service.

If one inspect the "source" function for the auto-complete expression input control (see, e.g. here) one find the following visual options are provided:

    var opts = s.CurrentSorters().Options;
    var arr = opts.filter(function (val) {
        return val.DisplayAs.toLowerCase().indexOf(
                          request.term.toLowerCase()) == 0;
    }).sort(tokenSortCmp);
    if (arr.length != 1 || deleting) {
        response($.map(arr, function (item) {
            return { 
                     label: item.DisplayAs == "this" ? 
                            item.TkName : item.DisplayAs, 
                     value: item.DisplayAs 
                   };
        }));
    }

Namely, it is the value of DsiaplayAs that is used as user input option, not the one for the TkName. Therefore if one could change the value of DsiaplayAs, then one can change how token is input while leaving the meaning of the token unchanged for the machine.

4.5.2 Customization ^

Customization is consisted of two parts:

  1. Token filtering. Some token can be filtered out of the available token options for a user.
  2. Token name change. Change the value of DsiaplayAs so that it can be understood, used, etc., better by targeting users.

To do it, the token options can be pre-processed inside of the GetNextSorterOps and GetNextFilterOps methods of the UserSet view model by a global method named tokenNameMap, if defined. For example, in GetNextSorterOps we have

// after load the options from the service
for (var i = 0; i < r.Options.length; i++) {
    var tk = new QToken();
    tk.CopyToken(r.Options[i]);
    if (tokenNameMap) {
        // when token customization exists, customize it
        if (tokenNameMap(tk, setName, false)) {
            self.CurrentSorters().Options.push(tk);
        }
    } else {
        self.CurrentSorters().Options.push(tk);
    }
}

If tokenNameMap is defined, only those ones accepted by it (namely true value is returned from it) is added to the options for a user. tokenNameMap is defined in an additional JavaScript response.

4.5.3 Configuration based method ^

Pros: More structured and less technical. It can be globalized.

Cons: Less flexible. Server restarts whenever the configuration file changes so it can not add rules on the fly.

4.5.3.1 The JavaScript ^

The included customization JavaScript is dynamically generated on the server side base on information specified in a custom section of the Web.config file, which is described in more details in the following sub-section. The JavaScript is referenced at the application level query pages in the following way

    <script src="@Url.Content("~/JavaScript/QueryCustomization?src=")"></script>

Application level customization normally contains more restrictive rules than administration level customization, which is included via a different url:

    <script src="@Url.Content("~/JavaScript/QueryAdminCustomization?src=")"></script>

The empty src parameter src= here means that the default data source name defined in the Web.config file should be used. For example, for the current web application

<appSettings>
 ...
 ...
 <add key="DefaultDataSource" value="MembershipPlus" />
 ...
</appSettings>

As it is shown, the JavaScript generator is handled by the QueryCustomization method or the QueryAdminCustomization method of the JavaScriptController controller class

public class JavaScriptController : BaseController
{
    private static QueryCustomization QueryTokenMap = null;
 
    public JavaScriptController()
    {
        if (QueryTokenMap == null)
        {
            QueryTokenMap = ConfigurationManager.GetSection(
                                            "query/customization") 
                            as QueryCustomization;
        }
    }
 
    [HttpGet]
    public ActionResult QueryAdminCustomization(string src)
    {
        if (QueryTokenMap == null || !QueryTokenMap.ConfigExists)
        return new HttpStatusCodeResult(404, "Not Found");
        StringBuilder sb = new StringBuilder();
        if (string.IsNullOrEmpty(src))
            src = ConfigurationManager.AppSettings["DefaultDataSource"];
        _queryCustomization(sb, src, QueryTokenMap.GetAdminFilters);
        return ReturnJavascript(sb.ToString());
    }
 
    [HttpGet]
    public ActionResult QueryCustomization(string src)
    {
        if (QueryTokenMap == null || !QueryTokenMap.ConfigExists)
        return new HttpStatusCodeResult(404, "Not Found");
        StringBuilder sb = new StringBuilder();
        if (string.IsNullOrEmpty(src))
            src = ConfigurationManager.AppSettings["DefaultDataSource"];
        _queryCustomization(sb, src, QueryTokenMap.GetAppFilters);
        return ReturnJavascript(sb.ToString());
    }
    
    private void _queryCustomization(StringBuilder sb, string src, 
                              <Funcstring, string, SetFilters> getfilters)
    {
       ...
    }
    
    ...
}

It is not going to be described in more details here. Interested readers can go directly into the source file to understand how it is done after finish reading the next sub-section.

The JavaScript file CustomMembershipPlus.js under the Scripts/DataService sub-directory of the web application contains a copy of the generated content according to the current Web.config file.

4.5.3.2 The configuration ^

In the configuration based method, the customization can be done in a customization section in the Web.config file:

  <configSections>
    <sectionGroup name="query">
      <section name="customization" 
               type="...Configuration.QueryCustomizationHandler,
                     MembershipPlusAppLayer" />
    </sectionGroup>
  </configSections>

Namely the customization information is stored under the query/customization node in the Web.config file and is processed by the QueryCustomizationHandler class. All related types are currently defined in one file, namely the QueryCustomizationCfg.cs file under the Configuration sub-directory of the MembershipPlusAppLayer45 project. The QueryCustomizationHandler class delegates the parsing of the customization section to the QueryCustomization class defined in the same file, which builds up data structures used to generate the customization JavaScript.

The following is a sample customization:

  <query>
     <customization>
       <global>
         <maps>
           <map from="&amp;&amp;" to="and" />
           <map from="||" to="or" />
           <map from="asc" to="asc" />
           <map from="desc" to="desc" />
         </maps>
       </global>
       <datasource name="MembershipPlus">
         <set name="User">
           <filters type="admin" allow-implied="false">
             <filter target="sorting" 
                        expr="{0}.indexOf('Password') == -1" />
             <filter target="filtering" 
                        expr="{0}.indexOf('Password') == -1 || 
                        {0}.indexOf('Password') != -1 &amp;&amp; 
                        {0}.indexOf('Failed') != -1" />
           </filters>
           <filters type="app">
             <filter target="sorting" expr="*password*" 
                        case-sensitive="false" />
             <filter target="filtering" expr="*password*" 
                         case-sensitive="false" />
           </filters>
           <maps>
             <map from="UserAppMember." to="Membership." />
             <map from="UserDetail." to="Detail." />
             <map from="TextContent" to="keywords" />
             <map from="AddressInfo" to="Address" />
             <map from="Username" to="Name" 
                     to-resId="92f1b1481fa6ff46c4a3caae78354dac" 
                     globalize="false" />
           </maps>
         </set>
       </datasource>
     </customization>
  </query>

The optional <global> node contains common mapps for all data sources and data sets. It is followed by a series of <datasource> node whose name is the name of the data source, "MembershipPlus" in the present case. Each <datasource> contains a set of <set> child nodes whose name is the entity class name of the set. Each <set> contains <maps> child node that hold <map> nodes and <filters> child node that hold <filter> child nodes.

A <map> node has the following attributes:

Name Type Optional Default Description
from String No The TkName value. It is the name of one of the properties of the corresponding entity set <set>.
to String No The mapped DisplayAs value.
to-resId Guid Yes Hex encoded form of 16 byte Guid of a globalized resource for "to".
globalize Boolean Yes true If "to-resId" is initialized with a valid value and this attribute is set to "true", then the mapped DisplayAs will try to use globalized resource instead of the "to" value.

A <filters> node has the following attributes:

Name Type Optional Default Description
type Enum No It determines the kind of the set of filters for the web application. The current allowed values are "admin" and "app". Filters of type "admin" apply to the administration pages and those of type "app" apply to the normal more restrictive pages. The list is sure to expanded in future as the application get more sophisticated.
allow-implied Boolean Yes true It defines the default behavior in interpreting missing or unspecified filters for this type of filters. If it is true, then all mis-matched tokens is allowed, otherwise all mis-matched tokens are disallowed.

A <filter> node has the following attributes:

Name Type Optional Default Description
target Enum Yes all It determines which part of the query expression the filter applies. Allowed values are: "sorting", "filtering" and "all". If left un-specified, the value "all" is assumed.
allowed Boolean Yes allow-implied It determines how to treat a matching token. If true, the matched token is allowed, otherwise it is not allowed. If it is not specified, then the value of the "allow-implied" attribute of its parent node <filters> is used.
case-sensitive Boolean Yes false Whether or not the filter matching is case sensitive. The default is false.
expr String No The pattern of its value determines what type of matching method it uses:
  • If has pattern "*"+<str>+"*": filter matches if the token name TkName contains <str>. For example "*abc*" -> contains "abc".
  • If has pattern <str>+"*": filter matches if the token name TkName starts with <str>.
  • If has pattern "*"+<str>: filter matches if the token name TkName ends with <str>.
  • If has pattern "["+<str>+"]": filter matches if the token name TkName matches (JavaScript) regular expression <str>. For example if expr="[/BobIsKool/g]", then the filter matches if TkName contains "BobIsKool".
  • If it matches the .Net regular expression pattern "\{\d+\}" then filter matches is JavaScript expression template in which the string format place holder {0}, {1}, etc., are replaced by JavaScript local variables or parameters.
  • Otherwise, the filter matches if the value is the same as TkName.
4.5.3.3 Context dependency ^

The current customization system is a simple one in that it is view context dependent, unlike the query intelligence system supported by the data service. Therefore for a given view of a data set, the token mapping and filtering apply to properties of any other data set that the set is related to.

For example suppose one is querying the User data set, when encounter the Description token following the token Detail., rules for the User set, instead of those for the UserDetail set will be used. Here Detail. refers to the set of user details record in the UserDetail data set that the current user is associated (via a foreign key). Note, the more concise display name Detail. for the token is mapped from the original value UserDetail. that is used to refer them internally (see 2nd last section) because there is a map in the customization section of Web.config:

 <map from="UserDetail." to="Detail." />

for the User set. That is to say if one add

 <map from="Description" to="Descr" />

sibling node of the above one, then the Detail.Description is no longer correct input. Instead Detail.Descr is the correct one. However when one now goes to a page used to query the UserDetail set (suppose it is implemented), the newly added rule will have no effect there because it is only mapped under the user query context.

4.5.4 Code based method ^

An alternative is to write the mapping script file manually. The file CustomMembershipPlus.js under the Scripts\DataService sub-directory of the web application is an example, it is equivalent to the one generated according to the above configuration.

The default method is the configuration based one described above. To use the current method, the SearchMembers.cshtml page file under the Views\Query sub-directory should be modified. The line

    <script src="@Url.Content("~/JavaScript/QueryCustomization?src=")"></script>

should be changed to

    <script src="@Url.Content("~/Scripts/DataService/CustomMembershipPlus.js")"></script>

Pros: Flexible.

Cons: Requires knowledge in JavaScript programming. It can not be globalized.

5. Prepare the Data Service ^

Data service seting up was described in article I. The data service for this article is extended to support full text indexing and searching over a few text properties:

  1. The Description property of an UserDetail entities. This property normally contains text blocks that is best to searched using KS method.
  2. The AddressInfo and Comment properties of Communication entities. They may also contain text blocks that are best to be searched using KS method.

The addition of these full text searchable properties does not cover all possibilities. For example the Title and Description property of Announcement entities are also suitable candidates for performing full text indexing and searching, etc. The reason for including only what is needed now is for simplicity purposes, it is not difficult for us to incrementally add new ones later when it is about to be talked about.

The sample data contains 1478 randomly selected members that a user can query.

5.1 Full text indexing ^

5.1.1 Native KS ^

There is a common syntax for search native full text indices. For any text properties, there is an operator called native-matches that could be used to instruct the back-end database engine to do full text search on the targeting property, but it is effective only if full text indexing and searching is setted up for the said property. For example, when the Title property of Announcement entities had full text index setted up, then the following expression will do full text search on them, namely

Title native-matches "keyword"

where "keyword" is the keyword to search for.

5.1.2 Unified full text indexing and KS ^

Full text search is not a SQL standard. For a database agnostic data service, one can not assume

  1. There is in fact a relational database engine back-end.
  2. The back-end support full text indexing and searching.
  3. The back-end full text searching has common search syntax (namely for more advanced search involving more than one keywords).
  4. The back-end full text indices can be transferred from one back-end to another, namely they can be made compatible.
  5. etc.

The unified full text indexing and search system of the data service is designed to overcome these problems.

The general syntax for a unified full-text search on a particular data set is

TextContent matches pattern { <query-expr> } <paging opts> 

where <query-expr> is the keyword search expression, and the <paging opts>, for which the query intelligence system will provides available options, is used to select the matching data. The current system uses Lucene.Net to realize text indexing, so the syntax for the <query-expr> is that of the Lucene query syntax (see here for details). The system will guide a user to formulate simple <query-expr>s. It will get better in the future. For more complex ones that does not fit in the existing patterns, prefix the start of the expression using $, after which the user can enter any expression freely. Note, the unified full-text search option (namely the TextContent expression keyword) is only available for those data sets that have it declared when the system is produced.

Notice that since we had a rule

<map from="TextContent" to="keywords" />    

in the query customization section for the User data set in the Web.config file, any expression that starts a unified full text search start with keywords matches ... instead of TextContent matches ... in a user querying context. For example, when trying to search the full text index for the AddressInfo property from all the Communication records associated with a user, the following expression should be used, namely

Communication.keywords matches pattern { AddressInfo "unicons" } first 333

instead of

Communication.TextContent matches pattern { AddressInfo "unicons" } first 333

Due to the view context dependency described above, this rule does not apply to other querying contexts without an explicit mapping rule like the above being added to the corresponding customization node for the set.

5.1.3 The Indexing Tool ^

The data service comes with an indexing, inter data source synchronization program named ServiceDataSync.exe. that one can used to build the unified full text indices. It is included as a download above.

To build indices, go to the "main operations / text indexing page" after the program starts up. One should first of all select a data service base url and then set the output directory, like, e.g. the following.

.

Figure: Snapshot of a progressing index building process.

Supply the data service (for Membership+ system) base url that a user is interested and then set the output directory to the App_Data\MembershipPlus\Indices sub-directory of the same data service website. This directory is where the data service looks for full text search indices.

Push the start button after the above steps and wait for the completion of the process, as shown above.

Note: The included sample indices in the demo data package is built from the sample data in the App_Data\MembershipPlus\Data sub-directory, please do not use it for any other data sets.

6. Data Graph & Query ^

The data service supports operations on the corresponding relational data source. Relational data are best to be modelled using directed graph data structure than any other ones, like tree structures commonly used in object oriented (OO) world in various form. However, most OO frameworks can support data graph structures without any technical problems.

Querying of relational data source in our approach thus involves the construction of expressions used to refer to other related entities of a given syntactic context.

6.1 Entity graph navigation ^

Each entity in a relational data source that belongs to one kind of data set may depend on some other entities that belong to other kinds of data set, including its own kind. This same entity may also has dependencies from other entities that belong to other kinds of data set, including its own kind as well. These inter-dependencies create a directed graph of entities that one can "walk" from one node to another following some navigational rules to be described in the sequel.

For the Membership+ system, a schematic view of the data graph is given here. It provides a visual and schematic map that can help a user to navigate the graph more easily. There the direction of the relation link represents the kind of dependency, namely the entity (A) that the link enters depends on the entity (B) that the link comes out, namely A depends on B or B is depended upon by A. Here B->A is a one to many relationship.

To simplifies the view, this figure does not contain many details, especially when there are multiple dependency (see the following). For more precise information on how an entity is related to others, one can find it in two ways:

  1. The client API document that comes with the data service contains the name, and nature of dependency. The "remarks" section of class level document for each data model of a kind of entity contains a list of "entities that this entity depends on", if any, and a list of "entity sets that depend on this entity", if any.

  2. The source code for the data model of a kind of entity contains the same kinds of information. They are in the regions #region Entities that the current one depends upon and #region Entities that depend on the current one.

The naming convention for the related entities and entity sets is dependent upon whether or not the dependency is single or multiple. When a data set depends on another data set once or more technically it has only one foreign key that references the other set in the data schema, then the dependency is single. Otherwise it has multiple dependency on the other set.

The following is a specification of the naming convention. Let's define a base object identity for related entities of a given one, namely base-object-id first:

  • Single dependency: base-object-id := <entity-name>, where <entity-name> is the corresponding name of the related entity.
  • Multiple dependencies: base-object-id := <entity-name> + "_" + <foreignKey_name>, where <foreignKey_name> is the name of the corresponding foreign key property for that dependency.

Depending on the nature of the dependency, we have the following naming convention

  • The name of the entities that the current entity depends upon is base-object-id + "Ref", For example, an entity in UserDetail data set depends on an entity in User data set, the corresponding property of the data model entity has the name of UserRef according to the rule. There are two exceptions however: 1) when the related entity belongs to the same data set as the current one (namely it is self-referential), like the Role data set, then the name is UpperRef; and 2) when it is multiple dependency one, the "Ref" suffix is not appended (this exception could be removed in the future).
  • For those entities that depend upon the current entity, since the relationship is one to many, they are recorded in different sets of entity records:
    • base-object-id + "s": The type of it is <entity-name> + "Set". It defines the related set as a sub-set of entities declaratively (see here), but not the loaded sub-set itself.
    • base-object-id + "Enum": Representing the whole related set. This is mostly used on the service side.
    • "Changed" + base-object-id + "s": Array of some members belonging to related set. It can be used to hold changed or new entities in a add or update operation involving an entity graph. It can also be used to hold entity graph elements when doing recursive partial entity graph loading (see here).

For example, a UserAssocInvitation entity depend on User entities multiple times since an (social) association invitation has a sender and a receiver, therefore the two sides of the relationship have the following

  • A UserAssocInvitation entity has User_FromUserID property (of type User) representing the user who sent the invitation and User_ToUserID property of the same type representing the user who the invitation is targeting.
  • A User entity has {UserAssocInvitation_FromUserIDs, UserAssocInvitation_FromUserIDEnum, ChangedUserAssocInvitation_FromUserIDs} and {UserAssocInvitation_ToUserIDs, UserAssocInvitation_ToUserIDEnum, ChangedUserAssocInvitation_ToUserIDs} property sets representing the invitations sent by the user and the ones received by the user.

6.2 Referential Syntax ^

6.2.1 Filter expressions ^

After getting familiar with the entity graph navigation (naming) convention, the syntax used to reference related entities in a query becomes quite simple. Depending on the direction of the reference, they follows the following rule:

  • When referencing entities that the entity in the current syntactic context is depending upon, uses the corresponding property name as base name, which is called base-name in the following. There are two cases
    • If the corresponding foreign key is not nullable, then there is one way to reference, namely, base-name + ".". The back end will change the entity context after encounter this token to what it refers to.
    • If the corresponding foreign key is nullable, then in addition to the above one, there is another one, namely base-name, which is used to construct predicates on whether or not the referenced entity is null. However, the best way to achieve this is to use the foreign key rather than the corresponding entity for such a purposes.
  • When referencing entities that depend upon the entity in the current syntactic context, use the property name for the entity set without the "s" suffix plus the dot character ".". For example, use UserAssocInvitation_FromUserID. to refer to the set of association invitation that a user sent when in the User (syntactic) context. The back end will change the entity context after encounter this token to what it refers to.

Starting at a given entry syntactic context, an expression can be extended to reach any other ones that the current one is connected with using the above "navigation" rule. The good news is that a user does not have to remember the rules in exact form since the query intelligence system will guide him/her in the navigation process.

In addition, tokens like UserAssocInvitation_FromUserID. may looks verbose and ugly given a view context in which the meaning of many parts of it is obvious without perceptional ambiguities. If this is the case, one can always create alias for it using the customization system described here to "shorten or simplify" it. For example in a user social connection invitation management page, the rule UserAssocInvitation_FromUserID. -> Sent. is much better than the original one since everything else is assumed by the view context.

6.2.2 Sorter expressions ^

Sorting can also be performed according to properties of related entities of a given syntactic context. However, unlike the filtering case, it can only performed on the related entities that the entity correspond to the current syntactic context depends upon. Going in the other direction does not make much sense due to they have one to many relationships.

Since the User data set does not depend on other sets, it can not be used as an example for the current case. For the sake of demonstration, let's take the currently not implemented UserDetail data set query view for example. Suppose one would like to sort according to

CreateDate asc UserRef Username asc

which means sort CreateDate (of a UserDetail entity) in ascending order, then its UserRef's Username (of a User entity) in ascending order (note there is no "." after UserRef). It will be fine if he/she just stop there.

What If he/she want to continue, what are next options?

It is the sorting options for User data set due to the fact that the syntactic context is changed to it due to the UserRef token entered.

How to go back? The answer is use the this operator. This operator will bring the syntactic context back to the entry one no matter how deep the current one had reached. So the following expressions is correct

CreateDate asc UserRef Username asc this BirthDate asc ID desc

where BirthDate and ID refer to the properties of a UserDetail entity, not the ones for a User one.

6.3 A few examples ^

All of the the following query expressions, verbose as it seems to be, can actually be constructed easily using the query input interface described in the present article after a few key strokes.

  • Starting from the User entity set, the query expression used to select users who is a member of an application, is

    UserAppMember.Application_Ref.Name == "MemberPlusManager" && 
    ( UserAppMember.SearchListing is null || UserAppMember.SearchListing == true )

    Here UserAppMember. lead to UserAppMember entity set context and Application_Ref. lead to the Application_ entity set context in which it is required that the Name property of the corresponding entity is the same as "MemberPlusManager".

  • Start from the User entity set, the query expression used to select users whose home address matches keyword "unicons", is

    Communication.TypeID == 1 and Communication.keywords matches pattern 
    { Address "unicons" } first 100

    where the and operator is mapped from the && one ( see the user global customization section of the Web.config file). Note, the quote around the string value are auto generated, a user should not type them in!. Here it selects only type 1 of the communication channel (namely the HomeAddress type) and also requires that the Address (mapped from AddressInfo, see the user query customization section of the Web.config file) attribute of the channel to contain keyword "unicons". If one find using a hard coded type id in the query is not ideal, one can construct a more complex query using the channel type name to filter as well, namely

    Communication.CommunicationTypeRef.TypeName == HomeAddress and 
    Communication.keywords matches pattern { Address "unicons" } first 100

    Here the value HomeAddress is not quoted because it is regarded as a discrete (enumerable) value when the data service is built.

  • If a reader do not want that much accuracy or is trying to get something "un-expected", he/she use less restrictive filter

    Communication.keywords matches pattern { Address "unicons" } first 100
  • If a reader wishes to be more sophisticated in keyword matching, he can construct the follow search phrase

    Communication.keywords matches pattern { 
           Address "california" OR Address "canada" OR Comment "ok" 
    } at page 1 where size of pages is 100
  • Finding self described "geeks":

    Detail.keywords matches pattern { "geek" } first 100

    Here, since the UserDetail data set has only one property full text indexed, the name of the property does not appear in the expression.

  • Finding self described "geeks" or "nerds":

    Detail.keywords matches pattern { "$geek OR nerd" } first 100

    Here $ operator is used to escape the following expression so that arbitrary Lucene keyword search pattern can be specified. Here the system will not guide the user to construct nor will it check the validity of the Lucene search sub-expression.

7. History ^

  • 2014-03-19. Article Version 1.0.0, Initial publication.
  • 2014-03-25. Article Version 1.0.5. Data schema changed: Added MemberNotificationType, MemberNotification data sets. Added ConnectionID, AcceptLanguages and SearchListing properties for the UserAppMember data set. Added ApplicationID foreign key property for UserGroup data set. Minor changes to service documents.
  • 2014-05-06. Article Version 1.2.0. The data service now run under .Net 4.5.1 and Asp.Net Mvc 5 that contains significant expansion and improvements. The web application is upgraded to run under the latest libraries. Many new data sets are added to support future scaleout capable SignalR push notification features.

If a reader have sufficient knowledge about the Git source control system, he/she can follow the Git repository on github.com for the project. The source code for the current article is maintained on the codeproject-3 branch, namely here.

License

This article, along with any associated source code and files, is licensed under The Apache License, Version 2.0

Share

About the Author

Shuqian Ying
Founder CryptoGateway Software Inc.
Canada Canada

His interests in software research and development include security, domain specific meta-language development technologies and tools, meta-language driven code generation, generation of non-invasive and virtual SQL database for "un-structured" data (sqlization), automated relational data service production technologies, file system sqlization technologies and products, structured multi-lingual documentation tools and technologies, sqlization of user's personal data, like emails, etc.. He likes to use distributed code version control system to manage codes. For that reason, a tool to manage GIT repository forest (related GIT repositories) was even developed internally.

He was a physist before year 2000. He studied theoretical physics (the hidden symmetry between the past and future, quantum field theories, mirror universe, cosmological dark energies etc.) in which he think to had made fundamental breakthroughs[^] but the community is either not ready for it or is actively rejecting/ignoring it Smile | :) .





Most recent free tools to share:

  • Vertical Search Portal[^] - gateway to multiple data services, where in depth information query is made possible.
  • Email Aggregation Manager[^]. It downloads emails from email boxes and lets user to sort, query and organize them using sql like structured query user interface.

Comments and Discussions

 
Questionexecuting PinmemberMember 109189462-Jul-14 2:12 
AnswerRe: executing PinmemberShuqian Ying2-Jul-14 12:52 
Questiongood one PinprofessionalHarikrishnanvr17-Apr-14 9:07 
GeneralMy vote of 5 PinprofessionalVolynsky Alex27-Mar-14 8:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150326.1 | Last Updated 28 May 2014
Article Copyright 2014 by Shuqian Ying
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid