Collection Expressions (M to SQL Mapping)

[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.]

Collection expressions are expressions that work on collections. They are translated as shown in the following table.

Operation	“M” Example	SQL Example
Subset	A <= B	Does not exist (A except B).
Superset	A >= B	Does not exist (B except A).
Strict Subset	A < B	Does not exist (A except B) and exists (B except A).
Strict Superset	A > B	Does not exist (B except A) and exists (A except B).
Equal	A == B	Does not exist (A except B) and does not exist (B except A).
Not equal	A != B	Exists (A except B) or exists (B except A).
in	A in B	A in B.
Union	A | B	A union B.
Intersection	A & B	A intersect B.
where	A where B	Equivalent to “from value in A where B select value”.
select	A select B	Equivalent to “from value in A select B”.
Choose	A.Choose	Not currently implemented.
Count	A# A.Count	Not currently implemented.
Distinct	A.Distinct	Not currently implemented.

Exceptions

Entity types from different tables: Set comparison (such as subset and equal), in and intersection are not supported when used over entity types that come from different tables.

Entity types with different fields: Union is presently unsupported when used over entity types with different sets of fields (but they may come from different tables).

Translations Unique to Entity Collections

Operation	“M” Example	SQL Example
Projector	A.field	Equivalent to “from value in A select value.field”.
Selector	A.field(B)	Equivalent to “from value in A where value.field = B select value”.
Table Selector	A(B)	Not currently implemented.

Selectors are unsupported when the field is an entity type.

Translations Unique to Logical Collections

Operation	“M” Example	SQL Example
All	A.All	Not currently implemented.
Exists	A.Exists	Not currently implemented.

Translations Unique to Numerical Collections

Operation	“M” Example	SQL Example
Average	A. Average	Not currently implemented.
Maximum	A.Maximum	Not currently implemented.
Minimum	A.Minimum	Not currently implemented.
Sum	A.Sum	Not currently implemented.

Selectors are unsupported when the field is an entity type.