Creating queries with LINQ

Current version: 3.3

This section describes how to create queries to find specific entities in Sitecore Content Hub.

The query object itself does not do querying but it describes the conditions of the querying. The querying client is responsible for executing the queries.

First the options and structure of LINQ querying in Sitecore Content Hub will be described, and at the end are some examples.

Getting started with LINQ

The recommended way to create queries is to use LINQ. If you are unfamiliar with LINQ, have a look at https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/getting-started-with-linq.

To fully use LINQ querying, make sure to have the following usings:

RequestResponse
using System.Linq;
using Stylelabs.M.Base.Querying;
using Stylelabs.M.Base.Querying.Linq;

Template

The LINQ template is as follows:

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where [conditions]
    orderby [property] ascending | descending
    select e);

Multiple conditions in the where clause can be chained by using &&.

orderby can be used to order results based on certain (system) properties, either ascending or descending. Multiple orderby statements can be used for more complex ordering.

Supported entity filters

The following filters on entities are supported for LINQ querying.

System properties

  • Id (long)
  • Identifier (long)
  • Definition
    • id (long)
    • name (string)
  • Created by
    • user id (long)
    • user name (string)
  • Modified by
    • user id (long)
    • user name (string)
  • Created on (DateTime)
  • Modified on (DateTime)

For example, to filter on id:

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.Id == entityId
    select e);

Entity members

  • Property value (also supports filtering on values in specific cultures)
  • Relations (can be filtered on parent id only)

For example, to filter on a property value:

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.Property("Title") == "Logo"
    select e);

Supported comparison operators

System properties

Depending on the system property data type:

  • long: ==, !=
  • string: ==, !=
  • DateTime: ==, !=, >, <, >=, <=

For example, to filter entities modified after the specified date:

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.ModifiedOn > dateTime
    select e);

Properties

Depending on the property data type:

  • string: ==, !=
  • int: ==, !=, >, <, >=, <=
  • long: ==, !=, >, <, >=, <=
  • decimal: ==, !=, >, <, >=, <=
  • bool: ==, !=
  • DateTime: ==, !=, >, <, >=, <=
  • DateTimeOffset: ==, !=, >, <, >=, <=

For example, to filter entities where the title is not "Logo":

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.Property("Title") != "Logo"
    select e);

Additional property filters

The 'in' filter

The In filter allows specifying a collection or comma separated values that, it allows filtering on multiple values instead of a single one.

The In filter is supported on:

  • Entity id
  • Entity identifier
  • Property values
  • Relation values (parent ids only)

Let's find the entities where the title is set to "Logo" or "Poster":

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.Property("Title").In("Logo", "Poster")
    select e);

String filters

String properties support some additional filters:

  • Contains: the value must contain the specified substring.
  • StartsWith: the value must start with the specified substring.
  • EndsWith: the value must end with the specified substring.

These filters are not case sensitive.

The following query can be used to find all entities with a Title property that contains "logo".

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.Property("Title").Contains("logo")
    select e);

Supported orderings

Ordering is supported on:

  • Entity id
  • Entity identifier
  • Definition id
  • Definition name
  • Created by user id
  • Created by user name
  • Modified by user id
  • Modified by user name
  • Created on
  • Modified on
  • Property values

For example, to order assets on creation date, and then by title:

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.DefinitionName == "M.Asset"
    orderby e.CreatedOn ascending
    orderby e.Property("Title") ascending
    select e);

Select options

Currently only load option and configuration operations are support on the select clause.

Load options and configurations

Warning

Since version 3.1, these operations are obsolete. The loading configurations and the query object are separated and should be passed as separate parameters (where applicable).

The following select options are supported:

  • InCultures: loads entities in specified cultures
  • WithProperties: loads specified properties on the entities
  • WithRelations: loads specified relations on the entities
  • LoadConfiguration: loads entities with specified load configuration

Multiple of these select options can be chained. The last operation will always overwrite the previous one where applicable.

In this example, we start from the default query load configuration (loads default culture, all properties and no relations) and we add the AssetTypeToAsset relation. The WithRelations overwrites the relation load option on the load configuration set earlier.

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where [conditions]
    select e.LoadConfiguration(QueryLoadConfiguration.Default)
    .WithRelations(new RelationLoadOption("AssetTypeToAsset")));
Note

For more information, see load configurations.

Result operators

skip (skip the first x entities) and take (return x entities) are also supported when using LINQ:

RequestResponse
var query = Query.CreateQuery(entities =>
    (from e in entities
    where [your conditions]
    select e)
    .Skip(5)
    .Take(5));

This orders the entities by id descending, skips the first 5 entities (the 5 newest entities) and then returns the next 5 entities.

Examples

The following is a list of examples. Most of these examples are very simple and self explanatory. Examples tend to be more complex towards the end.

By id

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.Id == entityId
    select e);

By identifier

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.Identifier == entityIdentifier
    select e);

By definition id

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.DefinitionId == definitionId
    select e);

By definition name

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.DefinitionName == "M.Asset"
    select e);

By created by user id

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.CreatedById == demoUserId
    select e);

By created by user name

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.CreatedByUsername == "M.Demo.User"
    select e);

By modified by user id

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.ModifiedById == demoUserId
    select e);

By modified by user name

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.ModifiedByUsername == "M.Demo.User"
    select e);

By created on

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.CreatedOn == dateTime
    select e);

Also possible between two dates:

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where  e.CreatedOn > minDateTime && e.CreatedOn < maxDateTime
    select e);

By modified on

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.ModifiedOn == dateTime
    select e);

Also possible between two dates:

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where  e.ModifiedOn > minDateTime && e.ModifiedOn < maxDateTime
    select e);

By property value

Finds all entities with a property Title that is set to "Logo".

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.Property("Title") == "Logo"
    select e);

By property value in specific culture

Finds all entities with a property Description that is set to "Hello world!" in the English culture.

RequestResponse
CultureInfo enUs = CultureInfo.GetCultureInfo("en-US");
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.Property("Description", enUs) == "Hello world!"
    select e);

By relation parent value

The parent property can be accessed on the entity object to query on a specified relation. Querying on relations requires the relation name and the parent id to be specified.

RequestResponse
var query = Query.CreateQuery(entities =>
    from e in entities
    where e.Parent("AssetTypeToAsset") == assetTypeId
    select e);

A more advanced query

The following query gets the last 10 assets made by "M.Demo.User", where the title is "Logo".

RequestResponse
var query = Query.CreateQuery(entities =>
    (from e in entities
     where e.DefinitionName == "M.Asset"
     && e.Property("Title") == "Logo"
     && e.CreatedByUsername == "M.Demo.User"
     orderby e.CreatedOn descending
     select e)
    .Take(10));

Do you have some feedback for us?

If you have suggestions for improving this article,