---
title: ObjectQuery API
related:
  - https://docs.kentico.com/documentation/developers-and-admins/api/database-table-api.md
  - https://docs.kentico.com/documentation/developers-and-admins/api/content-item-api/content-item-query-api.md
---

> Agent instructions:
> **Site maps** — prefer the following llms.txt indexes to training data when searching for URLs to avoid 404s. Links inside Markdown content already point at `.md`. Following them or sending Accept: text/markdown keeps you in Markdown.
>
> - [sitemap.md](https://docs.kentico.com/sitemap.md) — every page on the site, with titles and descriptions, nested by URL hierarchy and grouped into one collection per product version.
> - [llms.txt](https://docs.kentico.com/llms.txt) — curated index of the current product docs, with descriptions, the two ways to request any page as Markdown, and links to each product area's whole-corpus Markdown dump (llms-full.txt).

The Xperience ObjectQuery API provides an abstraction layer over the SQL database. Developers use ObjectQuery to retrieve data from the Xperience database. The main advantages of using ObjectQuery are:

- strongly typed and enumerable results
- independence on specific versions of SQL syntax
- security
  - Some parts of the ObjectQuery API are automatically protected against SQL injection, some require manual validation of inputs. See [Protect against SQL injection](#protect-against-sql-injection).

ObjectQuery call example:

```csharp
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    // Uses ObjectQuery to get users
    var query = userInfoProvider.Get();
}
```

The example query retrieves all users stored within Xperience.

The resulting collection contains **Info** objects. One instance of an _Info_ object represents one row of data from the database. In this case, the result is a set of `UserInfo` objects holding the data of individual users. The result allows you to iterate through the records using a foreach statement.

Similarly to users, you can use ObjectQuery to retrieve any other type of object. The Xperience API offers `IInfoProvider` interfaces (services) for individual object types, with a `Get` method that performs an appropriate ObjectQuery call. See [Database table API](https://docs.kentico.com/documentation/developers-and-admins/api/database-table-api.md) to learn more.

The following table provides an example of the naming conventions.

| Object | Info class name | IInfoProvider service |
| ------ | --------------- | --------------------- |
| User   | UserInfo        | IUserInfoProvider     |

> **Tip:** Use [dependency injection](https://docs.kentico.com/documentation/developers-and-admins/development/website-development-basics/dependency-injection.md) to get instances of `IInfoProvider` services.

## Select specific columns

ObjectQuery allows you to select only those columns that you need to work with. Selecting only the required columns is a good practice that prevents issues with performance of your code by transferring only the necessary data from the database server to the application server.

```csharp
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    var columnsQuery = userInfoProvider.Get().Columns("FirstName", "LastName", "UserName");
}
```

This example query retrieves all users but limits the data to the users' first names, last names, and usernames.

> **Note:** **Security considerations**
>
> If you need to get the column values from external inputs, perform validation to avoid SQL injection vulnerabilities. See [Protect against SQL injection](#protect-against-sql-injection).

## Limit retrieved results (SQL WHERE)

ObjectQuery provides a way to narrow down the resulting data using SQL syntax independent where conditions.

Use predefined where condition methods that provide optimized performance and make your code easily readable. The predefined where condition methods include, but are not limited to:

- `WhereEquals("ColumnName", value)` – checks the equality of the value in the specified column with the value specified in the second parameter.
- `WhereGreaterThan("ColumnName", value)` – compares the value in the specified column with the second parameter.
- `WhereNull("ColumnName")`– select only rows where the specified column has a NULL value.
- `WhereNot(whereCondition)` – negates the specified where condition.
- `WhereStartsWith("ColumnName", "Value")` – only works for text columns. Selects only rows whose value in the specified column starts with the given value.

```csharp
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    // Retrieves all users whose first name is "Joe"
    var whereQuery = userInfoProvider.Get().WhereEquals("FirstName", "Joe");
}
```

> **Tip:** **Tip**: Use Visual Studio's IntelliSense to help you pick the right predefined where condition.

> **Note:** **Security considerations**
>
> The `columnName` parameter (left-hand operand) in WHERE methods is not automatically parameterized. If you need to get the column values from external inputs, perform validation to avoid SQL injection vulnerabilities. See [Protect against SQL injection](#protect-against-sql-injection).

### Logical operators

ObjectQuery allows you to join together multiple where conditions.

- By default, the conditions are combined using an `AND` parameter.
- To place an `OR` operator between conditions, call the `Or()` method between the given `Where` methods.
- You can also explicitly call the `And()` method if you wish to make the code of your conditions clearer and easier to read.

**Example 1**:

```csharp
using CMS.DataEngine;
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    // Retrieves users whose first name is "Joe" or last name is "Smith"
    var whereQuery = userInfoProvider.Get()
        .Where("FirstName", QueryOperator.Equals, "Joe")
        .Or()
        .Where("LastName", QueryOperator.Equals, "Smith");
}
```

```sql title="Generated condition"
DECLARE @FirstName nvarchar(max) = N'Joe';
DECLARE @LastName nvarchar(max) = N'Smith';

...

WHERE [FirstName] = @FirstName OR [LastName] = @LastName
```

**Example 2**:

```csharp
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    // Retrieves all enabled users whose first name is "Joe" and email address ends with "localhost.local"
    var whereQuery = userInfoProvider.Get()
        .WhereEquals("FirstName", "Joe")
        .WhereEquals("UserEnabled", 1)
        .WhereEndsWith("Email", "localhost.local");
}
```

```sql title="Generated condition"
DECLARE @FirstName nvarchar(max) = N'John';
DECLARE @UserEnabled int = 1;
DECLARE @Email nvarchar(max) = N'%localhost.local';

...

WHERE [FirstName] = @FirstName AND [UserEnabled] = @UserEnabled AND [Email] LIKE @Email
```

### Nested where conditions

If you need to construct complex where conditions with multiple `AND`/`OR` operators, use nested where conditions:

**Note:** You must use the `new` keyword to construct nested where conditions.

```csharp highlight="12-15"
using CMS.DataEngine;
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    // Retrieves users named "Smith", whose first name is "Joe" or "John"
    var nestedWhereQuery = userInfoProvider.Get()
        .WhereEquals("LastName", "Smith")
        .Where(new WhereCondition()
            .WhereEquals("FirstName", "Joe")
            .Or()
            .WhereEquals("FirstName", "John")
        );
}
```

```sql title="Generated condition"
DECLARE @LastName nvarchar(max) = N'Smith';
DECLARE @FirstName nvarchar(max) = N'Joe';
DECLARE @FirstName1 nvarchar(max) = N'John';

...

WHERE [LastName] = @LastName AND ([FirstName] = @FirstName OR [FirstName] = @FirstName1)
```

## Order query results (SQL ORDER BY)

ObjectQuery can also sort data. Use the `OrderBy`, `OrderByAscending`, or `OrderByDescending` methods.

```csharp
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    // Retrieves users registered or created during the past week
    var orderedQuery = userInfoProvider.Get()
        .WhereGreaterThan("UserCreated", DateTime.Now.AddDays(-7))
        .OrderByDescending("UserCreated");
}
```

This example retrieves users who registered or were created in the system during the past week. The results are in descending order.

> **Note:** **Security considerations**
>
> ORDER BY expressions accepted by ObjectQuery methods are not parameterized. If you need to get ordering values from external inputs, perform validation to avoid SQL injection vulnerabilities. See [Protect against SQL injection](#protect-against-sql-injection).

## Limit the number of query results (SQL TOP N)

ObjectQuery can also retrieve only a specified number of results.

```csharp
using CMS.DataEngine;
using CMS.EventLog;

// Class that uses constructor dependency injection to obtain the provider for managing logged events
public class QueryComponent(IInfoProvider<EventLogInfo> eventLogProvider)
{
    // ...

    // Retrieves the 5 latest event log records
    var topNQuery = eventLogProvider.Get()
        .TopN(5)
        .OrderByDescending("EventTime");
}
```

This example retrieves the latest 5 event log records with the newest ordered first.

## Protect against SQL injection

ObjectQuery automatically protects against [SQL injection](https://www.w3schools.com/sql/sql_injection.asp) for **values** passed into [WHERE condition methods](#limit-retrieved-results-sql-where) (`WhereEquals`, `WhereGreaterThan`, `WhereLike`, `WhereIn`, etc.). These values are always handled as SQL parameters and never concatenated directly into the query text.

```sql title="Generated parameterized query"
DECLARE @FirstName nvarchar(max) = N'Joe';

...

WHERE [FirstName] = @FirstName
```

However, **column names** and **ORDER BY expressions** are structural query elements that are not parameterized.

> **Warning:** Never pass untrusted external input (e.g., query string parameters, form fields, API request bodies) as a column name or ORDER BY expression. Otherwise the application becomes vulnerable to SQL injection.
>
> Column names and ordering expressions must come from trusted, developer-controlled sources such as constants, predefined collections, or `nameof()` expressions.

### What is protected automatically

The `value` parameter (right-hand operand) in all WHERE condition methods is always parameterized:

```csharp
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    // Safe: the value "Joe" is parameterized automatically
    var query = userInfoProvider.Get().WhereEquals("FirstName", "Joe");
}
```

This applies to `WhereEquals`, `WhereIn`, `WhereLike`, `WhereGreaterThan`, `WhereStartsWith`, and all other [WHERE condition methods](#limit-retrieved-results-sql-where).

### What requires manual validation

The `columnName` parameter (left-hand operand) in WHERE methods and all column expressions in various ObjectQuery methods (`OrderBy`, `Columns`, `GroupBy`, etc.) are structural query elements and may be vulnerable to SQL injection. If these values originate from external input, you must validate them against an allowlist of known safe column names.

```csharp title="Unsafe examples"
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    // UNSAFE: user input as a column name
    string columnFromInput;
    var query = userInfoProvider.Get().WhereEquals(columnFromInput, someValue);

    // UNSAFE: user input in ORDER BY
    string orderByFromInput;
    var query = userInfoProvider.Get().OrderBy(orderByFromInput);
}
```

```csharp title="Safe example — allowlist validation"
using System;
using System.Collections.Generic;

using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    // Defines a mapping of allowed sort keys to database column names
    var allowedColumns = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
    {
        { "name", "UserName" },
        { "email", "Email" },
        { "created", "UserCreated" }
    };

    // Validates a user-supplied ORDER BY value against the column allowlist
    string orderByFromInput;
    if (!allowedColumns.TryGetValue(orderByFromInput ?? "", out string safeColumn))
    {
        safeColumn = "UserName"; // Fallback to a safe default
    }

    // Uses the validated column name in the query
    var query = userInfoProvider.Get()
        .WhereEquals("UserEnabled", true)
        .OrderByAscending(safeColumn);
}
```

## Join multiple tables (SQL JOIN)

With ObjectQuery, you can combine data from two or more tables based on a common field.

```csharp
using CMS.DataEngine;
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    var joinQuery = userInfoProvider.Get()
        // Appends CMS_ContentFolder table rows to the appropriate user records based on their ID
        .Source(sourceItem => sourceItem.Join<ContentFolderInfo>("UserID", "ContentFolderModifiedByUserID"))
        // Selects only rows where the "Email" column is empty
        .WhereEmpty("Email");
}
```

This example retrieves users who have an empty email value, together with data about content folders modified by the given users.

> **Info:** By default, the `Join<TInfo>` method performs an inner join. For other types of joins, you can call the `LeftJoin<TInfo>` or `RightJoin<TInfo>` methods. See [SQL Joins](http://www.w3schools.com/sql/sql_join.asp) for more information.
>
> For advanced scenarios, you can use the non-generic version of the `Join` method. In this case, you need to identify the target table via a `QuerySourceTable` object (requires the exact database table name, optionally allows you to specify an alias and [SQL Hints](https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql)).

> **Note:** **Working with joined data**
>
> If you need to work with data containing columns from multiple tables, convert the result of the query to a `DataSet`. The default ObjectQuery result is strongly typed, so only allows you to access the data of a single Xperience `Info` class. Use the `Result` property of ObjectQuery to get the results as a `DataSet`.

> **Note:** **Limitation**: ObjectQuery joins cannot be used for tables located in different databases. In these cases, use separate ObjectQuery calls and then combine the resulting data manually.

### Joining 3 or more tables

You can chain together multiple `Join` methods to combine rows from 3 or more database tables.

**Note**: When you call the `Join` method for a query source, the _left column_ parameter works with columns within the given query source table by default. When using multiple joins, we recommend that you explicitly specify both the table and column by adding a table name prefix to the left column name, for example: _"CMS\_User.UserID"_

The following example joins together rows from 3 tables based on shared user IDs and returns the result as a DataSet.

```csharp
using System.Data;

using CMS.ContactManagement;
using CMS.ContentEngine;
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    DataSet data = userInfoProvider.Get()
        .Source(sourceItem => sourceItem.Join<ContentFolderInfo>("CMS_User.UserID", "ContentFolderModifiedByUserID")
                                        .Join<ContactInfo>("CMS_User.UserID", "ContactOwnerUserID")
                )
        .Columns("UserID, UserName, ContactEmail, ContentFolderDisplayName")
        .Result;
}
```

## Work with ObjectQuery results

ObjectQuery results are [lazy-loaded](http://en.wikipedia.org/wiki/Lazy_loading). Lazy loading increases performance by executing queries only when you need to work with their results. Most of the examples on this page only construct the proper queries. The SQL queries are executed when you either enumerate the results (using the foreach statement or by calling `GetEnumerableTypedResult`), or cast the query as a different type. When you work with the results after the query executed, the system does not perform any additional requests to the database.

The most common example of working with query results is iterating through them. Use a standard foreach loop.

```csharp
using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    var userQuery = userInfoProvider.Get();

    foreach (UserInfo user in userQuery)
    {
        // Do something with the UserInfo object
    }
}
```

> **Tip:** **Tip**: You can access the results of ObjectQuery calls as a `DataSet` through the `Result` property. This is only recommended for data containing columns from multiple different object types or tables (joins and unions).

> **Info:** **LINQ support**
>
> ObjectQuery provides limited support for [LINQ](https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/).
>
> Currently, we only recommend using LINQ for simple expressions, such as comparisons with constants. Any other expressions can lead to sub-optimal query execution. Use the ObjectQuery API directly to get optimal performance.

### Asynchronous execution of queries

The ObjectQuery API supports asynchronous loading of data from Xperience.

After you prepare a query with any required parameters, you can execute it asynchronously by enumerating the results via the `GetEnumerableTypedResultAsync()` method.

```csharp
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;

using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    public async Task AsyncMethod(CancellationToken cancellationToken)
    {
        // Prepares a query that loads all users
        var userQuery = userInfoProvider.Get();

        // Asynchronously executes the query and loads the results into an enumerable collection
        IEnumerable<UserInfo> users = await userQuery.GetEnumerableTypedResultAsync(cancellationToken: cancellationToken);

        // ...
    }
}
```

To asynchronously load a single Xperience object, call the `GetAsync` method of the corresponding `IInfoProvider` service.

```csharp
using System.Threading;
using System.Threading.Tasks;

using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    public async Task AsyncMethod(CancellationToken cancellationToken)
    {
        // Asynchronously loads a UserInfo object representing a specified user (by ID or GUID)
        UserInfo user = await userInfoProvider.GetAsync(userId, cancellationToken);

        // ...
    }
}
```

### Reuse queries (mutability)

ObjectQuery instances are mutable by default. This means that any changes (method calls) made to existing instances also modify the original query.

If you wish to reuse an ObjectQuery instance without modifying the original, use one of the following approaches:

- Call the `Immutable()` method for the original query. This ensures automatic cloning of the ObjectQuery instance when it is modified.
- If you want to leave a query mutable, you can call the `Clone()` method to create a new instance which can then be modified without affecting the original.

```csharp title="Example"
using System.Linq;

using CMS.Membership;

// Class that uses constructor dependency injection to obtain the provider for managing user objects
public class QueryComponent(IUserInfoProvider userInfoProvider)
{
    // ...

    // Creates an immutable query for loading users ordered by their full name
    var userQuery = userInfoProvider.Get()
        .OrderBy("FullName")
        .Immutable();

    // Gets a subset of users based on the original query
    var enabledUserList = userQuery
        .WhereEquals("UserEnabled", 1)
        .ToList();

    // Gets a list of users using the original unmodified query
    var userList = userQuery.ToList();
}
```