Kentico DataQuery API – Sub-selects

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.

Auteur: Marnix van Valen

Lead developer en architect

Eén gedachte over “Kentico DataQuery API – Sub-selects”

Reacties zijn gesloten.