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.