Selectors

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

The Microsoft code name “M” modeling language generates indexers on collections of entities called selectors. A selector expression extracts those members of a collection in which a specified field matches a specified value. A selector expression generates T-SQL code for a Create View statement that returns rows where a column matches the specified value.

Selection can also be done with linq query expressions; however, selection notation is more compact and easy to use.

Using Selectors

The compiler generates indexers for each field in the entities that make up a collection. A selector expression can evaluate to a collection that contains a single member of the original collection ("row" in the corresponding T-SQL view), multiple members, or no members.

The following examples in this topic use the following type and extent. You can copy this code into Intellipad and save it as SelectorQueries.m.

module Queries
{
    type Person 
    {
        Id : Integer64 => AutoNumber();
        First : Text;
        Last : Text;
        Age : Integer32;
    } where identity Id;

    People 
    {
      { First => "Mary", Last => "Smith", Age => 24 },
      { First => "John", Last => "Doe", Age => 32 },
      { First => "Dave", Last => "Smith", Age => 32 },
      { First => "Dave", Last => "Jones", Age => 39 },
      { First => "Bob", Last => "Martin", Age => 53 },
    }
}

Selecting Indexed Rows

Consider the following expression.

People.First("Mary") 

It evaluates to the following.

{{ First => "Mary", Last => "Smith", Age => 24 }}

The expression, when compiled, generates the following T-SQL code. You can see this code in Intellipad if you click the M Mode menu entry and select T_SQL Preview.

create view [Queries].[selectorName]
(
    [Id],
    [First],
    [Last],
    [Age]
)
as
    select [$temp].[Id] as [Id],
        [$temp].[First] as [First],
        [$temp].[Last] as [Last],
        [$temp].[Age] as [Age]
    from [Queries].[People] as [$temp]
    where [$temp].[First] = N'Mary';

In this code, Queries is the name of the module that contains the expression. All code generated from selectors is similar: the main differences are the column name and value in the where clause.

Here is an example that evaluates to an empty collection because the selector condition is not met.

People.Name("Bill") 

Selector expressions can evaluate to expressions that contain more than one member, as shown by the following code.

People.First("Dave")

The result is the following.

{
      { First => "Dave", Last => "Smith", Age => 32 },
      { First => "Dave", Last => "Jones", Age => 39 }
}

Selectors on Identity Fields

Selector expressions have a special format when evaluated on an identity field, as seen in the following code.

People(1)

Because the People extent has an auto indexer for its identity, if you knew that indexing started at 0, then the preceding code would return the following.

{
         { First => "John", Last => "Doe", Age => 32 },
}

Suppose the identity constraint was defined over two fields, as in the following code. Note that in a real application you would never do this, because first and last name are almost always not unique.

    type Person 
    {
        First : Text;
        Last : Text;
        Age : Integer32;
    } where identity (First, Last);

Then you could write the following selector expression.

People("John", "Doe")

This would evaluate to the following.

{
         { First => "John", Last => "Doe", Age => 32 },
}