SELECT (Entity SQL)

  • Article

Specifies the elements returned by a query.

SELECT [ ALL | DISTINCT ] [ topSubclause ] aliasedExpr 
      [{ , aliasedExpr }]
or
SELECT VALUE [ ALL | DISTINCT ] [ topSubclause ] expr

Arguments

  • ALL
    Specifies that duplicates can appear in the result set. ALL is the default.
  • DISTINCT
    Specifies that only unique results can appear in the result set.
  • VALUE
    Allows only one item to be specified, and does not add on a row wrapper.

  • topSubclause
    Any valid expression that indicates the number of first results to return from the query, of the form top (expr).

    The LIMIT parameter of the ORDER BY (Entity SQL) operator also lets you select the first n items in the result set.

  • aliasedExpr
    An expression of the form:

    expr as identifier | expr

  • expr
    A literal or expression.

Remarks

The SELECT clause is evaluated after the FROM (Entity SQL), GROUP BY (Entity SQL), and HAVING (Entity SQL) clauses have been evaluated. The SELECT clause can only refer to items currently in-scope (from the FROM clause, or from outer scopes). If a GROUP BY clause has been specified, the SELECT clause is only allowed to reference the aliases for the GROUP BY keys. Referring to the FROM clause items is only permitted in aggregate functions.

The list of one or more query expressions following the SELECT keyword is known as the select list, or more formally as the projection. The most general form of projection is a single query expression. If you select a membermember1from a collection collection1, you will produce a new collection of all themember1values for each object in collection1, as illustrated in the following example.

SELECT collection1.member1 FROM collection1

For example, ifcustomersis a collection of type Customer that has a propertyNamethat is of type string, selectingNamefromcustomerswill yield a collection of strings, as illustrated in the following example.

SELECT customers.Name FROM customers AS c

It is also possible to use JOIN syntax (FULL, INNER, LEFT, OUTER, ON, and RIGHT). ON is required for inner joins and is nto allowed for cross joins.

Row and Value Select Clauses

Entity SQL supports two variants of the SELECT clause. The first variant, row select, is identified by the SELECT keyword, and can be used to specify one or more values that should be projected out. Because a row wrapper is implicitly added around the values returned, the result of the query expression is always a multiset of rows.

Each query expression in a row select must specify an alias. If no alias is specified,Entity SQL attempts to generate an alias by using the alias generation rules.

The other variant of the SELECT clause, value select, is identified by the SELECT VALUE keyword. It allows only one value to be specified, and does not add a row wrapper.

A row select is always expressible in terms of VALUE SELECT, as illustrated in the following example.

SELECT 1 AS a, "abc" AS b, a+34 AS c
SELECT VALUE ROW(1 AS a, "abc" AS b, a+34 AS c)

All and Distinct Modifiers

Both variants of SELECT in Entity SQL allow the specification of an ALL or DISTINCT modifier. If the DISTINCT modifier is specified, duplicates are eliminated from the collection produced by the query expression (up to and including the SELECT clause). If the ALL modifier is specified, no duplicate elimination is performed; ALL is the default.

Differences from Transact-SQL

Unlike Transact-SQL, Entity SQL does not support use of the * argument in the SELECT clause. Instead, Entity SQL allows queries to project out entire records by referencing the collection aliases from the FROM clause, as illustrated in the following example.

SELECT * FROM T1, T2

The previous Transact-SQL query expression is expressed in Entity SQL in the following way.

SELECT a1, a2 FROM T1 AS a1, T2 AS a2

Example

The following Entity SQL query uses the SELECT operator to specify the elements to be returned by a query. The query is based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:

  1. Follow the procedure in How to: Execute a Query that Returns StructuralType Results (EntityClient).

  2. Pass the following query as an argument to the ExecuteStructuralTypeQuery method:

SELECT VALUE product FROM AdventureWorksEntities.Product AS product

The output is shown below:

ProductID: 1
Name: Adjustable Race
ProductNumber: AR-5381
MakeFlag: False
ProductID: 2
Name: Bearing Ball
ProductNumber: BA-8327
MakeFlag: False
ProductID: 3
Name: BB Ball Bearing
ProductNumber: BE-2349
MakeFlag: True
ProductID: 4
Name: Headset Ball Bearings
ProductNumber: BE-2908
MakeFlag: False
ProductID: 316
Name: Blade
ProductNumber: BL-2036
MakeFlag: True
...

See Also

Reference

TOP (Entity SQL)

Concepts

Query Expressions (Entity SQL)
Entity SQL Reference