Getting started with Kentico’s Data Query API can be a bit confusing, even if you’ve read the help and a few blog posts. The examples online are often quite basic, and when you need to implement more complex queries (or run into unexpected errors doing something you thought was simple), it can be time consuming and frustrating. I’m sharing a collection of Kentico Data Query API examples which could be useful for you.

Kentico’s online help is useful, but very basic:

• https://docs.kentico.com/display/K9/Retrieving+database+data+using+ObjectQuery+API
• https://docs.kentico.com/display/K9/Working+with+pages+in+the+API

And while these posts were to announce the new Data Query APIs in Kentico 8, they’re a great intro and are packed with useful info:

• http://devnet.kentico.com/articles/kentico-8-technology-dataquery-api
• http://devnet.kentico.com/articles/kentico-8-technology-dataquery-advanced-api
• http://devnet.kentico.com/articles/kentico-8-technology-documentquery-api

It’s worth noting that in these posts, Martin invites us to provide feedback so Kentico can expand on the DataQuery capabilities to meet our needs as developers. If you can’t do something with Kentico’s DataQuery API, ask Kentico for it.

Easy Traps to Fall Into

If you’re like me, when working with Kentico’s DataQueries for the first time you might do things like this:

CustomTableItemProvider.GetItems<SampleTableItem>().FirstOrDefault();
CustomTableItemProvider.GetItems<SampleTableItem>().Where(x=> x.GetStringValue("ItemText")=="Sample Text 4").ToList();

and because these return the data you expect, you don’t pay much more attention. If you checked the SQL Debugger, you’d realise both of these lines fetch all of the SampleTableItem rows from the database and filter them only after they’re in memory.

If you’re working with Kentico’s Data Queries, you’ll need to learn to use them properly in order to execute the SQL that you want. Embrace the DataQuery API. Embraaaceee iiiiitt.

More Complex Kentico Data Queries

I’ve created a set of code examples exploring more complex solutions like WhereIn statements, and complex joins, including joining between a table and a document. We’ll also look at why joining with a DocumentQuery is a bit different to joining with a MultidocumentQuery. If you’d like an example of anything in particular, please let me know and I’ll try to add it here when I have time. Also please feel free to make pull requests to contribute your own API examples.

These examples were created and successfully executed on a Kentico 9 Dancing Goat website, with code generated for the Article page type and the SampleTableItem custom table.

Full Github Gist can be found at https://gist.github.com/carly-io/0c425bbaf529c287c276a56280082138: