Putting Queries to Use in Business Central

We’ve had query objects for a while now – since NAV 2013 (I think). In theory they sound great, link a bunch of tables together, aggregate some columns, get distinct values, left join, right join, cross join – executed as a single SQL query.

Why Don’t We Use Queries Much?

In practice I haven’t seen them used that much. There’s probably a variety of reasons for that:

• We have limited control over the design and execution of the query at runtime. The design is pretty static making it difficult to create useful generic queries short of throwing all the fields from the relevant tables in – which feels heavy-handed
• I find how the links between dataitems unintuitive
• It isn’t easy to visualise the dataset that you are creating when writing the AL file
• Habit – we all learnt FindFirst/Set, Repeat and Until when we first started playing with CAL development and are more comfortable working with loops than datasets. Let’s be honest, the RDLC report writing experience hasn’t done much to convert us to a set-based approach to our data

However, just because there is room for improvement doesn’t mean that we can’t find good uses for queries now. Queries are perfect for:

• Using queries to select DISTINCT values in the dataset
• Aggregates – min, max, sum, average, count – especially for scenarios that aren’t suited to flowfields
• Date functions – convert date columns to day/month/year – which allows you to easily aggregate another column by different periods
• Outer joins – it’s possible, but far more expensive, to create this kind of dataset by hand with temporary tables
• Selecting the top X rows
• Exposing as OData web services

It’s the last point in that list that I particularly want to talk about. We’ve been working on a solution lately where Business Central consumes its own OData web services.

What?! What kind of witchcraft is this? Why would you consume a query via a web service when you can call it directly with a query object? Hear me out…

Consuming Queries

I think you’ve got two main options for consuming queries via AL code.

Query Variable

You can define a variable of type query and specify the query that you want to run. This gives you some control over the query before you execute it – you can set filters on the query columns and set the top number of rows. Call Query.Open and Query.Read to execute the query and step through the result set.

The main downside is that you have to specify the query that you want to use at design-time. That might be fine for some specific requirement but is a problem if you are trying to create something generic.

Query Keyword

Alternatively we can use the Query keyword and execute a query by its ID. Choose whether you want the results in CSV (presumably this is popular among the same crowd that are responsible for an increase in cassette tape sales) or XML and save them either to disk or to a stream.

The benefit is that you can decide on the query that you want to call at runtime. Lovely. Unfortunately you have to sacrifice even the limited control that a query variable gave you in order to do so.

OData Queries/Pages

Accessing the query via OData moves us towards having the best of both worlds. Obviously there is significant extra overhead in this approach:

• Adding the query to the web service table and publishing
• Acquiring the correct URL to a service tier that is serving OData requests for your query
• Creating the HTTP request with appropriate authentication
• Parsing the JSON response to get at the data that you want

This is significantly more work than the other approaches – let’s not pretend otherwise. However, it does give you all the power of OData query parameters to control the query. While I’ve been talking about queries up til now almost all of this applies to pages exposed as OData services as well.

• $filter: specify column name, operator and filter value that you want to apply to the query, join multiple filters together with and/or operators
• $select: a comma-separated list of columns to return i.e. only return the columns that you are actually interested in
• $orderBy: specify a column to order the results by – use in combination with $top to get the min/max value of a column in the dataset
• $top: the number of rows to return
• $skip: skip this many rows in the dataset before returning – useful if the dataset is too large for the service tier to return in a single call
• $count: just return the count of the rows in the dataset – if you only want the count there is no need to parse the JSON response and count them yourself