/xperience-query-extensions

This package provides helpful extension methods for the various Kentico Xperience Document/Object query APIs.

Primary LanguageC#MIT LicenseMIT

Xperience Query Extensions

GitHub Actions CI: Build

Publish Packages to NuGet

NuGet Package

This package provides a set of extension methods for Kentico Xperience 13.0 DocumentQuery, MultiDocumentQuery, ObjectQuery, and IPageRetriever data access APIs.

Dependencies

This package is compatible with ASP.NET Core 3.1 -> ASP.NET Core 5 applications or libraries integrated with Kentico Xperience 13.0.

How to Use?

  1. Install the NuGet package in your ASP.NET Core project (or class library)

    dotnet add package XperienceCommunity.QueryExtensions

  2. Add the correct using to have the extensions appear in intellisense

    using XperienceCommunity.QueryExtensions.Documents;

    using XperienceCommunity.QueryExtensions.Objects;

    using XperienceCommunity.QueryExtensions.Collections;

    The extension methods are all in explicit namespaces to prevent conflicts with extensions that Xperience might add in the future or extensions that the developer might have already created.

    If you are using C# 10, you can apply these globally with C# 10 implicit usings

Extension Methods

DocumentQuery

Prerequisites

using XperienceCommunity.QueryExtensions.Documents;

Examples

These work for both DocumentQuery<T> and MultiDocumentQuery

public void QueryDocument(Guid nodeGuid)
{
    var query = DocumentHelper.GetDocuments()
        .WhereNodeGUIDEquals(nodeGuid);
}
public void QueryDocument(int nodeID)
{
    var query = DocumentHelper.GetDocuments()
        .WhereNodeIDEquals(nodeID);
}
public void QueryDocument(int documentID)
{
    var query = DocumentHelper.GetDocuments()
        .WhereDocumentIDEquals(documentID);
}
var query = DocumentHelper.GetDocuments()
    .OrderByNodeOrder();
var query = DocumentHelper.GetDocuments()
    .Tap(q => 
    {
        // access the query 'q'
    });
bool condition = ...

var query = DocumentHelper.GetDocuments()
    .If(condition, q => 
    {
        // when condition is true
    });
bool condition = ...

var query = DocumentHelper.GetDocuments()
    .If(condition, 
    q => 
    {
        // when condition is true
    }, 
    q =>
    {
        // when condition is false
    });
var query = DocumentHelper.GetDocuments()
    .OrderByDescending(nameof(TreeNode.NodeID))
    .TopN(1)
    .DebugQuery();

/*
--- BEGIN [path\to\your\app\Program.cs] QUERY ---


DECLARE @DocumentCulture nvarchar(max) = N'en-US';

SELECT TOP 1 *
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID]
WHERE [DocumentCulture] = @DocumentCulture
ORDER BY NodeID DESC


--- END [path\to\your\app\Program.cs] QUERY ---
*/
var query = DocumentHelper.GetDocuments()
    .OrderByDescending(nameof(TreeNode.NodeID))
    .TopN(1)
    .DebugQuery("Newest Document");

/*
--- BEGIN [Newest Document] QUERY ---


DECLARE @DocumentCulture nvarchar(max) = N'en-US';

SELECT TOP 1 *
FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON [V].[NodeSKUID] = [S].[SKUID]
WHERE [DocumentCulture] = @DocumentCulture
ORDER BY NodeID DESC


--- END [Newest Document] QUERY ---
*/
var query = DocumentHelper.GetDocuments()
    .OrderByDescending(nameof(TreeNode.NodeID))
    .TopN(1)
    .TapQueryText(fullQueryText =>
    {
        Debug.WriteLine(fullQueryText);
    })
    .WhereEquals(...)
public void QueryDatabase(ILogger logger)
{
    var query = DocumentHelper.GetDocuments()
        .OrderByDescending(nameof(TreeNode.NodeID))
        .TopN(1)
        .LogQuery(logger, "Logged Query");
}
var query = DocumentHelper.GetDocuments()
    .Where(w => w.WhereInPath("path1", "path2"));

ObjectQuery

Prerequisites

using XperienceCommunity.QueryExtensions.Objects;

Examples

return UserInfo.Provider.Get()
    .Tap(q =>
    {
        // access the query
    });
bool condition = ...

var query = UserInfo.Provider.Get()
    .If(condition, q => 
    {
        // when condition is true
    });
bool condition = ...

var query = UserInfo.Provider.Get()
    .If(condition, 
    q => 
    {
        // when condition is true
    }, 
    q =>
    {
        // when condition is false
    });
var query = UserInfo.Provider.Get()
    .OrderByDescending(nameof(UserInfo.UserLastModified))
    .TopN(1)
    .DebugQuery();

/*
--- BEGIN [path\to\your\app\Program.cs] QUERY ---


SELECT TOP 1 *
FROM CMS_User
ORDER BY UserLastModified DESC


--- END [path\to\your\app\Program.cs] QUERY ---
*/
var query = UserInfo.Provider.Get()
    .OrderByDescending(nameof(UserInfo.UserLastModified))
    .TopN(1)
    .DebugQuery("User");

/*
--- QUERY [User] START ---


SELECT TOP 1 *
FROM CMS_User
ORDER BY UserLastModified DESC


--- QUERY [User] END ---
*/
public void QueryDatabase(ILogger logger)
{
    var query = UserInfo.Provider.Get()
        .OrderByDescending(nameof(UserInfo.UserLastModified))
        .TopN(1)
        .LogQuery(logger, "Logged User Query");
}
var query = UserInfo.Provider.Get()
    .TapQueryText(text =>
    {
        // do something with the query text
    });
var query = UserInfo.Provider.Get()
    .Source(s => s.InnerJoin<UserSettingInfo>(
        "UserID", 
        "UserSettingUserID", 
        "MY_ALIAS",
        additionalCondition: new WhereCondition("MY_ALIAS.UserWaitingForApproval", QueryOperator.Equals, true),
        hints: new[] { SqlHints.NOLOCK }))
    .TopN(1)
    .DebugQuery("User");

/*
--- QUERY [User] START ---


SELECT TOP 1 *
FROM CMS_User
INNER JOIN CMS_UserSetting AS MY_ALIAS WITH (NOLOCK) ON UserID = MY_ALIAS.UserSettingUserID AND MY_ALIAS.UserWaitingForApproval = 1
ORDER BY UserLastModified DESC


--- QUERY [User] END ---
*/
// ExecuteAsync returns a populated dataset with all the columns returned by the query.
// When there are no results, dataset.Tables[0] will still be populated with an empty DataTable.

var dataset = await UserInfo.Provider.Get()
    .Source(source => source
        .InnerJoin<UserSettingInfo>(
            "UserID", 
            "UserSettingUserID", 
            "MY_ALIAS")
        .InnerJoin<CustomerInfo>(
            "CustomerUserID",
            "UserID",
            "C",
            )
        )
    .Columns("UserID", "UserSettingID", "CustomerID")
    .ExecuteAsync();
    
foreach (var row in dataset.Tables[0].Rows)
{
    Console.WriteLine($"User: {row["UserID"]}, User Setting: {row["UserSettingID"]}, Customer: {row["CustomerID"]}");
}

Collections

Requirements

using XperienceCommunity.QueryExtensions.Collections;

Examples

TreeNode? page = await retriever
    .RetrieveAsync<TreeNode>(q => q.TopN(1), cancellationToken: token)
    .FirstOrDefaultAsync();
IList<TreeNode> pages = await retriever
    .RetrieveAsync<TreeNode>(cancellationToken: token)
    .ToListAsync();
IList<TreeNode> pages = await retriever
    .RetrieveAsync<TreeNode>(cancellationToken: token)
    .ToArrayAsync();

PageRetriever

Requirements

using Kentico.Content.Web.Mvc;

Examples

void GetPages(int pageIndex, int pageSize)
{
    var result = await retriever.RetrievePagedAsync<TreeNode>(
        pageIndex,
        pageSize,
        q => q.OrderByNodeOrder(),
        cancellationToken: token);

    int total = result.TotalRecords;
    List<TreeNode> pages = result.Items;

    // or

    var (totalRecords, pages) = await retriever.RetrievePagedAsync<TreeNode>(
        pageIndex,
        pageSize,
        q => q.OrderByNodeOrder(),
        cancellationToken: token);
}

XperienceCommunityConnectionHelper

Examples

var dataSet = await XperienceCommunityConnectionHelper.ExecuteQueryAsync("CMS.User", "GetAllUsersCustom");
string queryText = @"
SELECT *
FROM CMS_User
WHERE UserID = @UserID
"

var queryParams = new QueryDataParameters
{
    { "UserID", 3 }
};

var dataSet = await XperienceCommunityConnectionHelper.ExecuteQueryAsync(queryText, queryParams, token: token);

References

.NET

Kentico Xperience