Kentico Xperience Xplorations: Quickly Debugging Queries in a Console Application
Tags: dotnet, xperience, cms, csharp
Published: 2020-12-14 17:45:21

---
title: Kentico Xperience Xplorations: Quickly Debugging Queries in a Console Application
published: true
description: Let's explore the fastest way to quickly iterate and troubleshoot Kentico Xperience data access queries
tags: dotnet, xperience, cms, csharp
series: Kentico Xperience Xplorations
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/algcgsbuf2z4hv65yrw7.jpg
---

> In this post we'll be exploring how to quickly and easily debug our Kentico Xperience 13 queries with a .NET 5 Console application and some helpful Xperience NuGet packages 😮.

> You can skip the post and jump right into Xperience query debugging with the [Xperience Query Debugger](https://github.com/wiredviews/xperience-query-debugger) repository, or read on and build your own 😊!

## Starting Our Journey: Xperience's Data Access APIs

[Kentico Xperience](https://xperience.io/) is a data driven platform, with much of the data being the content, created by content editors in the Content Management (CMS) application, persisted in the MS SQL database.

As software developers using Xperience, our job regularly requires us to write code that retrieves that data from the database to display to visitors of the Content Delivery (MVC) application.

Xperience provides developers with a robust 💪🏽 set of primitive APIs to query data in the database and return back strongly typed results in C#.

These APIs primarily come from the `DocumentQuery`, `MultiDocumentQuery`, and `ObjectQuery` types.

### Simple Queries

Sometimes it's pretty clear how the Xperience query APIs translate into the SQL that is then executed in the database:

```csharp
List<UserInfo> users = UserInfo.Provider.Get()
    .WhereLike("Email", "%@localhost.local")
    .ToList();
```

This `ObjectQuery<UserInfo>` query will generate the following SQL:

```sql
DECLARE @Email nvarchar(max) = N'%@localhost.local';

SELECT *
FROM CMS_User
WHERE [Email] LIKE @Email
```

Ya... pretty simple 😋!

> If you have used a micro Object Relational Mapper (ORM) like [Dapper](https://github.com/StackExchange/Dapper) or something more powerful like [Entity Framework Core](https://github.com/dotnet/efcore), Xperience's data access APIs fall somewhere in the middle of those.

### Complex Queries

However, other times, when we start writing more complex queries, we might not be so sure what kind of SQL the Xperience queries will generate.

For example, I imagine we would all have a pretty difficult time figuring out, exactly, what kind of SQL this `DocumentQuery` (which was copied from a real project) would create 😵:

```csharp
var services = ServicePageProvider.GetServicePages()
    .GetLatestSiteDocuments(Context)
    .OrderByNodeOrder()
    .Source(s => s.Join(
        new QuerySourceTable("CMS_Relationship", "R"), 
        "V.NodeID", 
        "R.RightNodeId"))
    .Source(s => s.Join(
        new QuerySourceTable("CMS_RelationshipName", "RN"), 
        "R.RelationshipNameID", 
        "RN.RelationshipNameID"))
    .WhereEquals("RN.RelationshipName", "HasService")
    .WhereIn(
        "R.LeftNodeId", 
        pages.Select(p => p.NodeID).ToArray())
    .Columns(
        "R.LeftNodeId as ProjectNodeId", 
        "C.ServicePageGraphicMediaPath");
```

In these situations, how might we iterate on a query, adding, testing, and removing code as needed, to get the SQL we want 🤔?

## Our First Stop: Running our ASP.NET Core Application

The simplest solution is to write our query, run the application, and debug our code using a breakpoint to see what ends up in the result of our query. If it matches our expectations, then we're probably good to move on to the next problem 🤨.

But there's some caveats to this approach:

- ❌ It requires us to startup and run a full ASP.NET Core application
- ❌ If we don't know what SQL is being executed, it's hard to catch corner cases
- ❌ If we have multiple environments (Local, Development, UAT, Production), we might have unique data in each environment that doesn't work with our query, and we can't just run our new code against Production!

## A Step in the Right Direction: Using `GetFullQueryText()`

Instead of looking at the resulting collection of C# objects that our queries generate, we can use a helpful method, `.GetFullQueryText()` that exists on the `DataQueryBase` class, which means all our `DocumentQuery`, `MultiDocumentQuery`, `ObjectQuery` queries have access to it.

`GetFullQueryText()` returns a `string` containing the SQL that the given query would generate, including parameter values 👏🏾.

> I previously mentioned this method in a previous post, _[Kentico 12: Design Patterns Part 14 - DocumentQuery and ObjectQuery Tips](https://dev.to/seangwright/kentico-12-design-patterns-part-14-documentquery-and-objectquery-tips-2c7h)_.

We can use `GetFullQueryText()` as follows:

```csharp
var query = UserInfo.Provider.Get()
    .OrderBy(nameof(UserInfo.UserID)
    .TopN(5);

string queryText = query.GetFullQueryText();

var results = query.ToList();
```

We can log the value of `queryText` or, while debugging, copy it and execute it our [favorite MS SQL editor](https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15).

While this approach is better than checking the C# objects our query generates 😉, and lets us run the SQL against multiple environments, it still requires we launch an ASP.NET Core application 😒.

## Our Destination: Xperience In a Console Application

What if I told you there's a way to setup an environment where we can quickly iterate on our queries without the hassle that a web application brings?

Sounds great 😃!

We want to get rid of as much unnecessary code as possible, and there's nothing more trimmed down than a simple .NET console app.

> As a bonus, since Xperience 13.0 supports .NET Core / .NET 5, which are cross platform, we can use [VS Code](https://code.visualstudio.com/), a great cross platform editor for building .NET applications 👍🏻.

> I'll be using the [.NET CLI](https://docs.microsoft.com/en-us/dotnet/core/tools/) to scaffold out the application, but you can use Visual Studio as well.

### Digging Deeper: Setting Up the Console App

#### Keeping Our Project Local

If we want to debug queries using code from an active project, we probably want to reference shared libraries to reduce copying and pasting, especially for [custom Page Types](https://docs.xperience.io/developing-websites/defining-website-content-structure/managing-page-types/creating-page-types) and [custom Module classes](https://docs.xperience.io/custom-development/creating-custom-modules/creating-custom-binding-classes).

In that case, we can run these commands in our existing project folder and create a new Solution just for this debugging project and the projects it references. We can then exclude the solution and console app project from source control so our query experiments don't conflict with anyone else's 🤗.

#### Creating Our Solution

Let's first create a new Solution using the .NET CLI:

```powershell
> dotnet new sln --name Sandbox.XperienceQueries
```

Now we'll make a new console application project and add it to the solution:

```powershell
> dotnet new console --name Sandbox.XperienceQueries.App

> dotnet sln .\Sandbox.XperienceQueries.sln add .\Sandbox.XperienceQueries.App
```

Now, we can open our project from the command line in VS Code:

```
> code .
```

The first thing we want to do is create an `appsettings.json` file in the root of the project and add our `CMSConnectionString` to it, so Xperience can query our database:

```json
{
   "ConnectionStrings": {
      "CMSConnectionString": "<add your connection string here>"
    }
}
```

Now we'll add some packages to the console app (this could be done via the .NET CLI `dotnet add` command, but copying and pasting is also fun 😁).

Add this XML to the `Sandbox.XperienceQueries.csproj` file:

```xml
<ItemGroup>
  <PackageReference 
    Include="Kentico.Xperience.Libraries" 
    Version="13.0.6" />
  <PackageReference 
    Include="WiredViews.Xperience.AppSettingsJsonRegistration" 
    Version="1.0.0" />
  <PackageReference 
    Include="WiredViews.Xperience.QueryExtensions" 
    Version="1.0.0" />
</ItemGroup>
```

Here's a summary of the 3 packages we added:
- `Kentico.Xperience.Libraries` gives us access to all the Xperience APIs we need to query the database
- `WiredViews.Xperience.AppSettingsJsonRegistration` tells Xperience to use the `appsettings.json` file as a source of configuration (and our connection string)
- `WiredViews.Xperience.QueryExtensions` give us a friendly way to use `GetFullQueryText()` as `.DebugQuery()` and `.LogQuery()` extension methods

While we're here, let's add configuration for the `appsettings.json` we created:

```xml
<ItemGroup>
  <None Update="appsettings.json">
    <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
  </None>
</ItemGroup>
```

This ensures our `appsettings.json` file will be copied to the `\bin` directory when we build our app, which means it can be found when we run it 👍.

Last, but not least, we need to tell Xperience to 'start' and get all of its internals wired up. We can do this in our console application's `Main()` method:

```csharp
class Program
{
    static void Main(string[] args)
    {
        CMSApplication.Init();
        
        // write our query here...
    }
}
```

### Writing Our Query

Now we can finally write our query, using the nice `DebugQuery()` extension method from `WiredViews.Xperience.QueryExtensions`:

```csharp
new ObjectQuery<UserSettingsInfo>()
    .From(new QuerySource(new QuerySourceTable("CMS_UserSettings", "US")))
    .Source(s => s.Join(new QuerySourceTable("CMS_User", "U"), "US.UserSettingsUserID", "U.UserID"))
    .WhereLike("Email", "%@localhost.local")
    .WhereTrue("UserEnabled")
    .DebugQuery("User Settings");
```

`DebugQuery()` will print the full query text to the Debug console, from the query that is generated from all the preceding methods. The text sent to the Debug console will be labeled with "User Settings", the name passed to `DebugQuery()`.

### Debugging Our Query

In VS Code, setting up debugging for .NET Core application is pretty simple 😎.

Click the "Play" icon in the left sidebar and click "Create a launch.json file".

Then select ".NET Core" from the list in the dialog, and click the green play button in the drop down list with ".NET Core Launch (console)" selected.

You can place breakpoints in your code and view the `.DebugQuery()` output displayed in the "DEBUG CONSOLE".

![Using VS Code to configure debugging for .NET Core](https://dev-to-uploads.s3.amazonaws.com/i/siuhly8yn5t47dz9virx.gif)

We can now quickly change our query and debug our code in a matter a seconds 🤩.

If we want we can add a call to `.DebugQuery()` after each `ObjectQuery` method, which will show us the changes that each method makes to the query 🤓:

```csharp
new ObjectQuery<UserSettingsInfo>()
    .From(new QuerySource(new QuerySourceTable("CMS_UserSettings", "US")))
    .DebugQuery("From")
    .Source(s => s.Join(new QuerySourceTable(
        "CMS_User", "U"), 
        "US.UserSettingsUserID", 
        "U.UserID"))
    .DebugQuery("Join")
    .WhereLike("Email", "%@localhost.local")
    .DebugQuery("Like Email")
    .WhereTrue("UserEnabled")
    .DebugQuery("User Enabled");
```

This will result in the following Debug output:

```bash
~~~ BEGIN [From] QUERY ~~~

SELECT *
FROM CMS_UserSettings AS US

~~~ END [From] QUERY ~~~

~~~ BEGIN [Join] QUERY ~~~

SELECT *
FROM CMS_UserSettings AS US INNER JOIN CMS_User AS U ON [US].[UserSettingsUserID] = [U].[UserID]

~~~ END [Join] QUERY ~~~

~~~ BEGIN [Like Email] QUERY ~~~

DECLARE @Email nvarchar(max) = N'%@localhost.local';

SELECT *
FROM CMS_UserSettings AS US INNER JOIN CMS_User AS U ON [US].[UserSettingsUserID] = [U].[UserID]
WHERE [Email] LIKE @Email

~~~ END [Like Email] QUERY ~~~

~~~ BEGIN [User Enabled] QUERY ~~~

DECLARE @Email nvarchar(max) = N'%@localhost.local';
DECLARE @UserEnabled bit = 1;

SELECT *
FROM CMS_UserSettings AS US INNER JOIN CMS_User AS U ON [US].[UserSettingsUserID] = [U].[UserID]
WHERE [Email] LIKE @Email AND [UserEnabled] = @UserEnabled

~~~ END [User Enabled] QUERY ~~~
```

That's pretty awesome 🎉🥳!

---

## Conclusion

Kentico Xperience's data access APIs give developers a low-level set of tools to build both simple and complex SQL queries in C#.

Most of the time these queries are run in the context of an ASP.NET Core application.

When we need to troubleshoot our queries or explore all the various building blocks the Xperience APIs provide us, it is often easier to quickly iterate outside of a large complex ASP.NET Core application.

Fortunately, we can use a .NET Core / .NET 5 console application to iterate as quickly as possible on our `DocumentQuery`, `MultiDocumentQuery`, or `ObjectQuery` code.

Hopefully you now know 🧐 how to build your own simple console app for query debugging...

Or you can use the the repository I already created - [Xperience Query Debugger](https://github.com/wiredviews/xperience-query-debugger) - which is all set up and ready to go 😊.

Here are the Xperience libraries, on GitHub, used to make all of this easier:

- [Xperience Query Extensions](https://github.com/wiredviews/xperience-query-extensions)
- [Xperience appsettings.json Registration](https://github.com/wiredviews/xperience-appsettings-json-registration)

...

As always, thanks for reading 🙏!

---

<figcaption>Photo by <a href="https://unsplash.com/@jordanmadrid">Jordan Madrid
</a> on <a href="https://unsplash.com">Unsplash</a></figcaption>

---

We've put together a list over on [Kentico's GitHub account](https://github.com/Kentico/Home/blob/master/RESOURCES.md) of developer resources. Go check it out!

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

{% tag kentico %}

Or my [Kentico Xperience blog series](https://dev.to/seangwright/series), like:

- [Kentico Xperience Xplorations](https://dev.to/seangwright/series/8185)
- [Kentico Xperience MVC Widget Experiments](https://dev.to/seangwright/series/9483)
- [Kentico 12 - Design Patterns](https://dev.to/search?q=Kentico%2012%3A%20Design%20Patterns)