A contact search screen

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.RetrieveMultiple.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_SQLSERVER")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_SQLSERVER environment variable to your SQL Server connection string.");

await new RealCase1_SearchContacts().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.RetrieveMultiple.RealCases;

/// <summary>
/// A contact search screen, in three scenarios: a free-text box (Contains, case-insensitive, on two columns with OR);
/// filters the user ticked (an In list); and text with wildcards in it, searched literally.
/// </summary>
public sealed class RealCase1_SearchContacts : ISample
{
    public string Name => "retrieve-multiple-real-1-search-contacts";

    public string Title => "A contact search screen";

    public async Task RunAsync(SampleContext dbContext)
    {
        var contacts = new List<ArxEntity>();

        foreach (var (first, last, title, channel) in new[]
        {
            ("Maria", "Search-R601", "Sales Manager", "E"),
            ("Mario", "Search-R601", "Engineer", "P"),
            ("Anna", "Marino-R601", "100% remote engineer", "E"),
            ("Tom", "Search-R601", "Buyer", "S"),
        })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = last;
            contact["job_title"] = title;
            contact["preferred_channel"] = new ArxSelectionSetValue("ContactChannel", channel[0]);
            contacts.Add(contact);
        }

        await dbContext.Service.CreateMultipleAsync(contacts);

        await Scenario1_AFreeTextBoxAsync(dbContext);

        await Scenario2_TickedFiltersAsync(dbContext);

        await Scenario3_WildcardsAreSearchedLiterallyAsync(dbContext);
    }

    /// <summary>The query every scenario starts from: this sample's contacts, by first name.</summary>
    private static ArxQueryExpression Contacts()
    {
        var query = new ArxQueryExpression("contact");
        query.ColumnSet.AddColumns("first_name", "last_name", "job_title");
        var ours = query.Criteria.AddFilter(ArxLogicalOperator.Or);
        ours.AddCondition("last_name", ArxConditionOperator.EndsWith, "-R601");
        query.AddOrder("first_name", ArxOrderType.Ascending);
        return query;
    }

    /// <summary>1. The user types "mari": first name or last name contains it, in any case.</summary>
    private static async Task Scenario1_AFreeTextBoxAsync(SampleContext dbContext)
    {
        Step("1. A free-text box: first or last name contains \"mari\"");

        var query = Contacts();
        var text = query.Criteria.AddFilter(ArxLogicalOperator.Or);
        text.AddCondition("first_name", ArxConditionOperator.Contains, "mari");
        text.AddCondition("last_name", ArxConditionOperator.Contains, "mari");

        var found = await dbContext.Service.RetrieveMultipleAsync(query);
        Show(string.Join(", ", found.Entities.Select(x => $"{x["first_name"]} {x["last_name"]}")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, last_name
         * FROM contact
         * WHERE last_name LIKE '%-R601' AND (first_name LIKE '%mari%' OR last_name LIKE '%mari%')
         * ORDER BY first_name;
         * -- Expect: 3 rows, Anna Marino-R601, Maria Search-R601, Mario Search-R601.
         * ===========================================================
         */
    }

    /// <summary>2. The user ticked "e-mail" and "phone" as preferred channels: one In list, one parameter a value.</summary>
    private static async Task Scenario2_TickedFiltersAsync(SampleContext dbContext)
    {
        Step("2. Ticked filters: preferred channel e-mail or phone");

        var query = Contacts();
        query.Criteria.AddCondition("preferred_channel", ArxConditionOperator.In, "E", "P");

        var found = await dbContext.Service.RetrieveMultipleAsync(query);
        Show(string.Join(", ", found.Entities.Select(x => x["first_name"])));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name
         * FROM contact
         * WHERE last_name LIKE '%-R601' AND preferred_channel IN ('E', 'P')
         * ORDER BY first_name;
         * -- Expect: 3 rows, Anna, Maria, Mario.
         * ===========================================================
         */
    }

    /// <summary>3. "100%" typed into the box: the % is a character to find, not a wildcard.</summary>
    private static async Task Scenario3_WildcardsAreSearchedLiterallyAsync(SampleContext dbContext)
    {
        Step("3. \"100%\" is searched as text, not as a pattern");

        var query = Contacts();
        query.Criteria.AddCondition("job_title", ArxConditionOperator.Contains, "100%");

        var found = await dbContext.Service.RetrieveMultipleAsync(query);
        Show(string.Join(", ", found.Entities.Select(x => $"{x["first_name"]}: {x["job_title"]}")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, job_title
         * FROM contact
         * WHERE last_name LIKE '%-R601' AND CHARINDEX('100%', job_title) > 0;
         * -- Expect: 1 row, Anna, 100% remote engineer.
         * ===========================================================
         */
    }
}

  > 1. A free-text box: first or last name contains "mari"
      Anna Marino-R601, Maria Search-R601, Mario Search-R601
  > 2. Ticked filters: preferred channel e-mail or phone
      Anna, Maria, Mario
  > 3. "100%" is searched as text, not as a pattern
      Anna: 100% remote engineer