Kentico Data Access

If you've never written custom code to retrieve and manipulate data stored by Kentico CMS then you might not be familiar with the two types DocumentQuery<T> and ObjectQuery<T> 🤔.

These two classes, along with the large collection of related base classes, are what allow developers to write queries in their C# code that eventually get evaluated to a string of parameterized SQL, and executed in the database.

These types and methods sit somewhere between Microsoft's complex and feature rich Object Relational Mapper (ORM), Entity Framework, and the open source minimalist ORM Dapper, from the folks at StackOverflow 🧐.

Kentico's documentation explains the fundamentals of how to use these APIs, but is a little light on complex use-cases (understandably).

The documentation also doesn't list all the best patterns which might help you scale and maintain your application - it's intentionally low level and general purpose.

Below, let's look at several tips for how to get the most out of your DocumentQuery<T> and ObjectQuery<T> use ⚡.

Use nameof()

Unlike Entity Framework, Kentico's query APIs don't accept Lambda Expressions as parameters (although they do use expression trees) to generate the final SQL text.

Instead, they use a loosely-typed and string-based parameter API.

As an example, to get the set of all enabled users, you could use a bool value true:

var enabledUsers = UserInfoProvider
    .GetUsers()
    .WhereEquals("UserEnabled", true)
    .ToList();

Or an int value 1:

var enabledUsers = UserInfoProvider
    .GetUsers()
    .WhereEquals("UserEnabled", 1)
    .ToList();

Avoiding Typos

There is no type support for the column UserEnabled, which maps directly to the UserInfo.UserEnabled property, or requirement that "UserEnabled" is a valid column to place in the WHERE clause of the resulting query.

So, the query below would not work:

var enabledUsers = UserInfoProvider
    .GetUsers()
    .WhereEquals("UsersEnabled", true)
    .ToList();

Did you spot the typo? ... "UsersEnabled" should be "UserEnabled" 😣.

To help avoid these easily made typos, we can take advantage of two things:

• Kentico names all columns for a table that represents an entity the exact same as the properties on the C# class for that entity 👍.
• C# lets us use the nameof() operator to turn a token into a string 👍.

Here's my recommended approach for the above query:

var enabledUsers = UserInfoProvider
    .GetUsers()
    .WhereEquals(nameof(UserInfo.UserEnabled), true)
    .ToList();

Supporting Refactoring

This is helpful for queries against Kentico's built-in types, but I've found it's even more beneficial for queries against the custom Module class and Page types we create.

Let's assume we've created a UserMembershipProfile custom Module class.

Over the course of a project, our UserMembershipProfile class could have its column names changed several times as we iterate on a design.

If we use string column names in our queries, it's going to be harder to catch those column name changes without robust unit and integration tests 😮.

However, using nameof() gives us compile type checks against those database column names 😉.

We should use Kentico's column naming conventions, which is to prefix each column name with the entity name. This helps avoid column ambiguity when performing a JOIN in SQL.

So, the ID column for UserMembershipProfile would be UserMembershipProfileID and the custom Module class UserMembershipProfile would similarly have a property UserMembershipProfileID.

Create Re-usable Extensions

Kentico provides us with a large set of extension methods that can be used for querying both objects and documents.

Examples can be found in CMS.DataEngine.WhereConditionBase where we can see methods like the following:

public TParent Where(string columnName, QueryOperator op, object value);

public TParent WhereEquals(string columnName, object value);

public TParent WhereContains(string columnName, string value);

public TParent WhereNotNull(string columnName);

These methods all return the original query through the generic type parameter TParent, which allows us to chain multiple methods together, and these methods are all low level and lack any business meaning to our specific applications.

This is a good thing since it allows us to easily compose these pieces together.

However, we might want to provide re-usable methods similar to the ones above that apply a standard set of SELECTs or WHERE clauses without breaking the flow of our query syntax.

We can accomplish this pretty easily using extension methods on ObjectQuery<T> or DocumentQuery<T>.

Below is an example of an extension that adds a filter to the provided query that only includes UserInfo rows that are enabled and have logged in recently:

public static class UserInfoQueryExtensions
{
    public static ObjectQuery<UserInfo> WhereEnabledAndActive(
        this ObjectQuery<UserInfo> query)
    {
        DateTime thirtyDaysAgo = DateTime.Now.AddDays(-30);

        return query
            .WhereEquals(nameof(UserInfo.UserEnabled), true)
            .And()
            .WhereGreaterThan(nameof(UserInfo.LastLogon), thirtyDaysAgo);
    }
}

The use of .And() isn't necessary - AND is implied - but it helps to be explicit, especially in more complex queries.

Also, I wouldn't recommend using DateTime.Now directly since it's not going to be unit testable. Instead supply an interface to this extension method, like IDateTimeProvider

Strategies for dealing with DateTime.Now in unit tests

Jan 4 '11 Comments: 1 Answers: 3

7

I have business logic that does not perform certain functions on Saturday or Sunday. I want my unit tests to verify that these functions are performed, but the tests will fail on Saturday/Sunday.

I figure the easiest route is to have the unit tests convey a friendly message stating that…

Open Full Question

We can now use this extension as follows:

var activeUsers = UserInfoProvider
    .GetUsers()
    .WhereEnabledAndActive()
    .TypedResult
    .ToList();

This is an easy way to consolidate domain-specific re-usable querying logic throughout your application (or libraries) 🤓.

Trim Your SELECTs and Then Project!

When writing a new query it's pretty easy to filter down to the rows you want and forget filtering the columns.

Here's an example DocumentQuery to retrieve all the published Article documents on a site Sandbox:

string siteName = "Sandbox";

var articles = ArticleProvider
    .GetArticles()
    .OnSite(siteName)
    .Published(true)
    .TypedResult
    .ToList();

If we are only using DocumentName (from the CMS_Document table), NodeGUID (from the CMS_Tree table), and ArticleSummary (from the joined Sandbox_Article table), then we should be explicit about that and only SELECT those columns:

string siteName = "Sandbox";

var articles = ArticleProvider
    .GetArticles()
    .OnSite(siteName)
    .Published(true)
    .Columns(
        nameof(TreeNode.NodeGUID),
        nameof(TreeNode.DocumentName),
        nameof(Article.ArticleSummary)
    )
    .TypedResult
    .ToList();

Great! We've trimmed down our query so that it executes faster and requires fewer resources from the database 🎉🎉.

Unfortunately, we've created a leaky abstraction 😩!

The Article type is supposed to represent a fully hydrated object from the joining of the CMS_Tree, CMS_Document, and Sandbox_Article tables, but we've only populated three of its properties.

If we return this data from a method as IEnumerable<Article>, we are either...

1. ❌ Being dishonest to the consumers, which assume they can use any of the properties on Article.
2. ❌ Forcing consumers to know the exact shape and syntax of the query being executed.

Neither of these options is ideal, so instead of continuing to use the Article type, we can project the result of our query into either an anonymous type (if it doesn't need to leave the scope of the method), or a custom C# class:

string siteName = "Sandbox";

var articles = ArticleProvider
    .GetArticles()
    .OnSite(siteName)
    .Published(true)
    .Columns(
        nameof(TreeNode.NodeGUID),
        nameof(TreeNode.DocumentName),
        nameof(Article.ArticleSummary)
    )
    .TypedResult
    .Select(a => new { a.NodeGUID, a.DocumentName, a.ArticleSummary })
    .ToList();

foreach(var article in articles)
{
    Debug.WriteLine($"{article.NodeGUID} - {article.DocumentName}");
}

Using this approach means the code that follows our query is strongly-typed against the exact set of data we retrieved from the database 🤗.

Properties that are missing values won't accidentally be relied on, and consumers won't have to know what was SELECT'd in the query.

My rule of thumb:

If you want to use .Columns() to trim your SELECT, be a good developer and always project!

It rhymes, that's how you know it's good 😎.

Debug Your Queries

We are going to often run into situations where our queries don't return the data we expect, and it can be hard to figure out what's going on behind the scenes 😒.

This, unfortunately, is one of the issues that comes with using an ORM. C# is not SQL and SQL is not C#.

There's a long, but interesting post about the issues that developers always see when using an ORM 💯.

If interested, you can read Jeff Atwood's thoughts on the article or the original, just make sure you find a comfortable chair.

There are several ways we can "debug" the SQL that gets generated by the combination of APIs we use from Kentico's libraries.

The most classic, but most painful 😝 in my opinion, is to use SQL Profiler from within SQL Server Management Studio (SSMS) .

This is like using a blowtorch 🔥 to light a candle 🕯!

Expression.DebugView

Fortunately, C# provides us an out-of-the-box way to debug the generated SQL text.

Since Kentico's query APIs generate an Expression which is turned into SQL, we can look at that SQL text, while debugging a running application.

This SQL text can be found on ObjectQuery<T>.Expression.DebugView or DocumentQuery<T>.Expression.DebugView as seen below:

This is the SQL generated by the following ObjectQuery<UserInfo> code:

var usersQuery = UserInfoProvider
    .GetUsers()
    .WhereEquals(nameof(UserInfo.UserEnabled), true);

var users = usersQuery
    .TypedResult
    .ToList();

We have to create an intermediate variable, usersQuery, to see the DebugView.

If we were to only have one variable, which was the result of a call to .ToList(), the result would be the materialized data, which isn't what we want.

var users = UserInfoProvider
    .GetUsers()
    .WhereEquals(nameof(UserInfo.UserEnabled), true)
    .TypedResult
    .ToList();

// No `users.Expression` available here!

This DebugView property is internal, so it can only be accessed through reflection in our code.

But don't worry, there's an even easier way 😅!

ObjectQueryBase.GetFullQueryText

Another approach is through a method that Kentico provides on CMS.DataEngine.ObjectQueryBase<TQuery> named GetFullQueryText().

It returns a string of the SQL that would be generated 😃.

public string GetFullQueryText(
    bool expand = false, 
    bool includeParameters = true, 
    DataQuerySettings settings = null);

While this is great for getting the query text, we might not want to go through a bunch of queries and make intermediate variables for the ObjectQuery<T> separate from the set of result data we want to work with 😫.

So, let's make an extension method to help us debug and make our code easier to read and work with!

public static class ObjectQueryExtensions
{
    public static ObjectQuery<T> PrintToDebug<T>(this ObjectQuery<T> query)
        where T : BaseInfo
    {
        string queryText = query.GetFullQueryText();

        Debug.WriteLine(queryText);

        return query;
    }
}

With this extension, we can turn the following use of .GetFullQueryText():

var usersQuery = UserInfoProvider
    .GetUsers()
    .WhereEquals(nameof(UserInfo.UserEnabled), true);

string queryText = usersQuery.GetFullQueryText();

Debug.WriteLine(queryText);

var users = usersQuery
    .PrintToDebug()
    .TypedResult
    .ToList();

Into something easier to read and maintain:

var users = UserInfoProvider
    .GetUsers()
    .WhereEquals(nameof(UserInfo.UserEnabled), true)
    .PrintToDebug()
    .TypedResult
    .ToList();

This will print the full SQL text to the Output window in Visual Studio, assuming you are debugging the application 👏.

You can get copy and paste it into SSMS, Azure Data Studio, LinqPad, ect... to try and diagnose your querying issues.

Kentico.Glimpse

For Kentico 12 MVC applications (it's compatible with Portal Engine sites as well), Kentico provides the Kentico.Glimpse NuGet package.

This integration with Glimpse creates a "Kentico SQL" tab in the Glimpse UI panel that appears at the bottom of your browser after every page response from the application.

This SQL tab includes all the queries run during the request for the page, with all parameters applied 👍.

If you already have Glimpse integrated into your Kentico application, this is a pretty easy way to debug your queries.

Summary

We covered using the nameof() operator to ensure the column names in the generated SQL text are always free of typos.

It also helps our code to be refactor-proof, providing some type support by making sure our query columns match the names in the code provided or generated by Kentico.

We looked at how we can use extension methods to create re-usable units of query code, smaller than a normal method, and nearly as composable as Kentico's built in querying methods.

In our attempt to optimize a query to retrieve Article documents from the database, we created a point of confusion in our code since our type Article no longer reflected the set of data we queried for.

This lead to the rule of thumb - If you want to use .Columns() to trim your SELECT, be a good developer and always project! - which I'll never get tired of saying 🤣.

Finally we looked at the various ways we can debug our queries and see exactly what SQL text is being generated.

Using an extension method and the .GetFullQueryText() method gave us a simple and re-usable solution for debugging.

I hope you found this dive into Kentico's data access APIs, ObjectQuery<T> and DocumentQuery<T>, helpful.

If you already knew all of this, then hopefully my future posts on Kentico's querying APIs will provided a deeper dive than this one!

Thanks for reading 🙏!

If you are looking for additional Kentico content, checkout the Kentico tag here on DEV:

Or my Kentico blog series:

• Kentico 12: Design Patterns