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 |