Kentico9 Query API examples

Kentico9-QueryAPI-Examples

/* 
This is a collection of Kentico 9 Query API snippets which can be helpful when learning.

These resources are also helpful when getting started:
https://docs.kentico.com/display/K9/Retrieving+database+data+using+ObjectQuery+API
https://docs.kentico.com/display/K9/Working+with+pages+in+the+API
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

I hope you find these useful! Please feel free to fork and contribute your own examples,
or let me know if you spot any issues.

Carly Lyddiard
@eee8aa
http://carly.io

*/

Aggregates.txt

/*
 Examples of some aggregates, 
 including GroupBy and Having clauses.
*/

CMS.Ecommerce.SKUInfoProvider
    .GetSKUs()
    .Columns("SKUTrackInventory")   // start with just this field
    .AddColumn(                     // now we add columns for each aggregate
        new CountColumn("SKUTrackInventory").As("ItemCount")
    )
    .AddColumn(
        new AggregatedColumn(AggregationType.Average,"SKUAvailableItems")
        .As("AvgAvailableItems")
    )
    .AddColumn(
        new AggregatedColumn(AggregationType.Sum, "SKUID")
        .As("SumSKUId")
    )
    .AddColumn(
        new AggregatedColumn(AggregationType.Max, "SKULastModified")
        .As("MostRecentMod")
    )
    .GroupBy("SKUTrackInventory")
    .Having(
            new WhereCondition()
            .WhereGreaterThan(new CountColumn("SKUTrackInventory"), 2)
    )
    .Result ;

FirstObject_TOPN.txt

/*
The correct way to retrieve the first object from a Kentico Data Query.
The TopN ensures the SQL statement onky fetches the Nth first rows - 
in this case, the first row only. The FirstObject converts our result
to a single object. LINQ's FirstOrDefault() can be used in place of
FirstObject without issue, as long as TopN(1) is in place.
*/

CMS.CustomTables.CustomTableItemProvider
  .GetItems<CMS.CustomTables.Types.SampleTableItem>()
  .TopN(1)
  .FirstObject;

Join_Document_And_Table.txt

/*
An example of one way to join a document query with a table query.

This example uses a DocumentQuery (so coupled data is fetched).
It uses TreeProvider, so only latest published versions are selected.

I've used AsNested when selecting DocumentQuery to join with, 
because DocumentQuery will already contain joins, and
joining against those without an anonymous inner view confuses
the API. 
If you don't do AsNested, you'll find Kentico try to join
to COM_SKU S.NodeId, which isn't a field. It will error.
(Unless you know a better way?)

*/

var tree = new CMS.DocumentEngine.TreeProvider();

var treeQuery = tree
    .SelectNodes("CMS.MenuItem") // Class name provided -> this is a Document query (not MultiDocument query)
    .OnCurrentSite()
    .Path("/Store", PathTypeEnum.Children)
    .NestingLevel(1) // relative to path, I believe
    .Columns("NodeId, NodeAliasPath, DocumentName")

    .AsNested()
    ;

var data = treeQuery
    .Source(tq=> 
            tq.LeftJoin<CMS.CustomTables.Types.SampleTableItem>(
                "NodeId", // Left table field
                "ItemId", // Right table field
                (new WhereCondition()).WhereGreaterThan("ItemId",4)
            )
     )
    .Columns("NodeId, NodeAliasPath, DocumentName, ItemId, ItemText")
    .OrderBy("NodeAliasPath")
    .Result; // Get the DataSet of results.

Joins.txt

/*
Demonstrating one technique for a left outer join using Kentico's Data API.
In this case, we're joining our custom table against the SKU table.

We're also demonstrating filters before the join (see where isProductSKUType
is hooked in) and after the join (WhereLessThan).

This query was created with a starting type of SampletableItem, so the strongly
typed result of this query will be SampleTableItems. With joins especially, we 
should use DataSets so we can easily get to all the fields in our joined table.
SampleTableItem would make no sense here. We retrieve DataSet from any query
by calling .Result (as opposed to .TypedResult which returns strongly typed result).

Kentico point out that for high performance, working with DataSets can avoid
the overhead of rehydrating strongly typed objects.
*/

// A prepared WhereCondition, for readability (and perhaps reuse?)
var isProductSKUType = (new WhereCondition())
                         .WhereEquals("SKUProductType", "PRODUCT");

var data = CMS.CustomTables.CustomTableItemProvider
    .GetItems<CMS.CustomTables.Types.SampleTableItem>() // starting table

    .Source(sourceItem =>
            sourceItem.Join<SKUInfo>(   // Kentico knows the SQL table via the class! Cool.
                "ItemId", // (left table)
                "SKUID",  // (right table)
                CMS.DataEngine.JoinTypeEnum.LeftOuter,  // optional - defaults to inner join
                isProductSKUType //optional WhereCondition 
            )
            // more joins here if more than 2 table joins are needed.
        )

    .WhereLessThan("ItemID", 10) // Filter by ACTUAL column names, not aliases.

    .Columns("ItemId as ID", "ItemText", "SKUName") // Check out Column aliases!

    .OrderByDescending("ItemId")

    .Result; // Get a DataSet, otherwise we'd only get our starting Type (SampleTableItem)

Paging.txt

/*
Fetching only just one page of data can be quick and easy, and can
avoid fetching the full data set just to present a single page of it.
This technique fetches only the single requested page, and the total 
count of all rows is available if desired.

query.TotalRecords is the total count of records (not just this page)
without a second query! It's populated after the query is executed.
This property is only available when paging is used.
*/


// Variables for page number and size. In this case they come
// from the querystring parameters.
int pageNumber = CMS.Helpers.QueryHelper.GetInteger("page", 1);
int pageSize = CMS.Helpers.QueryHelper.GetInteger("pageSize", 3);

var query = CMS.CustomTables.CustomTableItemProvider
  .GetItems<CMS.CustomTables.Types.SampleTableItem>()
  .Page(pageNumber, pageSize);

var data = query
  .Select(x => new
  {
      ItemId = x.ItemID,
      ItemGUID = x.ItemGUID,
      ItemText = x.ItemText,
      TotalRecords = query.TotalRecords
  })
  .ToList();

Select_CustomTableItem_CustomTable.txt

/*
Selecting data from a CustomTableItem query
and matching on a custom string value field.
*/

CMS.CustomTables.CustomTableItemProvider
.GetItems("customtable.SampleTable")
.Select(x => new {
    ItemId = x.ItemID,
    ItemGUID = x.ItemGUID,
    ItemText = x.GetStringValue("ItemText","")
})
.ToList();

Select_StronglyTyped_CustomTable.txt

/*
Selecting SampleTableItem data.
This strongly typed query of SampleTableItems
returns SampleTableItem objects, providing 
field access via properties.

SampleTableItem's code must be saved from Kentico
and added to the project to allow this.
*/

CMS.CustomTables.CustomTableItemProvider
.GetItems<CMS.CustomTables.Types.SampleTableItem>()
.Select(x => new {
    ItemId = x.ItemID,
    ItemGUID = x.ItemGUID,
    ItemText = x.ItemText
})
.ToList();

Select_StronglyTypedPage_TreeProvider.txt

/*
Selecting strongly typed Article documents using the TreeProvider.
TreeProvider should be used to retrieve the latest published 
version of documents - it's not version history aware.

This executes a DocumentQuery of Articles, returning 
coupled data for the Article objects.

Article page type's code must be saved from Kentico
and added to the project to allow this.
*/

var tree = new CMS.DocumentEngine.TreeProvider();

var data = tree
  .SelectNodes<Article>()                   // Note that we are supplying class type. This is our generated page type.
  .OnCurrentSite()
  .Path("/Articles", PathTypeEnum.Children) // All pages UNDER the Articles node (equivalent to /Articles/%)
  .Select(d => new                          // D is an Article object
  {
      NodeId = d.NodeID,
      Name = d.DocumentName,
      Class = d.ClassName,
      AliasPath = d.NodeAliasPath,
      ArticleSummary = d.ArticleSummary     // This coupled data field will be populated because we identified our class in SelectNodes
  })
  .ToList();

Select_TreeNode_TreeProvider_ByClassName.txt

/*
Selecting documents using the TreeProvider.
TreeProvider should be used to retrieve the latest published 
version of documents - it's not version history aware.

This executes a DocumentQuery, returning TreeNode objects
but with coupled data fetched because we supplied our 
class name in SelectNodes. Coupled data is accessible by 
.GetValue methods and similar, or by array access.
*/
var tree = new CMS.DocumentEngine.TreeProvider();

var data = tree
  .SelectNodes("DancingGoat.Article")       // Note that we are supplying class name
  .OnCurrentSite()
  .Path("/Articles", PathTypeEnum.Children) // All pages UNDER the Articles node (equivalent to /Articles/%)
  .Select(d => new                          // D is a TreeNode
  {
      NodeId = d.NodeID,
      Name = d.DocumentName,
      Class = d.ClassName,
      AliasPath = d.NodeAliasPath,
      ArticleSummary = d["ArticleSummary"]  // This coupled data field will be populated because we identified our class by class name
  })
  .ToList();

Where_Equals.txt

/*
Selecting data by matching exactly on a field using WhereEquals.
In this case, we pass in a string "3" to match on, but we can
pass in other objects like integers, doubles, etc.
*/

CMS.CustomTables.CustomTableItemProvider
.GetItems<CMS.CustomTables.Types.SampleTableItem>()
.WhereEquals("ItemID","3")
.Select(x => new
{
    ItemId = x.ItemID,
    ItemGUID = x.ItemGUID,
    ItemText = x.ItemText
})
.ToList();

Where_In.txt

/*
There are two forms of WhereIn.
Form 1: using a list of values you already have.
Selecting data where the DB field contains a value in a List object
which you already have. This List can be only int, string or Guid - 
Kentico's WhereIn function does not support other lists.
*/

CMS.CustomTables.CustomTableItemProvider
.GetItems<CMS.CustomTables.Types.SampleTableItem>()
.WhereIn("ItemId", new List<int> { 1,3}) 
.Select(x => new
{
   ItemId = x.ItemID,
   ItemGUID = x.ItemGUID,
   ItemText = x.ItemText
})
.ToList();


/*
Form 2: using a subquery to select a list of values from the DB,
all executed as a single statement.
The subquery can be any type of data query, as long as 
it selects only a single column.
*/

CMS.CustomTables.CustomTableItemProvider
.GetItems<CMS.CustomTables.Types.SampleTableItem>()
.WhereIn("ItemId",
        CMS.Ecommerce.SKUInfoProvider
            .GetSKUs()
            .Column("SKUId") // MUST be a single column
 )
.Select(x => new
{
    ItemId = x.ItemID,
    ItemGUID = x.ItemGUID,
    ItemText = x.ItemText
})

WhereConditions.txt

/*
Chained Where methods are "AND"ed by default.

WhereConditions can be nested in complex ways, and you can control
nesting by creating new instances of WhereCondition in your chaining.

In this way you can achieve clauses like "((A or B) and (C or D)) or X" etc.

WhereConditions could also be prepared as variables and reused. It could be useful
to have defined conditions in one place so they can be changed easily if requirements
change.

*/


var whereItemIDEqualsThree = new WhereCondition()
                        .WhereEquals("ItemId",3);

var whereItemTextContainsTwo = new WhereCondition()
                        .WhereContains("ItemText","2"); // just like String.Contains or Like '%2%'

var whereCarlyLikesThisItem =
    new WhereCondition() // Creates brackets around its child conditions
      .Where(whereItemIDEqualsThree)
      .Or()
      .Where(whereItemTextContainsTwo)
      .Or()
      .WhereEqualsOrNull("ItemText","Sample Text 4");

CMS.CustomTables.CustomTableItemProvider
    .GetItems<CMS.CustomTables.Types.SampleTableItem>()

    .Where(whereCarlyLikesThisItem)
    .WhereGreaterThan("ItemId",1)       // Wheres are AND by default.
    .WhereNotNull("ItemGuid")

    .Select(x => new
    {
        ItemId = x.ItemID,
        ItemGUID = x.ItemGUID,
        ItemText = x.ItemText
    })
    .ToList();