EntitySQL

Article
05/30/2007

Earlier posts (Jan 30 & Feb 12) introduced the Entity Data Model (EDM). This model is heavily influenced by the entity/relationship idiom, incorporates entity inheritance, and may be mapped to both relational and programming language concepts.

The Entity Framework enables developers to reason about and write queries in terms of the EDM model rather than the logical schema of tables, joins, foreign keys, and so on. Many enterprise systems have multiple applications/databases with varying degrees of normalization, different schema styles depending on the group that developed it, and different naming conventions for tables and columns. Furthermore, in complex systems the entities of interest may be scattered across multiple rows in multiple tables, and changes to the logical schema can be painful to track within applications. By adding an additional level of abstraction, the EDM insulates the developer from the low level details of the logical model, and frees them to focus on the essential aspects of the application/problem at hand.

SQL is the time proven query language for data access. However, since EDM introduces an enhanced data model which builds on entities, rich types and relationships, we need a query language that enables programmers to reason, express and write queries in terms of EDM abstractions. EntitySQL was designed to address this need. Among the many requirements, it is worth to call out some driving aspects of the language design:

a) Types: Support EDM types in a clean and expressive way;

b) SQL based: Provide a natural transition for SQL developers. While EntitySQL was heavily influenced by SQL, EntitySQL is not pure SQL. We will call out some differences in this posting. It is worth mentioning that EntitySQL does not prevent one from writing classic SQL queries as we will see a sample below on how to navigate relationships using classic joins/foreign-keys. We will also show how EntitySQL supports relationships to simplify this common task.

c) Composable and orthogonal: In some SQL implementations, certain expressions can only be placed in specific constructs. In contrast, EntitySQL expressions can be placed virtually anywhere. For instance, sub-queries are treated uniformly regardless where they are placed. A sub-query in the SELECT clause is treated in the same way as in a FROM clause, as opposed to SQL in which sub-queries in the SELECT clause must evaluate to scalars and as collection in a FROM clause. LIKE is another example. It can be used anywhere a Boolean condition is expected, even as a top level query. @myString LIKE @myPattern is a valid EntitySQL query that will be evaluated at the database and returns True, False or Null.

d) First class collections: EntitySets are treated as collections of a given entity type, therefore NorthwindContainer.Products is a valid query that returns the collection of all Products. {1,2,3} is a collection of integers constructed “inline”. Tables in the SQL sense are just collections of a given entity type. Collections can be created, nested and projected as any other EDM type.

e) Provider neutral: An interesting implication of writing queries against a conceptual model is that queries and applications can be virtually provider agnostic. When other provider writers make their database of choice available through the ADO.Net Entity Framework, queries written in EntitySQL may be reused across different store providers. The EntitySQL language exposes the same set of constructs regardless of the specific provider implementation.

Samples

In the sample code that follows, the Northwind sample database will be used. After generating a default EDM mapping the schema should look as illustrated in the diagram bellow:

You can download Northwind sample database here. If you have SQLExpress installed, you can use the following connection string.

string myConnectionString = "Metadata=.\\;provider=System.Data.SqlClient; provider connection string=\"server=.\\SqlExpress;Integrated Security=True; database=Northwind;\";";

You can use the following code template for the samples in this posting. This one is using EntityCommand interface:

using (EntityConnection eCon = new EntityConnection(myConnectionString)) {

eCon.Open();

string myQuery = "1";

using (EntityCommand cmd = new EntityCommand(myQuery, eCon)) {

using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) {

while (rdr.Read()) {

Console.WriteLine(rdr.GetValue(0));

}

And using ObjectQuery<T> interface:

using (ObjectContext oc = new ObjectContext(myConnectionString, "NorthwindContainer")) {

oc.Connection.Open();

string myQuery = "NorthwindContainer.Products";

foreach (Product p in new ObjectQuery<Product>(myQuery, oc)) {

Console.WriteLine(p.ProductName);

}

Types

EDM defines a number of types. There are three families of types: primitive types such as EDM.Int32, EDM.String; nominal types that are defined in the schema such as Entity types, Relationship types; and transient types that are anonymous types such as collection, row and ref.

A row instance can be constructed by using the ROW keyword as in:

ROW(1 AS myNumber, ‘foo’ AS myName)

using (EntityConnection eCon = new EntityConnection(myConnectionString)) {

eCon.Open();

string myQuery = "ROW(1as MyNumber, 'foo' as MyName)";

using (EntityCommand cmd = new EntityCommand(myQuery, eCon)) {

using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) {

while (rdr.Read()) {

Console.WriteLine(rdr["MyNumber"]);

Console.WriteLine(rdr["MyName"]);

}

Collections can be created by the using MULTISET() or by using the shorter variant {} as in:

MULTISET(1,2,2,3)

{1,2,2,3} -- equivalent to above

As we mentioned before, collections are first class constructs in EntitySQL and can be used anywhere an expression of collection type is allowed. The term collection is used many times in this posting and it is worth making explicit what we mean by it. Collections in ADO .Net Entity Framework is a synonym for multiset and bag.

-- returns the four integers

SELECT number FROM {1,2,2,3} as number

-- returns 4

MAX({0,1,2,4,4,3,0})

-- returns true if myParam value is in the range of the other two parameters @n1 and @n2

@myParam BETWEEN MIN({@n1, @n2}) AND MAX({@n1,@n2})

-- returns true if parameter p1 value is the set of allowed values

@p1 IN {10, 100, 1000, 10000}

-- returns a collection of ROWS

{ROW(1 as n, ‘FOO’ AS Name), ROW(2 as n, ‘BAR’ AS Name)}

A REF is a reference to a persisted entity. Note that we said persisted entity. This is an important distinction since ‘transient’ entities have NULL references (DBNull). For instance:

REF(Microsoft.CDP.Samples.Northwind.Category(1,'foo','bar'))

will result in DBNull since the Category instance is not ‘bound’ to an entityset. On the other hand,

-- creates a ref from the entity instance using REF()

SELECT ref(c) FROM NorthwindContainer.Categories as c

-- creates a ref using CREATEREF() passing a key

CREATEREF(NorthwindContainer.Categories, ROW(1))

will create a reference to the Category instance of key value 1. At this point in time you might be asking why and where we use references. One of the benefits of references is that it can be thought as a ‘lightweight’ entity in which we don’t need to spend resources in creating and maintaining the full entity state/values until it is really necessary. Once you have a ref to an entity, you can dereference it by using DEREF expression or by just invoking a property of the entity.

CREATEREF(NorthwindContainer.Categories, ROW(1)).CategoryName

will return ‘Beverages’. You could have used a parameter instead of the hardcoded ‘1’ value to get an entity from the entity set as in:

CREATEREF(NorthwindContainer.Categories, ROW(@myProductID)).CategoryName

If you want the whole entity:

DEREF(CREATEREF(NorthwindContainer.Categories, ROW(@myProductID)))

Here is a code snippet you can use to get an entity instance by using the entity key as a parameter:

using (EntityConnection eCon = new EntityConnection(myConnectionString)) {

eCon.Open();

string myQuery = "DEREF(CREATEREF(NorthwindContainer.Categories, ROW(@myProductID)))";

using (EntityCommand cmd = new EntityCommand(myQuery, eCon)) {

cmd.Parameters.Add(new EntityParameter("myProductID", DbType.Int32));

cmd.Parameters["myProductID"].Value = 1;

using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) {

while (rdr.Read()) {

Console.WriteLine(rdr["CategoryID"]);

Console.WriteLine(rdr["CategoryName"]);

Console.WriteLine(rdr["CategoryDescription"]);

}

Needless to say, Entities are at the core of EDM. An Entity set is what the name says – it is the analog to a table. An Entity Container is the analog to a database. EDM modeling will be covered in detail in future posts. In order to retrieve all entities from an entity set you can write something like:

using (ObjectContext oc = new ObjectContext(myConnectionString, "NorthwindContainer")) {

oc.Connection.Open();

string myQuery = "Products";

foreach (Product p in new ObjectQuery<Product>(myQuery, oc)) {

Console.WriteLine(p.ProductName);

Console.WriteLine(p.UnitPrice);

}

Using EntityCommand:

using (EntityConnection eCon = new EntityConnection(myConnectionString)) {

eCon.Open();

string myQuery = "NorthwindContainer.Products";

using (EntityCommand cmd = new EntityCommand(myQuery, eCon)) {

using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) {

while (rdr.Read()) {

Console.WriteLine(rdr["ProductName"]);

}

Expressions

As mentioned in the introduction, virtually everything in EntitySQL is an expression and they can be composed provided the resulting expression is semantically valid. For instance, all of the following queries below are valid EntitySQL queries:

1 * @p % (k + @q)

MAX({3.141516,null + 3m, CASE WHEN @p BETWEEN -10 AND 10 THEN 100 ELSE -100 END})

CASE WHEN AVG({@p1,@p2,@p3)) < 100 THEN TRUE ELSE FALSE END

@p LIKE ‘P%’

SUBSTRING(@p, LENGTH(@p),1)

ANYELEMENT(SET({3m,3m, 3.1415})) * LENGTH(NEWGUID()) + LENGTH('abc') + LENGTH(SUBSTRING(123,1,1))

All these expressions can be also composed with query expressions that we will discuss in the next section.

Query Expressions

Query expression is the traditional SFWGHO (SELECT-FROM-WHERE-GROUPBY-HAVING-ORDERBY). EntitySQL query expressions were derived from SQL with few differences but the overall structure is similar. One of the first things you will notice is that EntitySQL does not have ‘SELECT *’ – SELECT expressions must be declared explicitly as we will see bellow. We will discuss the reasons for it in future postings.

-- returns the collection of all product entities from NorthwindContainer.Products entity set

SELECT p

FROM NorthwindContainer.Products as p

SELECT p.ProductName, p.UnitPrice * @Discount AS DiscountedPrice

FROM NorthwindContainer.Products as p

ORDER BY p.ProductName Desc

SELECT MIN(p.UnitPrice), AVG(p.UnitPrice), MAX(p.UnitPrice)

FROM NorthwindContainer.Products as p

Another thing to keep in mind is that in EntitySQL, it is recommended that you declare aliases explicitly by using AS keyword ( … AS myAlias), even though EntitySQL will auto generate aliases for you in some cases if you don’t. Declaring aliases explicitly is also a good practice that can avoid ambiguity and confusion. We will discuss aliasing and scoping rules in future postings.

SELECT VALUE and SELECT ‘ROW’

In SQL, the SELECT statement always returns a collection of rows, even if you are projecting a single value as in:

SELECT p.ProductName as foo

FROM NorthwindContainer.Products as p

The result of this query is a collection of rows (collection of row types) with a column named foo. EntitySQL follows this convention. To read the results you will need to write something like:

using (ObjectContext oc = new ObjectContext(myConnectionString, "NorthwindContainer")) {

oc.Connection.Open();

string myQuery = "SELECT p.ProductName as foo FROM Products as p”;

foreach (DbDataRecord rec in new ObjectQuery<DbDataRecord>(myQuery, oc)) {

Console.WriteLine(rec[“foo”]);

}

But sometimes, you may just want to deal with a single VALUE directly, without having to deal with column names.

In order to project singleton values instead of rows, you can use EntitySQL’s “SELECT VALUE” construct, as in:

-- NOTE the VALUE keyword

SELECT VALUE p.ProductName

FROM NorthwindContainer.Products as p

The result of this query is now a collection of strings not a row with one column of string type. Note that there is no need to specify an alias – in fact, specifying an alias will throw a compilation error. To read the results you will need to write something like:

using (ObjectContext oc = new ObjectContext(myConnectionString, "NorthwindContainer")) {

oc.Connection.Open();

string myQuery = "SELECT VALUE c.CategoryName FROM Categories as c";

foreach (string s in new ObjectQuery<string>(myQuery, oc)) {

Console.WriteLine(s);

}

If you look closely to the sample code above, you will notice that we now iterate over strings (foreach(string … ). It doesn’t need to be primitive types only. Entities are also allowed, as are rows, nested collections etc.:

using (ObjectContext oc = new ObjectContext(myConnectionString, "NorthwindContainer")) {

oc.Connection.Open();

string myQuery = "SELECT VALUE p FROM Products as p";

foreach (Product s in new ObjectQuery<Product>(myQuery, oc)) {

Console.WriteLine(s.ProductName);

}

Polymorphic queries are very powerful and interesting. An entity set can have entities of different types belonging to the same entity type hierarchy. In the sample schema used here, you will find the following definition (we will not cover mapping in this posting, for detailed information on mappings see Dan Dosen’s Mapping 101series):

</EntityType>

OFTYPE, IS OF and TREAT

EntitySQL introduces new operators to make polymorphic queries easier. For instance, OFTYPE( entitySet, nominalTypeInstance ) returns a new collection containing elements from entitySet whose type name is either the same or derived from nominalTypeInstance.

OFTYPE(Employees, MySchema.Contractor)

This query will return a collection containing only entities of Contractors type. This can get really interesting if you have a richer hierarchy. OFTYPE is equivalent to query below using TREAT and IS OF that will be discussed ahead:

SELECT VALUE TREAT(e asMySchema.Contractor)

FROM Employees as e

WHERE e IS OF (MySchema.Contractor)

nominalTypeInstance IS [NOT] OF ([ONLY] type) is a predicate that can be used to determine if a given nominal type instance (nominalTypeInstance) is the same as type or sub-type of type . The given nominalTypeInstance must be a sub-type or super-type of type otherwise a compile type error will be raised. If nominalTypeInstance is determined to be the same as type or a sub-type of type at runtime, then IS OF returns TRUE otherwise returns FALSE. If nominalTypeInstance is NULL, then IS OF returns NULL. If the optional ONLY keyword is specified, then IS OF returns TRUE for instances of nominalTypeInstance that are strictly equivalent to type.

TREAT(nominalTypeInstance AS type)

tries to re-type a nominal instance as type. If nominalTypeInstance type is a sub-type or super-type of type, then TREAT returns an instance of type by retyping nominalTypeInstance as type. Otherwise, a NULL is returned. If nominalTypeInstance and type do not have a sub-type or super-type relation, then a compile time error will be raised.

Paging

Paging is another new feature of the Entity Framework. Other SQL dialects enables ‘physical’ paging as late additions to the language using relatively complex queries - some of the constructs are relatively intuitive others require more work. In EntitySQL, paging is a built in feature or query expressions and can be expressed by declaring a SKIP and LIMIT (though optional) sub-clauses to the ORDER BY clause, as in:

SELECT p.ProductName

FROM NorthwindContainer.Products as p

ORDER BY p.UnitPrice SKIP 5 LIMIT 5

SKIP means what the name implies. First we read all products, project its name, sort according to the ORDER BY specification and then ‘skip’ the first 5 results, which in practical terms, means the result set starts at the 6^th element the of the ordered set.

SELECT p.ProductName

FROM NorthwindContainer.Products as p

ORDER BY p.UnitPrice LIMIT 5

LIMIT is equivalent to TSQL TOP expression. It will restrict the number of elements of the resulting set to the value specified - in this case 5. Note that SKIP and LIMIT can only be used if an ORDER BY clause is present. The reason is that for paging to be deterministic, the result set needs to be ordered. EntitySQL also supports TOP in the same fashion as TSQL.

Imagine now that you have an application and want to enable scrolling over a collection of entities without having to read all the data or having to write specific code to handle that:

SELECT VALUE p

FROM NorthwindContainer.Products as p

ORDER BY p.UnitPrice desc

SKIP @pageStart -- controls what ‘page’ to show

LIMIT 20 –- each page has 20 items

In this query, you can control the ‘page’ you want by setting @pageStart parameter.

Relationship Navigation

Typically, traversing relationships requires the user to write JOINs and know about Primary and Foreign keys; and possibly about relationship tables. For instance, finding all products that belong to a given category would require one to write in pseudo SQL something like (for the sake of the sample code below, we are assuming that a hypothetical relationship table – CategoryProducts -- exists):

SELECT P

FROM dbo.Products as p

JOIN dbo.CategoryProducts as pc ON p.ProductID = pc.FKProductID

JOIN dbo.Categories as c ON pc.FKCategoryID = c.CategoryID

WHERE c.CategoryName = ‘Beverages’

In EntitySQL, the query would simply be:

SELECT VALUE P

FROM northwindcontainer.Products as P

WHERE p.Category.CategoryName = 'Beverages'

Note that you don’t need to know about how the relationship is actually implemented in the logical schema, or if there is a relationship table or not, what are the columns that form the primary and foreign keys. Note also, that if other Relationship properties where defined, you could ‘navigate’ through them just like any other property.

In the sample above, we used a Navigation Property. Navigation properties are defined in the schema as shown bellow (note the tag NavigationProperty):

</EntityType>

The navigation property defines a name for the property, declares what relationship type to use and the ‘direction’ of the navigation.

Earlier, we mentioned that EntitySQL using SQL like style. The following example shows how to get same results by using explicit joins. Note that you can just rely on the association set abstraction to navigate through the relationship.

SELECT P.Productname

FROM northwindcontainer.Products as P

JOIN northwindcontainer.CategoryProducts as PC ON PC.Product.ProductID = P.ProductID

JOIN northwindcontainer.Categories as C ON PC.Category.CategoryID = C.CategoryID

WHERE c.CategoryName = 'Beverages'

Here is yet another example that shows how you can use the KEY () operator to abstract what columns constitutes the keys of an entity even a association set.

SELECT P.productname

FROM NorthwindContainer.Products as P

JOIN NorthwindContainer.CategoryProducts as PC ON Key(PC.Product) = ROW(P.ProductID)

JOIN NorthwindContainer.Categories as C ON Key(PC.Category) = ROW(C.CategoryID)

WHERE c.CategoryName = 'Beverages'

The examples showed above were many-to-one relationships. Navigating relationships in the Entity Framework is reversible and you don’t need to worry about the details on how to reverse it. Here is an one-to-many example: suppose you wanted all the products that belong to ‘beverages’ category and has the Unitprice < 10.

SELECT p.Productname

FROM northwindcontainer.Categories AS c, c.Products AS p

WHERE c.CategoryName = 'Beverages' AND p.UnitPrice < 10m

Here we use something called ‘left-correlation’. Note the reference to c.Products in the FROM clause. The FROM clause allows one to reference input collections already defined in the FROM clause (hence the name left-correlation). It is important to remind that the FROM clause expects collections otherwise it will fail during compilation. This is the reason why we cannot use a many-to-one left-correlation as in the example below, since p.Category is a singleton not a collection .

SELECT p.Productname

FROM northwindcontainer.Products AS p, p.Category AS c

WHERE c.CategoryName = 'Beverages' AND p.UnitPrice < 10m

But you can rework the query in the following fashion (though it may not be the most optimal approach)

SELECT p.Productname

FROM northwindcontainer.Products AS p, {p.Category} AS c

WHERE c.CategoryName = 'Beverages' AND p.UnitPrice < 10m

Navigation properties are very convenient as we have demonstrated above. EntitySQL has another way to navigate relationships by using the NAVIGATE() operator. It is more powerful and requires a little more work than the handy navigation property. We will cover the NAVIGATE() operator in future postings.

Multi(set) operations

There are several (multi)set operators in EntitySQL. Set arguments can be (with few exceptions) collections of most EDM types, not only primitive types. For instance the following query shows a polymorphic query using EXCEPT with full entities. DiscontinuedProduct is a sub-type of Product. Imagine you want the collection of Products that are not DiscontinuedProduct.

using (ObjectContext oc = new ObjectContext(myConnectionString, "NorthwindContainer")) {

oc.Connection.Open();

string myQuery = "Products EXCEPT OFTYPE(Products, Microsoft.CDP.Samples.Northwind.DiscontinuedProduct)";

foreach (Product p in new ObjectQuery<Product>(myQuery, oc)) {

Console.WriteLine(p.ProductName);

}

SET(collection) returns a proper set by removing duplicates of a collection:

SET({1,1,1,2,2})

-- equivalent to

SELECT VALUE DISTINCT i FROM {1,1,1,2,2} AS i

Note that entities and rows are comparable in EDM. And you don’t need to necessarily extract the fields to compare them. Entities – as expected, are compared by their keys; ROWS are compared using structural equivalence – they must have the same ‘shape’ (the number of columns and their types must be compatible). Collections are not comparable.

e [NOT] IN collection is used to check if a given value belongs to a collection.

1 IN {1,2,3} -- returns TRUE

1 IN {-1,-2,-3} -- returns FALSE

Say you just want to find out if a given Product is discontinued or not.

DEREF(CREATEREF(Products, ROW(@myProductID)))

OFTYPE(Products, Microsoft.CDP.Samples.Northwind.DiscontinuedProduct)

This will returns TRUE if the product was discontinued and FALSE otherwise.

[NOT] EXISTS collection returns TRUE if collectionis not (is) empty.

-- returns TRUE

EXISTS({1})

-- returns FALSE

EXISTS(SELECT v FROM {-1,-2,-3} as v WHERE v == 1)

-- returns TRUE if collectionA and collectionB are mutually exclusive

NOT EXISTS(collectionA INTERSECT collectionB)

collection UNION [ALL] collection

collection INTERSECT collection

collection EXCEPT collection

These are the traditional set/multiset operators. EXCEPT is really set difference – A EXCEPT B, is the same as A – B and gives the elements of A that are not elements of B. (Note that any duplicates are eliminated)

-- returns {1,3,4}

{1,2,3} UNION {3,4,5}

-- returns {1,3,3,4}

{1,2,3} UNION ALL {3,4,5}

-- returns {3}

{1,3} INTERSECT {3,4}

-- returns Corporate Customers that are DeliquentCustomer

CorporateCustomers INTERSECT DelinquentCustomers

-- returns {1}

{1,3} EXCEPT {3,4}

-- returns all loans that are not mortgages

Loans EXCEPT Mortgages

A OVERLAPS B is ‘syntactic sugar’ for EXISTS(A INTERSECT B).

-- returns true

{1,2,3} OVERLAPS {3,2,5}

-- returns TRUE if there exists CorporateCustomers that are DelinquentCustmer

CorporateCustomers OVERLAPS DelinquentCustomers

ANYELEMENT(collection) extracts some element from a multiset. The result type of ANYELEMENT is the collection element type. For instance, if the collection is of type Collection<int>, then the result type of ANYELEMENT(collection<int>) is int. This is different from most set/multiset operators seen so far since most of them either return another collection or a Boolean. It is worth mentioning that ANYELEMENT is not deterministic when dealing with collections whose cardinality is greater than 1 – some arbitrary element of the collection is returned.

If the input collection is empty, ANYELEMENT returns a NULL.

-- will return one of 1, 2 or 3

ANYELEMENT({1,2,3})

-- will return 1

ANYELEMENT({1})

FLATTEN(collection) can be used to ‘lift’ nested collections. In other words, to ‘extract’ a collection from inside another collection.

-- returns true

FLATTEN(

SELECT VALUE c.Products

FROM Categories AS c

WHERE c.CategoryName LIKE 'BEV%')

The result of the inner SELECT is a collection<collection<Product>>. FLATTEN will produce a new collection of type collection<Product>. FLATTEN is a shortcut for:

SELECT VALUE p

FROM Categories AS c, C.PRoducts as p

WHERE c.CategoryName LIKE 'BEV%'

Note that the result is not just the ‘structural’ lifting of the inner collection. The values of the inner collection are lifted and ‘combined’ in the result.

Mark Shields
Software Architect, ADO.NET

Fabio Valbuena
Software Developement Engineer, ADO.NET

EntitySQL

Samples

Types

Query Expressions

Multi(set) operations

Additional resources