Queries from the Nathean Analytics cache are based upon the LINQ query execution language.
A sample syntax to a basic Nathean Analytics Cache Query is as below:
(MyObject) .Select(@Name as NameColumn) .Where(@Financial_Month__c == January 2011)
The query consists of several sections:
Object Name The first line will always be the name of the Dataset that resides in the Nathean Analytics Cache that the query will be executed against; in the above case the Dataset is named MyObject
- Object names must be enclosed in brackets
- The first line of a query should be the base object to query from.
Select The select section of the query should always be of the format: .Select(list of fields to select)
The rules governing field selection are as follows:
- Any field that is selected from the base object is preceded with the @ symbol to indicate to the compiler that the field is to be chosen from the cached object
- Each field selected must contain an ‘as’ which is followed by the output name of the field (e.g. as NameColumn)
Where The where section of the query is used to filter the result set. The where section should be in the format:
.Where(filter condition to execute)
The rules governing data filtering are as follows:
- Any field that is used from the base object must be preceeded by the @ symbol * Multiple filter conditions may be combined (e.g. @Name “Joe Bloggs” && @Age >= 18)
- The combining operators are: ‘&&’ for And and ‘||’ for Or.
- The following operators are supported:
== equals != does not equal > greater than < less than >= greater than or equal to <= less than or equal to
The where clause can contain operations on a column such as “Contains”, “Substring”, etc. If these operations are to be used in a where clause the field names must be enclosed in brackets e.g.:
The brackets are required to separate the function (e.g. .Contains) from the field as field names can also contain dots. (e.g. @MyCol.Contains is a valid field name).
Nathean Analytics provides the ability to execute non Linq statements in a Linq query by offering Linq extension methods. One such method of execution is to use the Prepare statement; the Prepare statement will execute an extension method prior to the execution of the remainder of the Linq query. The resultant Dataset will then be used as the basis of the query.
(UserRole) .Prepare(Recurse(UserRole.ParentRoleId, UserRole.Id)) .Select(@Username as Username, @UserRole.Id as RoleID)
In the above sample the Linq query is to be executed against a Dataset named UserRole. The second line of the query uses the prepare extension method, in this case to call a method named Recurse. Two parameters values are passed to the extension method (UserRole.ParentRoleId, UserRole.Id).
All parameters to extension methods are passed as strings.
User Defined Extensions
Nathean Analytics provides the ability for 3rd parties to develop extension methods and use them in Nathean Analytics .
(TestObject) .Prepare(Nathean.3rdParty.Financial.FinancialMonth(Financial_Month__c , LastModifiedDate))
In the above example the Prepare statement invokes an extension method developed in an external DLL to the Nathean Analytics deployment. The rules governing 3rd party extension methods are:
- Must be developed in a Microsoft .Net supported language.
- The first parameter to the object must be of type DataTable, a reference to the object being queried will be passed. There is no need to specify this parameter in the list of method parameters
- The extension method must return an output DataTable object which is the result of the manipulation performed on the input DataTable.
- A fully qualified namespace must be used in the Prepare statement to identify the object. (e.g. Nathean.3rdParty.Financial.FinancialMonth invokes a method named FincancialMonth in a class named Financial in the namespace Nathean.3rdParty).
- Extension methods must be placed in a DLL named Extension.dll which should be located in the Bin folder of the Nathean Analytics and Nathean Schedule Service deployments.
In the above example a prepare clause of:
.Prepare(Nathean.3rdParty.Financial.CalcFinancialMonth(Financial_Mont h__c, LastModifiedDate))
Would relate to a method signature in the C# programming language of:
public DataTable CalcFinancialMonth(DataTable source, string columnToUpdate, string dateColumn)