General Purpose Query Service

In some cases we have information stored in the system that is not exposed via the web service methods. When this happens you have to wait for a new release to get that information in a suitable way (i.e. a new web service method).

In rare occasions this is not fast enough, deadlines have to be met or some sort of edge case can be solved by gaining access to that extra piece of information

That is why we have developed a set of general purpose web service methods to query a table in the database. We call these new methods Lookup Services and each service method can query a given entity. An entity corresponds to a table in the database in most cases (currently in all cases actually).

The purpose, of these services, is to provide a simple way to get an entry from the database that you know something about. Typically this something would be a unique key (the primary key) or a key that several entities have in common.

Features and examples

The main function of a lookup service method is to provide a query that resembles a simple SQL select on a table with a where clause. Like so:

SELECT * FROM Customer WHERE CustomerNumber = 12345

In order to do the equivalent using a lookup service you would first need to know which entity you want, in this case the customer, and what you want to query for, in this case the CustomerNumber with a value of 12345. Now all there is left is to build a query object and call the CustomerLookup method. Like so:

var whereClause = new Customer();
whereClause.CustomerNumber = 12345;
var request = new CustomerLookupRequest();
request.Customer = whereClause;
var response = service.CustomerLookup(request);

As you can see the where clause is actually an instance of the object type we want to query for. This provides the parser with some useful information on what can be used in a query and what can’t.

Multiple Query Values

Lets say you know the phone number and the e-mail of a customer and you want to query for all customers with that combination of values. Simply add the phone number and the e-mail and you are good to go.

var whereClause = new Customer();
whereClause.EmailAddress = "testuser@disposethisfakeemail.com";
whereClause.Telephone1 = "+4712345678";
var request = new CustomerLookupRequest();
request.Customer = whereClause;
var response = service.CustomerLookup(request);

The resulting query will resemble that of an SQL with AND as the logical operator in the where clause

SELECT * FROM CUSTOMER WHERE Telephone1 = '+4712345678' AND EmailAddress = 'testuser@disposethisfakeemail.com'

Note
Note that it is not translated to that exact query, it is just used as a way to explain what happens and what you can expect.

Query Meta Information

So far we have only looked at the simple part, what to query for. In addition there is some extra metadata that can be specified on the query. Typically this is what you want to do with the result.

Order By

A common case is to order some data, lets say you want all InvoiceTransactions for a given Invoice. There might be hundreds, so you want to order your result by the amount to get a better overview. The code becomes something like the following

var whereClause = new InvoiceTransaction();
whereClause.Invoice_Id = 12345;
var metadata = new QueryMetadata();
metadata.OrderBy = "Amount";
var request = new InvoiceTransactionLookupRequest();
request.InvoiceTransaction = whereClause;
request.QueryMetadata = metadata;
var response = service.InvoiceTransactionLookup(request);

Now the result is ordered by amount. If you wanted to order by amount but in descending order you could specify that as

metadata.OrderBy = "Amount desc";

It is even possible to order by multiple criteria:

metadata.OrderBy = "Amount desc, Currency_Id asc"

This would order by amount descending and after that by Currency_Id in ascending order.

Paging

Continuing on our example from before with InvoiceTransaction and possible hundreds of results (its a big invoice!). You might want to split the view up into separate pages, each showing a subset of the results. The paging feature roughly corresponds to the TOP and LIMIT clauses in various SQL dialects. Given an offset and a result count, only a subset of results will be returned.

So lets say you want to get the first ten InvoiceTransactions you would specify that in the metadata like so

var metadata = new QueryMetadata();
metadata.ResultOffset = 0;
metadata.ResultCount = 10;

Now if you want the next 10 results you would just specify a larger offset like so

var metadata = new QueryMetadata();
metadata.ResultOffset = 11;
metadata.ResultCount = 10;

Note that the result count does not change, it is NOT the result row count but the max number of objects to retrieve.

Important
Note on performance: If for some reason you KNOW that you are going to request all results, then don’t bother with the paging, it will just slow you down.

Include Related Information

Often you want information from multiple entities at the same time. For instance lets pretend you would very much like to get the Invoice related to a given transaction (often it is the other way around). This can be done by first getting the InvoiceTransaction and then looking at the Invoice_Id and then querying for the type. If you had direct SQL/DB access you would just have done it using a JOIN right? Doing something similar is possible with the lookup services.

var whereClause = new InvoiceTransaction();
whereClause.Invoice_Id = 12345;
var metadata = new QueryMetadata();

// Includes allows for getting extra information on an entity
metadata.Includes = new List();
metadata.Includes.Add("Invoice");

var request = new InvoiceTransactionLookupRequest();
request.InvoiceTransaction = whereClause;
request.QueryMetadata = metadata;
var response = service.InvoiceTransactionLookup(request);

Now that would give you the Invoice of the transaction is one was found, and nothing if nothing was found (i.e. Invoice_Id is null).

You can add multiple include paths, so if you wanted the tax as well you could do the following:

metadata.Includes.Add("Invoice");
metadata.Includes.Add("Tax");

You would get both Tax and Invoice, much like a SQL JOIN (again not quite the thing that actually happens).

It is even possible to get nested information by extending the include string. As an example the Invoice just retrieved have an address associated with it. It would be nice to get that address while we are at it. The code would look like:

metadata.Includes.Add("Invoice.Address");
metadata.Includes.Add("Tax");

Now you would get Invoice, Address and Tax in one single query.

Note
Note The more data you include the longer the query will take, so only include data that you need.

Even though the service has concepts that are reminiscent of ordinary SQL and we use SQL to compare and explain features, the services does not expose the power of SQL (no compare operators, no wildcards, likes, group bys, union or similar), nor are they going to. Likewise it is a general purpose system so there is very little we can (or will) do to change the outcome for a given query if it is not performing to your expectations.

The include system can fail in unexpected ways, so even though you know there is a path from a Customer to another Customer via a multiple addresses, households, municipalities etc, there is no guarantee that the parsing engine knows how to get there, or it might be that it is hard to structure the query correctly. There is little to nothing we can do about it, we cannot tweak a specific entity include path, it is a general purpose system and sometimes it will fail. It is also possible to specify circular includes, this will also cause failures – so make experiments, but don’t expect magic.

While ordering results there are also some limits, in general you can sort on anything within the main result, but not on anything included. So if for instance you query for Customers and want to include addresses but sort by the Zip code of those addresses, well you are out of luck – this must be done on the client.

There is currently no way to lookup/search using complex types, so even though we use the data contract entity as a way to structure the “where” part of the query, we only consider the first level of data for query. So it is not possible to do queries of type where CustomerNumber = 12345 and Customer.CustomerType.Code == GOVERNMENT, but you could do a query on the CustomerType first and get the Id and then specify a new query of the type where CustomerNumber= 12345 and CustomerType_Id = 1 (ending up with two queries instead of one).

So how does this perform? We have taken some metrics on an internal test system. Now the actual numbers you get depends on a lot of things, the speed of the database and its I/O capabilities in particular. But consider these numbers as a reference on some average-to-old hardware.

The actual numbers you get might vary greatly, but you should see better numbers than many of the regular services as they query multiple tables and aggregate data differently (this is of course an accepted overhead if you need all that data in its aggregated form).

The Lookup Service is a generic service layer that sits on top of our database/schema (via an abstraction layer), and for that reason it will change with the database, so between versions you have to expect changes in the WSDL, breaking and otherwise. We rarely change the schema in any significant way, but when we do, it is up to you to change your client so that the result suits your needs once again.

Due to the fact that this service is not managed directly, we advise against relying permanently on the lookup service and instead work with Infosoft so we can provide you with a maintenable service that fulfills your needs.