Version: 8.3

QueryExtensions class

Level: advanced

note

The functionality is relevant to Classic UI.

The Terrasoft.Configuration.QueryExtensions utility class provides extending methods for the Terrasoft.Core.DB.Select class to build compact queries.

Each class method includes the sourceColumn parameter that can have the object types listed in the table below.

Type	Description
System.String	A column name in the `TableAlias.ColumnName AS ColumnAlias` format. Both `TableAlias` and `ColumnAlias` are optional. You can also pass "*" to select all columns.
Terrasoft.Core.DB.QueryColumnExpression	A query column expression that is added to the query without modification.
Terrasoft.Core.DB.IQueryColumnExpressionConvertible	An object that can be automatically converted to a `QueryColumnExpression`.
Terrasoft.Core.DB.Select	A subquery whose result can be used as a column in the main query.

If you pass an unsupported object type, Creatio throws an exception.

Methods

static Select Cols(
    this Select select,
    params object[] sourceColumns
)

Add specified columns or subexpressions to the query.

Parameters

select	The current `Select` instance to extend using additional columns.
sourceColumns	One or more columns or expressions to add to the query.

Recommended way
Not recommended way

new Select(userConnection)
    .Cols(
        "L.Id",
        "L.QualifyStatusId",
        "L.LeadTypeId",
        "L.LeadSourceId",
        "L.LeadMediumId AS LeadChannel",
        "L.BusinessPhone AS KnownBusinessPhone")
    .From("Lead").As("L");

new Select(userConnection)
    .Column("L", "Id")
    .Column("L", "QualifyStatusId")
    .Column("L", "LeadTypeId")
    .Column("L", "LeadSourceId")
    .Column("L", "LeadMediumId")
        .As("LeadChannel")
    .Column("L", "BusinessPhone")
        .As("KnownBusinessPhone")
    .From("Lead").As("L");

static Select Count(
    this Select select,
    object sourceColumn
)

Add an aggregation column to calculate the number of non-empty values in the query.

Parameters

select	The current `Select` instance to extend using the aggregation column.
sourceColumn	The column or expression for which non-empty values are calculated.

Recommended way
Not recommended way

var activitiesCount = new Select(userConnection)
    /* Specify the column name if needed. */
    .Count("*")
    .From("Activity")

var activitiesCount = new Select(userConnection)
    .Column(
        Func.Count(
            Column.Asterisk()
        )
    )
    .From("Activity")

static Select Coalesce(
    this Select select,
    params object[] sourceColumns
)

Add a column that determines the first non-NULL value in the query.

Parameters

select	The current `Select` instance to extend using a `COALESCE` column.
sourceColumns	One or more columns or expressions to evaluate in order.

Recommended way
Not recommended way

new Select(userConnection)
    .Cols("L.Id")
    .Coalesce("L.CountryId", "C.CountryId", "A.CountryId")
        .As("CountryId")
    .From("Lead")
        .As("L")
    .LeftOuterJoin("Contact")
        .As("C")
        .On("L", "QualifiedContactId").IsEqual("C", "Id")
    .LeftOuterJoin("Account")
        .As("A")
        .On("L", "QualifiedAccountId").IsEqual("A", "Id");

new Select(userConnection)
    .Cols("L.Id")
    .Column(
        Func.Coalesce(
            Column.SourceColumn("L", "CountryId"),
            Column.SourceColumn("L", "CountryId"),
            Column.SourceColumn("L", "CountryId")
        )
    ).As("CountryId")
    .From("Lead").As("L")
    .LeftOuterJoin("Contact").As("C")
        .On("L", "QualifiedContactId").IsEqual("C", "Id")
    .LeftOuterJoin("Account").As("A")
        .On("L", "QualifiedAccountId").IsEqual("A", "Id");

static Select DateDiff(
    this Select select,
    DateDiffQueryFunctionInterval interval,
    object startDateExpression,
    object endDateExpression
)

Add a column that specifies the date difference in the query.

Parameters

select	The current `Select` instance to extend using a `DATEDIFF` column.
interval	The unit of time to measure the difference, e. g., day, month, year.
startDateExpression	The column or expression that represents the starting date of the calculation.
endDateExpression	The column or expression that represents the ending date of the calculation.

Recommended way
Not recommended way

var day = DateDiffQueryFunctionInterval.Day;

new Select(userConnection)
    .Cols("L.Id")
    .DateDiff(
        day,
        "L.CreatedOn",
        Func.CurrentDateTime()
    ).As("LeadAge")
    .From("Lead").As("L");

new Select(_userConnection)
    .Cols("Id")
    .Column(
        Func.DateDiff(
            DateDiffQueryFunctionInterval.Day,
            Column.SourceColumn("L", "CreatedOn"),
            Func.CurrentDateTime()
        )
    ).As("LeadAge")
    .From("Lead").As("L");

static Select IsNull(
    this Select select,
    object checkExpression,
    object replacementValue
)

Add a column that replaces a NULL value with a replacement expression.

Parameters

select	The current `Select` instance to extend using an `ISNULL` column.
checkExpression	The column or expression to evaluate.
replacementValue	The value or expression used as a replacement when `checkExpression` is "NULL."

Recommended way
Not recommended way

new Select(userConnection)
    .Cols("L.Id")
    .IsNull(
        "L.CreatedOn",
        "L.ModifiedOn"
    )
    .From("Lead").As("L");

new Select(userConnection)
    .Cols("Id")
    .Column(
        Func.IsNull(
            Column.SourceColumn("L", "CreatedOn"),
            Column.SourceColumn("L", "ModifiedOn")
        )
    )
    .From("Lead").As("L");

Methods​

Methods