Share via

Entity SQL - Quick Reference

  • Article

 

Literals

“abc”

Value

abc

‘abc’

Value

abc

1

Value

1

{2}

Value

2

{3, 4, 5}

Value

3

4

5

Rows

ROW(1 AS i, ‘abc’ AS s)

i

s

1

abc

SELECT ROW(1 AS i, ‘abc’ AS s) AS Row

FROM {11, 12, 13};

Row

i

s

1

abc

i

s

1

abc

i

s

1

abc

{ROW(1 AS i, ‘abc’ AS s)}

UNION ALL

{ROW(2, ‘xyz’)};

i

s

1

abc

2

xyz

Entities

SELECT c

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

c

CultureID

Name

ModifiedDate

en

English

6/1/1998 12:00:00 AM

CultureID

Name

ModifiedDate

es

Spanish

6/1/1998 12:00:00 AM

CultureID

Name

ModifiedDate

fr

French

6/1/1998 12:00:00 AM

SELECT VALUE c

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

CultureID

Name

ModifiedDate

en

English

6/1/1998 12:00:00 AM

es

Spanish

6/1/1998 12:00:00 AM

fr

French

6/1/1998 12:00:00 AM

AdventureWorks.Department(

        CAST(100 AS Edm.Int16), 'Dyn. Dept',

       'Dyn. Group', Edm.GetDate());

DepartmentID

Name

GroupName

ModifiedDate

100

Dyn. Dept

Dyn. Group

6/20/2007 3:52:47 PM

SELECT VALUE AdventureWorks.Department(

        rs.ID, rs.Name, rs.[Group], rs.[Date])

FROM

       ({ROW(CAST(101 AS Edm.Int16) AS ID,

                  'Dyn. Dept 1' AS Name,

                   'Dyn. Group' AS [Group],

                  Edm.GetDate() AS [Date])}

         UNION ALL

         {ROW(CAST(102 AS Edm.Int16),

                   'Dyn. Dept 2', 'Dyn. Group',

                   Edm.GetDate())}

         UNION ALL

         {ROW(CAST(103 AS Edm.Int16),

                   'Dyn. Dept 3', 'Dyn. Group',

                   Edm.GetDate())}

) AS rs;

DepartmentID

Name

GroupName

ModifiedDate

101

Dyn. Dept 1

Dyn. Group

6/20/2007 3:59:43 PM

102

Dyn. Dept 2

Dyn. Group

6/20/2007 3:59:43 PM

103

Dyn. Dept 3

Dyn. Group

6/20/2007 3:59:43 PM

Functions

SELECT c.ContactID,

       -- Canonical:

       Length(c.FirstName) AS FirstNameLength,

       -- Canonical:

       Edm.Length(c.LastName) AS LastNameLength,

       -- Provider-specific:

       SqlServer.LEN(c.EmailAddress) AS EmailAddressLength

FROM AdventureWorks.Contact AS c

WHERE c.ContactID BETWEEN 10 AND 12;

ContactID

FirstNameLength

LastNameLength

EmailAddressLength

10

6

5

27

11

6

8

27

12

5

7

26

Keys/References

SELECT VALUE KEY(c)

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

CultureID

en

es

fr

SELECT VALUE REF(c)

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

C1

CultureID

0

en

0

es

0

fr

SELECT VALUE DEREF(REF(c))

FROM AdventureWorks.Culture AS c

WHERE c.CultureID IN {'en', 'es', 'fr'};

CultureID

Name

ModifiedDate

en

English

6/1/1998 12:00:00 AM

es

Spanish

6/1/1998 12:00:00 AM

fr

French

6/1/1998 12:00:00 AM

Navigation + Nesting

SELECT e.EmployeeID,

             -- to 1:

      e.Contact.FirstName, e.Contact.LastName,

             -- to many:

      (SELECT eph.RateChangeDate, eph.Rate

       FROM e.EmployeePayHistory AS eph) AS

                                                            PayHistory

FROM AdventureWorks.Employee AS e

WHERE e.EmployeeID IN {4, 6};

EmployeeID

FirstName

LastName

EmployeePayHistory

4

Rob

Walters

RateChangeDate

Rate

1/5/1998 12:00:00 AM

8.6200

7/1/2000 12:00:00 AM

23.7200

1/15/2002 12:00:00 AM

29.8462

6

David

Bradley

RateChangeDate

Rate

1/20/1998 12:00:00 AM

24.0000

8/16/1999 12:00:00 AM

28.7500

6/1/2002 12:00:00 AM

37.5000

Paging/TOP

SELECT TOP(3) c.ContactID, c.FirstName, c.LastName

FROM AdventureWorks.Contact AS c

WHERE c.ContactID >= 10;

ContactID

FirstName

LastName

10

Ronald

Adina

11

Samuel

Agcaoili

12

James

Aguilar

SELECT c.ContactID, c.FirstName, c.LastName

FROM AdventureWorks.Contact AS c

ORDER BY c.ContactID

SKIP 9 LIMIT 3;

ContactID

FirstName

LastName

10

Ronald

Adina

11

Samuel

Agcaoili

12

James

Aguilar

Grouping

SELECT c.FirstName, c.LastName, epc.PayChanges

FROM

         (SELECT eph.EmployeeID,

                      Count(eph.EmployeeID) AS PayChanges

          FROM AdventureWorks.EmployeePayHistory AS eph

          GROUP BY eph.EmployeeID

          HAVING Count(eph.EmployeeID)

                        >= 3) AS epc

         JOIN AdventureWorks.Contact AS c

          ON epc.EmployeeID = c.ContactID;

FirstName

LastName

PayChanges

Humberto

Acevedo

3

Frances

Adams

3

Sean

Jacobson

3

Adam

Barr

3

Mary

Billstrom

3

Cornelius

Brandon

3

Shirley

Bruner

3

Megan

Burke

3

Stephen

Burton

3

Jovita

Carmody

3

Matthew

Cavallari

3

Charles

Christensen

3

Bart

Duncan

3

Comments