Entity SQL Quick Reference

This topic provides a quick reference to Entity SQL queries. The queries in this topic are based on the AdventureWorks Sales model.

Literals

String

There are Unicode and non-Unicode character string literals. Unicode strings are prepended with N. For example, N'hello'.

The following is an examle of a Non-Unicode string literal:

'hello'
--same as
"hello"

Output:

Value
hello

DateTime

In DateTime literals, both date and time parts are mandatory. There are no default values.

Example:

DATETIME '2006-12-25 01:01:00.000' 
--same as
DATETIME '2006-12-25 01:01'

Output:

Value
12/25/2006 1:01:00 AM

Integer

Integer literals can be of type Int32 or Int64.

Example:

--a collection of integers
{1, 2, 3}

Output:

Value
1
2
3

Other

Other literals supported by Entity SQL are Guid, Binary, Float/Double, Decimal, and null. Null literals in Entity SQL are considered to be compatible with every other type in the Entity Data Model (EDM).

Type Constructors

ROW

ROW (Entity SQL) constructs an anonymous, structurally-typed (record) value as in: ROW(1 AS myNumber, ‘Name’ AS myName).

Example:

SELECT VALUE row (product.ProductID as ProductID, product.Name 
    as ProductName) FROM AdventureWorksEntities.Product AS product

Output:

ProductID	Name
1	Adjustable Race
879	All-Purpose Bike Stand
712	AWC Logo Cap
...	...

MULTISET

MULTISET (Entity SQL) constructs collections, such as:

MULTISET(1,2,2,3) --same as-{1,2,2,3}.

Example:

SELECT VALUE product FROM AdventureWorksEntities.Product AS product WHERE product.ListPrice IN MultiSet (125, 300)

Output:

ProductID	Name	ProductNumber	…
842	Touring-Panniers, Large	PA-T100	…

Object

Named Type Constructor (Entity SQL) constructs (named) user-defined objects, such as person("abc", 12).

Example:

SELECT VALUE AdventureWorksModel.SalesOrderDetail (o.SalesOrderDetailID, o.CarrierTrackingNumber, o.OrderQty, 
o.ProductID, o.SpecialOfferID, o.UnitPrice, o.UnitPriceDiscount, 
o.rowguid, o.ModifiedDate) FROM AdventureWorksEntities.SalesOrderDetail 
AS o

Output:

SalesOrderDetailID	CarrierTrackingNumber	OrderQty	ProductID
1	4911-403C-98	1	776
2	4911-403C-98	3	777
...	...	...	...

References

REF

REF creates a reference to an entity type instance. For example, the following query returns references to each Order entity in the Orders entity set: select ref(o) from LOB.Orders as o. The following example uses the property extraction operator (.) to access a property of an entity. When the property extraction operator is used, the reference is automatically dereferenced.

Example:

SELECT VALUE REF(p).Name FROM 
    AdventureWorksEntities.Product as p

Output:

Value
Adjustable Race
All-Purpose Bike Stand
AWC Logo Cap
...

DEREF

DEREF dereferences a reference value and produces the result of that dereference. For example, the following query produces the Order entities for each Order in the Orders entity set: select deref(o2.r) from (select ref(o) as r from LOB.Orders as o) as o2.

Example:

SELECT VALUE DEREF(REF(p)).Name FROM 
    AdventureWorksEntities.Product as p

Output:

Value
Adjustable Race
All-Purpose Bike Stand
AWC Logo Cap
...

CREATEREF AND KEY

CREATEREF creates a reference passing a key. KEY extracts the key portion of an expression with type reference.

Example:

SELECT VALUE Key(CreateRef(AdventureWorksEntities.Product, row(p.ProductID))) 
    FROM AdventureWorksEntities.Product as p

Output:

ProductID
980
365
771
...

Functions

Canonical

The namespace for canonical functions is Edm, as in Edm.Length("string"). You do not have to specify the namespace unless another namespace is imported that contains a function with the same name as a canonical function. If two namespaces have the same function, the user should specific the full name.

Example:

SELECT Length(c. FirstName) As NameLen FROM 
    AdventureWorksEntities.Contact AS c 
    WHERE c.ContactID BETWEEN 10 AND 12

Output:

NameLen
6
6
5

Microsoft Provider-Specific

Microsoft provider-specific functions are in the SqlServer namespace.

Example:

SELECT SqlServer.LEN(c.EmailAddress) As EmailLen FROM 
    AdventureWorksEntities.Contact AS c WHERE 
    c.ContactID BETWEEN 10 AND 12

Output:

EmailLen
27
27
26

Namespaces

USING specifies namespaces used in a query expression.

Example:

using SqlServer; LOWER('AA');

Output:

Value
aa

Paging

Paging can be expressed by declaring a SKIP and LIMIT sub-clauses to the ORDER BY (Entity SQL) clause.

Example:

SELECT c.ContactID as ID, c.LastName as Name FROM 
    AdventureWorks.Contact AS c ORDER BY c.ContactID SKIP 9 LIMIT 3;

Output:

ID	Name
10	Adina
11	Agcaoili
12	Aguilar

Grouping

GROUPING BY specifies groups into which objects returned by a query (SELECT) expression are to be placed.

Example:

SELECT VALUE name FROM AdventureWorksEntities.Product as P 
    GROUP BY P.Name HAVING MAX(P.ListPrice) > 5

Output:

name
LL Mountain Seat Assembly
ML Mountain Seat Assembly
HL Mountain Seat Assembly
...

Navigation

The relationship navigation operator allows you to navigate over the relationship from one entity (from end) to another (to end). NAVIGATE takes the relationship type qualified as <namespace>.<relationship type name>. Navigate returns Ref<T> if the cardinality of the to end is 1. If the cardinality of the to end is n, the Collection<Ref<T>> will be returned.

Example:

SELECT a.AddressID, (SELECT VALUE DEREF(v) FROM 
    NAVIGATE(a, AdventureWorksModel.FK_SalesOrderHeader_Address_BillToAddressID) AS v) 
    FROM AdventureWorksEntities.Address AS a

Output:

AddressID
1
2
3
...

SELECT VALUE AND SELECT

SELECT VALUE

Entity SQL provides the SELECT VALUE clause to skip the implicit row construction. Only one item can be specified in a SELECT VALUE clause. When such a clause is used, no row wrapper is constructed around the items in the SELECT clause, and a collection of the desired shape can be produced, for example: SELECT VALUE a.

Example:

SELECT VALUE p.Name FROM AdventureWorksEntities.Product as p

Output:

Name
Adjustable Race
All-Purpose Bike Stand
AWC Logo Cap
...

SELECT

Entity SQL also provides the row constructor to construct arbitrary rows. SELECT takes one or more elements in the projection and results in a data record with fields, for example: SELECT a, b, c.

Example:

SELECT p.Name, p.ProductID FROM AdventureWorksEntities.Product as p Output:

Name	ProductID
Adjustable Race	1
All-Purpose Bike Stand	879
AWC Logo Cap	712
...	...

CASE EXPRESSION

The case expression evaluates a set of Boolean expressions to determine the result.

Example:

CASE WHEN AVG({25,12,11}) < 100 THEN TRUE ELSE FALSE END

Output:

Value
TRUE

See Also

Concepts

Entity SQL Reference
Entity SQL Overview