Projectors

[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 called projectors on collections of entities. A projector expression returns the values of one field from each entity in a collection. A projector expression generates T-SQL code for a Create View statement that returns rows that consist of the values of a specified column.

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

Using Projectors

Projectors return the value of a specified field from each entity in a collection. A projector expression can evaluate to a collection that contains values that may or may not be distinct.

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 },
    }
}

Consider the following expression.

People.First 

It evaluates to the following.

{ "Mary", "John", "Dave", "Dave", "Bob" }

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].[projectorName]
(
    [Item]
)
as
    select [$temp].[First] as [Item]
    from [Queries].[People] as [$temp];

In this code, Queries is the name of the module that contains the expression. All code generated from projectors is similar: the main difference is the column name being selected.

Note that the evaluation of the expression resulted in duplicate entries. To avoid this, use the Distinct keyword, as shown in the following code.

People.First.Distinct 

The following members are defined on all collections: Distinct, Choose, and Count. If one of these members is used as a field name in the entities in a collection, then it becomes impossible to do a projection on that field name because of the name conflict.

Consider the following type and extent.

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

    People : {Person*}
    {
      { First => "Mary", Last => "Smith", Age => 24, Distinct => "yes"},
      { First => "John", Last => "Doe", Age => 32, Distinct => "no" },
      { First => "Dave", Last => "Smith", Age => 32, Distinct => "maybe" },
      { First => "Dave", Last => "Jones", Age => 39, Distinct => "unknown" },
      { First => "Bob", Last => "Martin", Age => 53, Distinct => "" },
    }
}

The following code shows this for the Distinct keyword.

People.Distinct

This code generates the following T-SQL code, which reflects applying the Distinct operator to the People collection, instead of applying the projection of the Distinct field name.

create view [Queries].[projectorDistinct]
(
    [Id],
    [First],
    [Last],
    [Age],
    [Distinct]
)
as
    select distinct [t0].[Id] as [Id],
        [t0].[First] as [First],
        [t0].[Last] as [Last],
        [t0].[Age] as [Age],
        [t0].[Distinct] as [Distinct]
    from 
    (
        select [Id] as [Id],
            [First] as [First],
            [Last] as [Last],
            [Age] as [Age],
            [Distinct] as [Distinct]
        from [Queries].[People]
    ) as [t0];