Entity SQL - Quick Reference

SELECT c

FROM AdventureWorks.Culture AS c

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

AdventureWorks.Department(

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

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

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;

SELECT VALUE KEY(c)

FROM AdventureWorks.Culture AS c

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

SELECT VALUE DEREF(REF(c))

FROM AdventureWorks.Culture AS c

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

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};

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

FROM AdventureWorks.Contact AS c

WHERE c.ContactID >= 10;

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;