Kentico Xperience Xplorations: Quickly Debugging Queries in a Console Application

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 repository, or read on and build your own 😊!

Starting Our Journey: Xperience's Data Access APIs

Kentico Xperience 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:

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

This ObjectQuery<UserInfo> query will generate the following 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 or something more powerful like Entity Framework Core, 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 😵:

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.

We can use GetFullQueryText() as follows:

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.

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, a great cross platform editor for building .NET applications 👍🏻.

I'll be using the .NET CLI 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 and custom Module 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:

> dotnet new sln --name Sandbox.XperienceQueries

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

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

{
   "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:

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

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

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:

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

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 🤓:

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:

~~~ 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 - 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
• Xperience appsettings.json Registration

...

As always, thanks for reading 🙏!

We've put together a list over on Kentico's GitHub account of developer resources. Go check it out!

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

Or my Kentico Xperience blog series, like:

• Kentico Xperience Xplorations
• Kentico Xperience MVC Widget Experiments
• Kentico 12 - Design Patterns