Testing Table Valued Functions with LINQPad

LINQPad is a lightweight software development tool for .net developers that can be installed easily within a minute. LINQPad users may find it practical to test table-valued functions of finaquant’s .net libraries Finaquant Protos or Calcs directly in LINQPad, especially for running queries on input and output tables. This is quite easy as the following steps will illustrate.

How to run demo queries with table functions in LINQPad

1) Download and install LINQPad
How LINQPad is defined by its developer: Querying is merely a special case of using LINQPad. More generally, LINQPad is a C#/VB/F# scratchpad that instantly executes any expression, statement block or program with rich output formatting and a wealth of features that “just work”.

2) Download and install one of the .net libraries with table-valued functions, Finaquant Protos (free) or Finaquant Calcs (commercial).
All the demo queries (i.e scripts) in LINQPad can be run with both libraries, excluding the Commission Calculation example which requires Finaquant Calcs.

These libraries include high-level methods (of class MatrixTable) with table-valued parameters, as often required in analytics or business intelligence.

Finaquant Calcs is dispatched with a 30-day trial version. Finaquant Protos is free, and requires no license updates for permanent non-commercial use.

Maximum table size is limited with Finaquant Protos to about 20’000 rows. You will need a permanent license for Finaquant Calcs for unlimited table size. Finaquant Calcs has also some additional calculation engine features like Nodes & Networks to build modular and extendable structures for complex rule-based computations, workflow integration, configurable input validations, stepwise calculations and so on.

3) Download and unzip LINQPad demo queries (scripts) in C# and VB.NET
You will find two folders: One folder is for examples in C#, other folder is for examples in VB.NET (Visual Basic for .NET).

4) Open LINQPad, set folder to one of the two folders (csharp or vb.net) you have unzipped in the previous step.

5) Copy the content of query file Extensions to the global My Extensions.
These are custom extension methods that will be available to all queries.

6) Create a new SQLEXPRESS database named finaquant
finaquant is the name of the database where all table-valued parameters (i.e. input & output tables) will be stored.

1. Add connection > Next > Select “Specify new or existing database”
2. Enter “finaquant” as database name, and press “Create database”
3. Uncheck all Data Context Options; these options are not required. “Pluralize EntitySet and Table properties” adds an “s” to the end of table names; an undesirable feature for our examples.

6) Add assembly (dll) and namespace references to LINQPad queries for Finaquant Protos or Calcs
LINQPad menu > Query > Query Properties > Add
Browse and add required assembly references for Protos or Calcs. Normally, you will find the dll files under a folder like:
C:\Program Files (x86)\Finaquant Analytics\finaquant calcs\dll

Don’t forget to enter the namespace, “FinaquantCalcs” or “FinaquantProtos” and press OK. You may also press the button “Set as default for new queries” if you want to create new queries with finaquant’s table functions.

Now you can run demo queries in LINQPad with table-valued functions

Just select the query you want in “My Queries” window, and press the arrow button (or F5).

First run the second query (Create Demo Tables) to create all the input tables that will be required for all the other examples.

The examples from 3 (view tables) to 8 (distribution) are included for getting started with table functions. The examples from 9 and above demonstrate the real power of high-level table functions: Simplicity of implementing seemingly complex computations!

You may need to get used to the idea that data tables are just parameters for finaquant’s table functions, like matrices for matrix functions in linear algebra. In that sense, finaquant’s .net libraries offer methods for table algebra. When you store and read parameters in/from a database, you are in fact storing and reading table-valued parameters.

You can read, write and delete table-valued parameters with the following methods defined in the global query My Extensions:

public static MatrixTable ReadTable(string TableName, DataStore dstore, MetaData md)
public static void WriteTable(MatrixTable tbl, string TableName, DataStore dstore)
public static void DeleteTable(string TableName, DataStore dstore)

This is the general flow of a calculation with table-valued parameters:

1. You read first some input tables from the database into in-memory tables of type MatrixTable (finaquant’s class for representing data tables).
2. You make some calculations with these in-memory tables using the table functions (without any connection to the database) to generate some resultant (output) tables.
3. If you want, you can store table-valued output parameters in the database, either for applying some queries on them (LINQPAd is an ideal tool for this purpose), or for using these tables as input parameters to subsequent calculation stages.

Any questions or comments? Please direct them to finaquant’s community forum.

Written by: Tunç Ali Kütükçüoglu

Copyright secured by Digiprove © 2014 Tunc Ali Kütükcüoglu