Client Side Web Application Primer

• Download source - 1.74 MB

This article can also be found on my blog.

Introduction

I plan to share my experiment with client side application and MVC backend. In this sample, as many other web site before I use many 3rd party libraries (hint: look at README / package list: EntityFramework (Extended), jQuery, Knockout, chosen, bootstrap, datatables). But I don’t plan to dwell much on them in this post. Instead I want to focus on how I use T4 Templates to generate strongly WebAPI server proxy in TypeScript and use Knockout to drive the UI. With a little bit extra on WebAPI and SQL.

This post is intended for web developer having basic to medium knowledge of MVC, JavaScript, jQuery.ajax(), TypeScript. I am not going to explain the whole application but just the challenging parts and the solutions I implemented. For the rest there is the source code.

My sample application (largely inspired from work needs) is a 3 pages application. A first page manage dynamic questionnaire (with dynamic groups of dynamic questions), a second page can answer those questionnaires and a third can query them.

Compatibility remark: this app has been tested on IE 8, IE 11, Chrome 30, Firefox 24

Screenshot of the 3 screens are at the top. 

Getting Started

I am using VS2013 for this and if you don’t have it some option might slightly different. VS2012 at least is required for the Visual Studio – TypeScript plugin to work. To be able to open the solution or replicate it, you will need to install the following:

1. First install the TypeScript plugin for visual studio (I wrote this app with TypeScript 0.9.1).
2. From tools => extensions: install Web Essentials
3. From tools => extensions: install T4 Tangible editor, it’s not required but makes working with T4 template more pleasant.
4. Prepare the database with the 2 SQL scripts (at the root if the ZIP file). “createDB.sql” create the schema (question) and tables, “reports.sql” create the search related SQL code (One SQL table data type and one stored procedure) (as I’m not using EF code first).

Now you can either use the provided project (you need to modify the connection string the Web.config to point to your DB first!) or:

1. Create a basic blank web project supporting MVC and WebAPI.
2. Install required packages (list provided in README.txt) with NuGET using the package manager console.
3. Create a “~/MyScripts” folder, where custom / handwritten scripts will be written, so they are clearly separated from 3rd parties (personal preference, doesn’t matter much). 

• Remark I’m using chosen instead of the more recent select2 for my combobox as select2 seems to be very slow on IE8.
• Remark I’m using jQuery 1.10.2 instead of 2.x for IE8 support.
• Remark I’m using EF5 (instead of newer EF6) as EF.Extended (which I use for Future query) doesn’t support EF5 yet. Future query is used to group multiple query in one database command, hence reducing network and connection overhead.
• Remark I choose Knockout over AngluarJS for my template engine because Knockout is fully declarative. I.e. it needs absolutely no code to setup and choose templates. Whereas AngularJS is a mix of declarative / imperative and you can’t escape writing infrastructure code when using template heavily.

About WebAPI

Web API looks very much like MVC. It has controllers (inheriting from ApiController instead of Controller) and returning plain objects (instead of ActionResult). The HTTP headers are used to select the in and out serialization method. It also has its own routing mechanisms, where one should set a route that won’t conflict with MVC (so as to be unambiguous).

Here is the WebAPI configuration I use in my project:

public static void Register(HttpConfiguration config)
{
    config.MapHttpAttributeRoutes();
    config.Routes.MapHttpRoute(
        name: "DefaultApi",
        routeTemplate: "api/{controller}/{action}",
        defaults: new { }
    );
    // Uncomment the line below to disable XML serialization
    //config.Formatters.Remove(config.Formatters.XmlFormatter);
}

Unlike the out of the box WebAPI config I specify the {action} in my route, as my WebAPI controller will have many methods, returning heterogeneous type. Unlike most sample on the net where there is one controller per data type with 4 actions (select/insert/update/delete).

Here is a much simplified version of my WebAPI controller:

public class SearchResult
{
    /** properties... **/
}
public class QuestionaireApiController : ApiController
{
    public List<searchresult> GetAllAnswers()
    {
        List<searchresult> results;
        /** do the thing, return the result **/
        return results;
    }
    /** more methods **/
}

This can be called with a simple HTTP GET request at: http://MyApp/api/QuestionaireApi/GetAllAnswers.

And it will return an object (as JSON / XML or whatever other format it supports and the query specifies).

Web API, use the start of the name of a method to check what HTTP method it supports but one can also decorate with [HttpGet], [HttpPost], etc… to specify the method.

Argument are passed by default on the URL, but one (and only one) argument can be passed as request body. It is useful for complex object or model, that can hardly be passed in the URL, as in:

[HttpPost]
public List<searchresult> SearchAnswers(bool isOrAnd, [FromBody]List<searchcriteria> criterium)
{
    List<searchresult> results;
    /** do the thing, return the result **/
    return results;
}</searchresult></searchcriteria></searchresult>

Remark HTTP GET method don’t support FromBody parameter.

About T4 Code Proxy Generation

I would like to encapsulate my WebAPI as a ServerProxy class that I can just call. The above GetAllAnswer() WebAPI method above could be encapsulated with an AJAX call as follows:

export class ServerProxy {
    cache = false;
    timeout = 2000;
    async = true;
    constructor(public baseurl: string) { }
    GetAllAnswers(): JQueryPromise<array<isearchresult>> {
        var res = $.ajax({
            cache: this.cache,
            async: this.async,
            timeout: this.timeout,
            dataType: 'json',
            contentType: 'application/json',
            type: 'GET',
            url: this.baseurl + 'api/QuestionaireApi/GetAllAnswers',
        })
        ;
        return res;
    }
}

Thanks to TypeScript it is strongly typed and would help removing parameter errors. However what I really would like is the server proxy code to be automatically synchronized or generated from my code. Here enter T4!

If you right click somewhere in the Solution Explorer, you can create a new (T4) Text Template

I won’t go into much about detail on the intricacies of T4 templates, MSDN is here for that. Here I want to explain how to explore the current project’s code.

When one create a T4 template, the first line will look like that:

<#@ template debug="false" hostspecific="false" language="C#" #>

The hostspecific property is the one that will give you access to VS data. It is false by default, you want to make it true.

When it is true you can access the some visual studio service, of interest there are those 2:

DTE DTE { get { return (DTE)((IServiceProvider)this.Host).GetService(typeof(DTE)); } }
Project ActiveProject { get { return DTE.ActiveDocument.ProjectItem.ContainingProject; } } 

The DTE interface is in the EnvDTE namespace. This is the namespace one would use to browse the project for code files and find their class and and methods.

Armed with that one can explore all files in the project, find classes and methods. A Project has a ProjectItems property which contains ProjectItem (which also has a ProjectItems property). Each ProjectItem has a FileCodeModel property which might or might not be null (whether its a code file or not).

The FileCodeModel has a CodeElements property which enumerate CodeElement (which also have a Children property of CodeElements).

Each CodeElement interface has a property Kind which tells you what it is (class, function, interface, attribute, and so on…) and then it can be cast to the appropriate interface for more info (CodeClass, CodeFunction, …).

I.e., the following code can be written to enumerate the project:

DTE DTE { get { return (DTE)((IServiceProvider)this.Host).GetService(typeof(DTE)); } }
Project ActiveProject { get { return DTE.ActiveDocument.ProjectItem.ContainingProject; } }
IEnumerable<projectitem> EnumerateProjectItem(ProjectItem p)
{
    yield return p;
    Level++;
    foreach (var sub in p.ProjectItems.Cast<projectitem>())
        foreach (var sub2 in EnumerateProjectItem(sub))
            yield return sub2;
    Level--;
}
IEnumerable<projectitem> EnumerateProjectItem(Project p)
{
    foreach (var sub in p.ProjectItems.Cast<projectitem>())
        foreach (var sub2 in EnumerateProjectItem(sub))
            yield return sub2;
}
// enumerate projects and code elements
IEnumerable<codeelement> EnumerateCodeElement(CodeElement element)
{
    yield return element;
    Level++;
    foreach (var sub in element.Children.Cast<codeelement>())
    {
        foreach (var sub2 in EnumerateCodeElement(sub))
            yield return sub2;
    }
    Level--;
}
IEnumerable<codeelement> EnumerateCodeElement(CodeElements elements)
{
    Level++;
    foreach (var sub in elements.Cast<codeelement>())
    {
        foreach (var sub2 in EnumerateCodeElement(sub))
            yield return sub2;
    }
    Level--;
}
IEnumerable<codeelement> EnumerateCodeElement(FileCodeModel code)
{
    return EnumerateCodeElement(code.CodeElements);
}
IEnumerable<codeelement> EnumerateCodeElement(ProjectItem p)
{
    return EnumerateCodeElement(p.FileCodeModel.CodeElements);
}
// cache usefull TS data
void ParseProject()
{
    if (_allClasses == null)
    {
        _allClasses = new List<codeclass>();
        _allEnums = new List<codeenum>();
        var proj = ActiveProject;
        foreach (var item in EnumerateProjectItem(proj))
        {
            var code = item.FileCodeModel;
            if (code == null)
                continue;
            foreach (var e in EnumerateCodeElement(code))
            {
                switch (e.Kind)
                {
                    case vsCMElement.vsCMElementClass:
                        _allClasses.Add((CodeClass)e);
                        break;
                    case vsCMElement.vsCMElementEnum:
                        _allEnums.Add((CodeEnum)e);
                        break;
                }
            }
        }
    }
}
List<codeclass> _allClasses;
List<codeenum> _allEnums;
List<codeclass> AllClasses
{
    get
    {
        ParseProject();
        return _allClasses;
    }
}
List<codeenum> AllEnums
{
    get
    {
        ParseProject();
        return _allEnums;
    }
}

Now one can explore the code in the current project and write a proxy generator. I won’t go too much in the details of my implementation I will just talk a little more about the result. I got two generators, one to generate TypeScript definition of my JSON exchange object, and one to generated TypeScript server proxy. I created an attribute ToTSAttribute, which I use to flag what I want to be recreated in TypeScript.

I modified my EF template to mark my EF class with ToTSAttribute as I want to manage them with this UI. I generate two TypeScript interfaces for each of my classes. The normal exchanged interface, and a Knockout friendly interface (more on that later).

About TypeScript

Here is a couple of tips I realized on how better use TypeScript.

TypeScrit files are .ts files. They are compiled (when building the solution) in .js file with the same name. Sadly at this stage the .js file are not marked as part of building output and, in case of automated build, one should manually add them to be part of the deployed files!

One solution is to edit project to add each of the .js files produced to it. “Unload Project” then “Edit MyProject.csproj”, then use the DependentUpon tag.

<TypeScriptCompile Include="MyScripts\Common.ts" />
<Content Include="MyScripts\Common.js">
    <DependentUpon>Common.ts</DependentUpon>
</Content>

I was not able to use RequireJS with TypeScript 0.9.1. I just explicitly include all the .js file needed in each page. Fortunately there isn’t that many anyway (yet)!

There is a silly issue about overriding and method declaration, summarized in the code below

class A {
    Who() {
        return "Who.A";
    }
    Who2 = function () {
        return "Who2.A";
    }
}
class B extends A {
    Who() {
        return super.Who();
    }
    // won't compile
    //Who2 = function () {
    //    return super.Who2();
    //}
}

There are two ways of declaring a member method. One as a method (Who) and one as a property being a function (Who2), the later can’t be overridden. And also the intellisense get sometimes confused about the special property “this”.

Class can’t be extended (you can’t add property to a strongly typed class object), but you can always extend interface, as in:

interface IPoint {
    getDist(): number;
}
interface IPoint {
    translate(x: number, y: number);
}

Interface are best used to describe data exchange objects. The T4 generate interface declarations. Which is complemented with hand written extra properties added and used by client side JavaScript data view model. Also one can’t really safely cast from a class to another (as nothing really happen) whereas interface casting is more appropriate.

About KnockoutJS

KnockoutJS is a library to do MVVM in JavaScript. For that it has a templating engine and introduce observable JavaScript object. There is great documentation and live example on the web site.

To setup KO write a few data template and tag with data binding and then setup a model on the whole page with

ko.applyBinding(model) // whole page
ko.appplyBinding(model, domElement) // part of the page

Remark The model could be any object. But if you want 2 way binding (i.e., UI automatically updating from data changes) you need to use observable.

Let’s say you want to make a file system tree view with the following data model:

interface IDirectory {
    subdirectories: Array<IDirectory>;
    files: Array<string>;
    name: string;
}

To have 2 way binding you need to use observable such as this one:

interface IKODirectory {
    subdirectories: KnockoutObservableArray>IKODirectory<;
    files: KnockoutObservableArray>string<;
    name: KnockoutObservable>string<;
}

You can then display particular property (with 2 way binding) with some knockout specific binding in the the data-bind tag as in:

<span data-bind="text: name"></span>
First Child <span data-bind="text: subdirectories()[0].name"></span>

where the text property (in the data-bind tag), is a path to an observable.

Remark the data-bind tag is where all the knockout magic reside

You can turn any simple value into an observable with ko.observable(value), for array use ko.observableArray(array), for object use ko.mapping.fromJS(obj) (it’s a plugin, need to be downloaded separately), and it will recursively set every property as an observable. The get the value from an observable you just invoke it, like so: myObservable(), to set it: myObservable(newValue). To be notified of change you can subscribe, like so: myObservable.subscribe(function(newValue) {}).

Knockout Template

Where Knockout really shines (more than AngularJS that is) is in how easy it is to define and use reusable template. Here is a recursive template to display the directory object defined just before.

<script id="tplDirectory" type="text/html">
    <span data-bind="text: name"></span>
    <div style="margin-left:2em;" data-bind="template: {
                                            name: 'tplDirectory',
                                            foreach: subdirectories }"></div>
</script>
<div data-bind="template: { name: 'tplDirectory', data: root }"></div> 

Template are defined in script tag. Referenced by their ID property. When one want to use the template pass the name of the template and data. And that’s all there is to it. The above sample is fully functional!

About the Application

There are two views: Manage.cshtml (define the questionnaires), Answer.cshtml (answer them), Query.cshtml (search the answers). The MVC controller methods are empty just returning the views, this being a client side app. Each page share the common Questionnaire.js (generated from Questionnaire.ts). And a page specific JavaScript file.

I defined two sets of KnockoutJS templates. One set is for editing objects, as they are pretty much all the same and numerous. The other set is for viewing and they are used on every screen. Because the templates are shared by all views I wrote them in a partial view (PartialTemplates.cshtml) and the data model for them should be an APPModel class (define in the common Questionnaire.ts).

Editing Questionnaire

My T4 template generates IQuestionaire, IKOQuestionaire, IQGroup, IKOQGroup, IQuestion, IKOQuestion interfaces, which I extend in my hand written view model code (with extra info needed for the UI to work) as follow:

interface IAppItemData {
    app_type: QOType;
    app_selected: KnockoutObservable<boolean>;
    app_editing: KnockoutObservable<boolean>;
}
module WebQuestionaire {
    interface IKOQuestionaire extends IAppItemData {
        app_answerSet: KnockoutObservable<IKOQAnswerSet>;
        app_groups: KnockoutObservableArray<IKOQGroup>;
    }
    interface IKOQGroup extends IAppItemData {
        app_questions: KnockoutObservableArray<IKOQuestion>;
    }
    interface IKOQuestion extends IAppItemData {
        app_options: KnockoutObservableArray<IKOQOption>;
        app_answer: KnockoutObservable<IKOQAnswer>;
    }
} 

I use ko.mapping.fromJS() to turn my data exchange object into the KO friendly interface.

var deo: IQuestionaire /* = something */
var qs = <IKOQuestionaire> ko.mapping.fromJS(deo); 

I also make them inherit from a common interface and add some extra UI properties. Including a app_type property so my model’s methods just take an IAppItemData and use the app_type property to find what kind of item it is. All my extra method have an obvious prefix to avoid colliding with EF generate data classes.

Then my questionnaire view model looks like that, with 3 identical properties (all APPItem<T>) for each column

class APPModel {
    questionaires = new APPItem<WQ.IKOQuestionaire>({
        /* init data */
    });
    groups = new APPItem<WQ.IKOQGroup>({
        /* init data */
    });
    questions = new APPItem<WQ.IKOQuestion>({
        /* init data */
    });
    model: WQ.IQuestionaireConfig;
    constructor(model?: WQ.IQuestionaireConfig) {
        if (model)
            this.load(model);
    }
    load(model: WQ.IQuestionaireConfig) {
        /** set up data, extends exchange data **/
    }
} 

All my server proxy methods return a jQuery deferred which I can act upon its return with the .then() method. I then set the Knockout model for the page by invoking my server proxy and binding the result.

declare var pserver: WQ.QuestionaireApiProxy;
$(document).ready(function () {
    pserver.GetQConfig(null).then(
        function (data: WQ.IQuestionaireConfig) {
            var model = new APPModel(data);
            ko.applyBindings(model);
        }
        , onGenericAjaxFail);
}); 

Remark all my editing method are in the root APPModel object. To access them in all the template I use the KO property “$root” which is the model set by the user on that location (as opposed to the current model “$data”, in case of recursive template use).

And display the editing column using bootstrap grid and knockout template.

<div class="row rpadded">
    <div class="col-md-4 qsection" data-bind="template: { name: 'template-column', data: questionaires }"></div>
    <div class="col-md-4 qsection" data-bind="template: { name: 'template-column', data: groups }"></div>
    <div class="col-md-4 qsection" data-bind="template: { name: 'template-column', data: questions }"></div>
</div>

At the top there are three columns set up with bootstrap grid layout (class: “row”, “col-md-4”) each with an identical knockout template (data-bind: template: name) of my column data (“questionnaires”, “groups”, “questions”)

Finally the UI looks like that, reusable template marked in red:

The template for editing the items might depends on the item, so instead of being a string it’s a function (returning a string) in APPModel (which I access with “$root”).

Every button call an action on my model, which update the data model which automatically update the UI.

For example here is how “+” button is handled:

<button type="button" class="btn btn-default navbar-btn" data-bind="click: function () { $root.addItem(id); }">
    <span class="glyphicon glyphicon-plus"></span>
</button>

in data-bind I use the click binding to call a method on my control which just create a new element. All method editing the object call the server and only do their stuff if the server method is successful, hence making sure the database is always update.

addItem(id: QOType) {
    var self = this;
    var name: string;
    switch (id) {
        case this.questionaires.id:
            name = "New Questionnaire";
            pserver.CreateQuestionaire(name).then(nid => {
                var Q = new WQ.Questionaire();
                Q.ID = nid;
                Q.Name = name;
                Q.Label = name;
                self.questionaires.items.push(self.extendItemData(ko.mapping.fromJS(Q), QOType.Questionnaire));
            }, onGenericAjaxFail);
            break;
        /* other cases */
    };
} 

Finally the server method is vanilla Entity Framework code:

[HttpPost]
public int CreateQuestionaire(string nameAndLabel)
{
    using (var ctxt = QuestionaireEntities.Create())
    {
        var q = new DD.Questionaire
        {
            Name = nameAndLabel,
            Label = nameAndLabel,
        };
        ctxt.Questionaires.Add(q);
        ctxt.SaveChanges();
        return q.ID;
    }
}

Of interest in the GetQConfig() method (which returns either all questionnaire data, or only one for a particular questionnaire) which use .Future() to turn multiple EF DB query into a single database call! Behold, there is only one database call done when the method below execute:

public QuestionaireConfig GetQConfig(int? id)
{
    using (var ctxt = QuestionaireEntities.Create())
    {
        // use .Future() for performance // to have only 1 SQL query
        var questionaires = ctxt.Questionaires.Where(x => id == null || x.ID == id).Future();
        var qgroups = ctxt.QuestionaireGroups.Where(x => id == null || 
            x.Questionaire.ID == id).Future();
        var groups = ctxt.QGroups.Where(x => id == null || 
            x.QuestionaireGroups.Any(qg => qg.Questionaire.ID == id)).Future();
        var gquestions = ctxt.QGroupQuestions.Where(x => id == null || 
            x.QGroup.QuestionaireGroups.Any(qg => qg.Questionaire.ID == id)).Future();
        var questions = ctxt.Questions.Where(x => id == null || x.QGroupQuestions.Any(
            gq => gq.QGroup.QuestionaireGroups.Any(qg => qg.Questionaire.ID == id))).Future();
        var options = ctxt.QOptions.Where(x => id == null || x.Question.QGroupQuestions.Any(
            gq => gq.QGroup.QuestionaireGroups.Any(qg => qg.Questionaire.ID == id))).Future();
        var result = new QuestionaireConfig()
        {
            questionaires = questionaires.ToList(),
            qgroups = qgroups.ToList(),
            groups = groups.ToList(),
            gquestions = gquestions.ToList(),
            questions = questions.ToList(),
            options = options.ToList(),
        };
        RemoveNonJSON(result);
        return result;
    }
}

Viewing Questionnaire

There is also some templates used on every page to view or answer a particular questionnaire. In the managing screen the selected Questionnaire is automatically previewed lived below, and update live as its configuration changes.

 

As shown above when of the extra property I add to question is the app_answer property, this way I can just get the answer from the questions.

Answering Questionnaire

The model inherits from APPModel, adds five short methods (4 for the button and one to load the selected questionnaire on demand).

The UI and code are really simple:

<p>
Select a Questionnaire <select style="width:200px;" 
                               data-bind="options: qlist,
                                        value: selectedQID,
                                        optionsText: 'Name',
                                        optionsValue: 'ID',
                                        chosen: {}
                                        "></select>
</p>
<div class="btn-group">
    <button type="button" class="btn btn-default" data-bind="click: $root.resetAnswers">Reset Answers</button>
    <button type="button" class="btn btn-default" data-bind="click: $root.loadLastAnswers">Load Last Answers</button>
    <button type="button" class="btn btn-default" data-bind="click: $root.copyLastAnswers">Copy Last Answers</button>
</div>
<p> </p>
<div class="panel panel-default" data-bind="if: questionaires.selected">
    <div class="panel-body">
        User Name: <input class="form-control" data-bind="value: questionaires.selected().app_answerSet().UserName" />
    </div>
    <!-- ko template: { name: 'template-view-questionaire', data: questionaires.selected } --> <!-- /ko -->
</div>

Of interest the comment below is not a comment, but a Knockout template without container DOM element.

Searching Answers

Remark only LIKE and == operators on text field are implemented in this sample.

I was looking for something which can represent a relatively flexible query with multiple AND / OR criteria. Unfortunately it appeared to me that letting the user choose arbitrarily nested query of arbitrary depth will lead to slow recursive SQL (with cursor). Instead I opted for 2 nested query level block, with OR at the top level and AND in sub block, or vice versa.

Coding the answer was relatively trivial. Of interest, I used datatable to render the result in a grid and a bootstrap modal to show individual results.

Also I used individual Knockout model for the popup and the rest of the page:

// finally updating UI
ko.applyBindings(qmodel, $("#query")[0]);
ko.applyBindings(popup_model, $("#result_popup")[0]);

What’s more interesting is the SQL implementation of the search. Due to the complexity of the search I decided to write as a SQL stored procedure (or sproc), instead of a C# query with EntityFramework. I pass a list of criteria block to SQL as a user defined custom table type.

Here is the definition of the table typed passed to a search SQL:

CREATE TYPE [question].[Criteria] AS TABLE(
    qgroupID int NOT NULL,
    questionID int NULL, -- question ID or null for ctype 0,1
    ctype int NOT NULL, -- 0:AS:UserName, 1:AS:LastModified, 2:A:Abool, 3:Atext, 4:Anumeric, 5:Adate, 6:Alist
    cop int NOT NULL, -- 0:LIKE, 1:IN, 2:==, 3:!=, 4:<, 5<=, 6:>, 7:>=
    valueText nvarchar(max) NULL,
    valueBit bit NULL,
    valueNum numeric(18, 0) NULL,
    valueDate datetime2(7) NULL
)
GO

The groupID is an arbitrary query block number which is only used to group the the criteria result together by query block.

The answer to a questionnaire is stored across multiple row as shown on this database diagram:

In my sproc I will have to match each answer with its question and criteria and check whether there is a match or not, represented as 0 for fail or 1 for success. Then I have to aggregate all the results applying the AND/OR logic displayed in the UI.

Below is the search stored procedure. For clarity sake I replaced the calculation which match a single criteria with ‘1’ so as to highlight how I declare and use the custom table type (at the top) and how I aggregate the answer. The individual row match are in a common table expression and the select below aggregates them and return matching answer sets IDs.

CREATE PROCEDURE [question].[Search] 
    @isOrAnd bit = 1 -- 0: AND/OR, 1: OR/AND
    , @criterias question.Criteria READONLY
AS
BEGIN
    -- TODO: implement all the ctype/cop combination
    -- this CTE check individidual criteria against individual answers
    ;WITH Criterium AS (
        SELECT [AS].ID, Q.ID AS IDQ, Q.Name AS QName, [AS].LastModified, [AS].UserName, C.qgroupID IDG, (
            -- ==========================================================================
            1 -- match 1 criteria against 1 answer to one question here and return 1 or 0
            -- ==========================================================================
        ) AS Success
        FROM question.QAnswer A
        INNER JOIN question.QAnswerSets [AS] on A.SetID = [AS].ID
        INNER JOIN question.Questionaires Q ON Q.ID = [AS].QuestionaireID
        INNER JOIN @criterias C on A.QuestionID = C.questionID
    )
    -- do grouping and calculate final YES/NO answer
    SELECT ID, IDQ, QName, LastModified, UserName 
    FROM (
        -- calculate success for all question group
        SELECT ID, IDQ, QName, LastModified, UserName
            , (CASE COUNT(CASE isAND WHEN 1 THEN 1 ELSE NULL END) WHEN 0 
                THEN 0 ELSE 1 END) isOrAnd -- 0 isAND means !isOrAnd
            , (CASE COUNT(CASE isOR WHEN 1 THEN NULL ELSE 1 END) WHEN 0 
                THEN 1 ELSE 0 END) isAndOr -- 0 !isOR means isAndOr
        FROM (
            -- caculate success of each question group
            SELECT ID, IDQ, QName, LastModified, UserName, IDG
                , (CASE COUNT(CASE Success WHEN 1 THEN 1 ELSE NULL END) WHEN 0 
                   THEN 0 ELSE 1 END) isOr -- 0 Success means !isOR
                , (CASE COUNT(CASE Success WHEN 1 THEN NULL ELSE 1 END) 
                   WHEN 0 THEN 1 ELSE 0 END) isAnd -- 0 !Success means isAND
            FROM Criterium
            GROUP BY ID, IDQ, QName, LastModified, UserName, IDG
        ) Results
        GROUP BY ID, IDQ, QName, LastModified, UserName
    ) RA
    WHERE 
        isOrAnd = (CASE @isOrAnd WHEN 1 THEN 1 ELSE NULL END)
        OR isAndOr = (CASE @isOrAnd WHEN 0 THEN 1 ELSE NULL END)
    ;
END

The individual criteria itself are just a gigantic nested (2 level) CASE statement looking at ctype (for field matched) and cop (for operator used).

Now this is well and good but calling this sproc was tricky too. EF doesn’t support custom table type. I had to revert to the lower level ADO.NET API. Custom table type are passed as DataTable. With the help of a few extension method calling this sproc and reading its result proved trivial:

[HttpPost]
public List<SearchResult> SearchAnswers(bool isOrAnd, [FromBody]List<SearchCriteria> criterium)
{
    using (var conn = new SqlConnection(
       ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    using (var cmd = (SqlCommand)conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "question.Search";
        cmd.Parameters.Add(new SqlParameter("@isOrAnd", isOrAnd ? 1 : 0));
        cmd.Parameters.Add(new SqlParameter("@criterias", criterium.ToDataTable()));
        var adap = new SqlDataAdapter(cmd);
        var ds = new DataSet();
        adap.Fill(ds);
        var table = ds.Tables[0];
        return table.ToList<SearchResult>();
    }
}

Last few words

Well I hope this rough explanation of my sample would have wet your appetite about Knockout, TypeScript, and WebAPI. Hopefully it will also help understood the source code better if you want to study the sample more in depth. Finally I hope my T4 template for strongly typed proxy generation will stir some interest too.