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 example 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 (123), UInt32 (123U), Int64 (123L), and UInt64 (123UL).
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 conceptual model.
Type Constructors
ROW
ROW 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 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 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) AS OrderID FROM Orders AS o
Output:
| Value |
|---|
1 |
2 |
3 |
... |
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 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 |