Kentico DataQuery API – Sub-selects

A little toy bus on the pavement

Kentico DataQuery API – Sub-selects

Posted 26 Sep 2016

In lots of projects a requirement pops up to count the number of items in a sub set; the number of products in a category for example.

In SQL you’d probably solve this with a sub select or a Common Table Expression (CTE) for more complex scenario’s.

With the Kentico Data API you can do subselects as well.
It’s not well documented so it took me a while to figure it out but here’s how it works:

CategoryInfoProvider.GetChildCategories(currentCategory.CategoryID, siteId: siteId)
 .Columns("CategoryDisplayName", "CategoryID")
 // "(SELECT COUNT(*) FROM CMS_Category c2 WHERE c2.CategoryParentID=CategoryID)"
 .AddColumn(
    new DataQuery().Source(s => s.SourceExpression = "CMS_Category c2")
    .Columns("Count(*)")
    .Where("CMS_Category.CategoryID = c2.CategoryParentID")
    .AsColumn("ChildCount"))
 .Result;

This particular query will get a DataSet with the category names, ids and the number of child categories. Unfortunately, you do need to use a DataSet here because using a typed result set will not give access to the ChildCount column.

Developer Notes