Kentico 12: Design Patterns Part 14 - DocumentQuery and ObjectQuery Tips
Tags: kentico, sql, csharp, dotnet
Published: 2019-09-23 14:04:47

---
title: Kentico 12: Design Patterns Part 14 - DocumentQuery and ObjectQuery Tips
published: true
description: Learn some tips and tricks for working with Kentico's DocumentQuery and ObjectQuery types to help improve the quality of your code and developer experience.
tags: kentico, sql, csharp, dotnet
series: Kentico 12 - Design Patterns
---

![Tip Jar](https://thepracticaldev.s3.amazonaws.com/i/oi9ig7vr8woleczeoyb5.jpg)
<figcaption>Photo by <a href="https://unsplash.com/@sam_truong">Sam Truong Dan
</a> on <a href="https://unsplash.com">Unsplash</a></figcaption>

## 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](https://docs.microsoft.com/en-us/ef/ef6/querying/), and the open source minimalist ORM [Dapper](https://github.com/StackExchange/Dapper), from the folks at StackOverflow 🧐.

Kentico's [documentation](https://docs.kentico.com/k12sp/custom-development/retrieving-database-data-using-objectquery-api) explains [the fundamentals](https://docs.kentico.com/k12sp/custom-development/working-with-pages-in-the-api) 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](https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/statements-expressions-operators/lambda-expressions) as parameters (although they do use [expression trees](https://docs.microsoft.com/en-us/dotnet/csharp/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`:

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

Or an `int` value `1`:

```csharp
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:

```csharp
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:

```csharp
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](https://docs.kentico.com/k12sp/custom-development/creating-custom-modules).

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:

```csharp
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 `SELECT`s 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:

```csharp
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`

> {% stackoverflow 4594652 %}

We can now use this extension as follows:

```csharp
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 `SELECT`s 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`:

```csharp
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:

```csharp
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`.
1. ❌ 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:

```csharp
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](https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/) or the [original](http://blogs.tedneward.com/post/the-vietnam-of-computer-science/), 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](https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-2017#sql-server-profiler-1) from within [SQL Server Management Studio (SSMS)](https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017) .

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:

![Expression DebugView](https://thepracticaldev.s3.amazonaws.com/i/ttfz85cqedw35wh9n60q.jpg)

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

```csharp
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.

```csharp
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](https://stackoverflow.com/a/31360768/939634).

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 😃.

```csharp
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!

```csharp
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()`:

```csharp
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:

```csharp
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 👏.

![SQL Query in Output window](https://thepracticaldev.s3.amazonaws.com/i/zs1k1ygvswjvcfsna73o.jpg)

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](https://www.nuget.org/packages/Kentico.Glimpse) NuGet package.

This integration with Glimpse creates a "Kentico SQL" tab in the [Glimpse](https://github.com/Glimpse/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:

{% tag kentico %}

Or my Kentico blog series:

- [Kentico 12: Design Patterns](https://dev.to/search?q=Kentico%2012%20-%20Design%20Patterns)