In this article by Joydip Kanjilal, author of the book Entity Framework Tutorial - Second Edition explains how Entity Framework contains a powerful client-side query engine that allows you to execute queries against the conceptual model of data, irrespective of the underlying data store in use. This query engine works with a rich functional language called Entity SQL (or E-SQL for short), a derivative of Transact SQL (T-SQL), that enables you to query entities or a collection of entities.

(For more resources related to this topic, see here.)

An overview of the E-SQL language

Entity Framework allows you to write programs against the EDM and also add a level of abstraction on top of the relational model. This isolation of the logical view of data from the Object Model is accomplished by expressing queries in terms of abstractions using an enhanced query language called E-SQL. This language is specially designed to query data from the EDM. E-SQL was designed to address the need for a language that can query data from its conceptual view, rather than its logical view.

From T-SQL to E-SQL

SQL is the primary language that has been in use for years for querying databases. Remember, SQL is a standard and not owned by any particular database vendor. SQL-92 is a standard, and is the most popular SQL standard currently in use. This standard was released in 1992. The 92 in the name reflects this fact. Different database vendors implemented their own flavors of the SQL-92 standard.

The T-SQL language was designed by Microsoft as an SQL Server implementation of the SQL-92 standard. Similar to other SQL languages implemented by different database vendors, the E-SQL language is Entity Framework implementation of the SQL-92 standard that can be used to query data from the EDM.

E-SQL is a text-based, provider independent, query language used by Entity Framework to express queries in terms of EDM abstractions and to query data from the conceptual layer of the EDM.

One of the major differences between E-SQL and T-SQL is in nested queries. Note that you should always enclose your nested queries in E-SQL using parentheses as seen here:

SELECT d, (SELECT DEREF (e) FROM NAVIGATE (d, PayrollEntities.FK_Employee_Department) AS e) AS Employees FROM PayrollEntities.Department AS d;

The Select VALUE... statement is used to retrieve singleton values. It is also used to retrieve values that don't have any column names. However, the Select ROW... statement is used to select one or more rows. As an example, if you want a value as a collection from an entity without the column name, you can use the VALUE keyword in the SELECT statement as shown here:

SELECT VALUE emp.EmployeeName FROM PayrollEntities.Employee as emp

The preceding statement will return the employee names from the Employee entity as a collection of strings.

In T-SQL, you can have the ORDER BY clause at the end of the last query when using UNION ALL.

SELECT EmployeeID, EmployeeName
From Employee
UNION ALL
SELECT EmployeeID, Basic, Allowances
FROM Salary
ORDER BY EmployeeID

On the contrary, you do not have the ORDER BY clause in the UNION ALL operator in E-SQL.

Why E-SQL when I already have LINQ to Entities?

LINQ to Entities is a new version of LINQ, well suited for Entity Framework. But why do you need E-SQL when you already have LINQ to Entities available to you? LINQ to Entities queries are verified at the time of compilation. Therefore, it is not at all suited for building and executing dynamic queries. On the contrary, E-SQL queries are verified at runtime, so they can be used for building and executing dynamic queries.

You now have a new ADO.NET provider in E-SQL, which is a sophisticated query engine that can be used to query your data from the conceptual model. It should be noted, however, that both LINQ and E-SQL queries are converted into canonical command trees that are in turn translated into database-specific query statements based on the underlying database provider in use, as shown in the following diagram:

We will now take a quick look at the features of E-SQL before we delve deep into this language.

Features of E-SQL

These are the features of E-SQL:

• Provider neutrality: E-SQL is independent of the underlying ADO.NET data provider in use because it works on top of the conceptual model.
• SQL like: The syntax of E-SQL statements resemble T-SQL.
• Expressive with support for entities and types: You can write your E-SQL queries in terms of EDM abstractions.
• Composable and orthogonal: You can use a subquery wherever you have support for an expression of that type. The subqueries are all treated uniformly regardless of where they have been used.

In the sections that follow, we will take a look at the E-SQL language in depth. We will discuss the following points:

• Operators
• Expressions
• Identifiers
• Variables
• Parameters
• Canonical functions

Operators in E-SQL

An operator is one that operates on a particular operand to perform an operation. Operators in E-SQL can broadly be classified into the following categories:

• Arithmetic operators: These are used to perform arithmetic operations.
• Comparison operators: You can use these to compare the values of two operands.
• Logical operators: These are used to perform logical operations.
• Reference operators: These act as logical pointers to a particular entity belonging to a particular entity set.
• Type operators: These can operate on the type of an expression.
• Case operators: These operate on a set of Boolean expressions.
• Set operators: These operate on set operations.

Arithmetic operators

Here is an example of an arithmetic operator:

SELECT VALUE s FROM PayrollEntities.Salary AS s
    where s.Basic = 5000 + 1000

The following arithmetic operators are available in E-SQL:

• + (add)
• - (subtract)
• / (divide)
• % (modulo)
• * (multiply)

Comparison operators

Here is an example of a comparison operator:

SELECT VALUE e FROM PayrollEntities.Employee
    AS e where e.EmployeeID = 1

The following is a list of the comparison operators available in E-SQL:

• = (equals)
• != (not equal to)
• <> (not equal to)
• > (greater than)
• < (less than)
• >= (greater than or equal to)
• <= (less than or equal to)

Logical operators

Here is an example of using logical operators in E-SQL:

SELECT VALUE s FROM PayrollEntities.Salary
    AS s where s.Basic > 5000 && s.Allowances > 3000

This is a list of the logical operators available in E-SQL:

• && (And)
• ! (Not)
• || (Or)

Reference operators

The following is an example of how you can use a reference operator in E-SQL:

SELECT VALUE REF(e).FirstName FROM PayrollEntities.Employee
 as e

The following is a list of the reference operators available in E-SQL:

• Key
• Ref
• CreateRef
• DeRef

Type operators

Here is an example of a type operator that returns a collection of employees from a collection of persons:

SELECT VALUE e FROM
    OFTYPE(PayrollEntities.Person, PayrollEntities.Employee) AS e

The following is a list of the type operators available in E-SQL:

• OfType
• Cast
• Is [Not] Of
• Treat

Set operators

This is an example of how you can use a set operator in E-SQL:

(Select VALUE e from PayrollEntities.Employee
    as e where e.FirstName Like 'J%') Union All
    ( select VALUE s from PayrollEntities.Employee
    as s where s.DepartmentID = 1)

Here is a list of the set operators available in E-SQL:

• Set
• Union
• Element
• AnyElement
• Except
• [Not] Exists
• [Not] In
• Overlaps
• Intersect

Operator precedence

When you have multiple operators operating in a sequence, the order in which the operators will be executed will be determined by the operator precedence. The following table shows the operator, operator type, and their precedence levels in E-SQL language:

Expressions in E-SQL

Expressions are the building blocks of the E-SQL language. Here are some examples of how expressions are represented:

1;         //This represents one scalar item
{2};     //This represents a collection of one element
{3, 4, 5} //This represents a collection of multiple elements

Query expressions in E-SQL

Query expressions are used in conjunction with query operators to perform a certain operation and return a result set. Query expressions in E-SQL are actually a series of clauses that are represented using one or more of the following:

• SELECT: This clause is used to specify or limit the number of elements that are returned when a query is executed in E-SQL.
• FROM: This clause is used to specify the source or collection for retrieval of the elements in a query.
• WHERE: This clause is used to specify a particular expression.
• HAVING: This clause is used to specify a filter condition for retrieval of the result set.
• GROUP BY: This clause is used to group the elements returned by a query.
• ORDER BY: This clause is used to order the elements returned in either ascending or descending order.

Here is the complete syntax of query expressions in E-SQL:

SELECT VALUE [ ALL | DISTINCT ] FROM expression [ ,...n ] as C [ WHERE expression ]
[ GROUP BY expression [ ,...n ] ] [ HAVING search_condition ] [ ORDER BY expression]

And here is an example of a typical E-SQL query with all clause types being used:

SELECT emp.FirstName FROM PayrollEntities.Employee emp, PayrollEntities.Department dept Group By dept.DepartmentName Where emp.DepartmentID = dept.DepartmentID Having emp.EmployeeID > 5

Identifiers, variables, parameters, and types in E-SQL

Identifiers in E-SQL are of the following two types:

• Simple identifiers
• Quoted identifiers

Simple identifiers are a sequence of alphanumeric or underscore characters. Note that an identifier should always begin with an alphabetical character.

As an example, the following are valid identifiers:

a12_ab
M_09cd
W0001m

However, the following are invalid identifiers:

9abcd
_xyz
0_pqr

Quoted identifiers are those that are enclosed within square brackets ([]). Here are some examples of quoted identifiers:

SELECT emp.EmployeeName AS [Employee Name] FROM Employee as emp
SELECT dept.DepartmentName AS [Department Name] FROM Department as  dept

Quoted identifiers cannot contain a new line, tab, backspace, or carriage return characters.

In E-SQL, a variable is a reference to a named expression. Note that the naming conventions for variables follow the same rules for an identifier. In other words, a valid variable reference to a named expression in E-SQL should be a valid identifier too. Here is an example:

SELECT emp FROM Employee as emp;

In the preceding example, emp is a variable reference. Types can be of three versions:

• Primitive types like integers and strings
• Nominal types such as entity types, entity sets, and relationships
• Transient types like rows, collections, and references

The E-SQL language supports the following type categories:

• Rows
• Collections
• References

Row

A row, which is also known as a tuple, has no identity or behavior and cannot be inherited.

The following statement returns one row that contains six elements:

ROW (1, 'Joydip');

Collections

Collections represent zero or more instances of other instances.

You can use SET () to retrieve unique values from a collection of values. Here is an example:

SET({1,1,2,2,3,3,4,4,5,5,6,6})

The preceding example will return the unique values from the set. Specifically, 2, 3, 4, 5, and 6.

This is equivalent to the following statement:

Select Value Distinct x from {1,1,2,2,3,3,4,4,5,5,6,6} As x;

You can create collections using MULTISET () or even using {} as shown in the following examples:

MULTISET (1, 2, 3, 4, 5, 6)

The following represents the same as the preceding example:

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

Here is how you can return a collection of 10 identical rows each with six elements in them:

SELECT ROW(1,'Joydip') from {1,2,3,4,5,6,7,8,9,10}

To return a collection of all rows from the employee set, you can use the following:

Select emp from PayrollEntities.Employee as emp;

Similarly, to select all rows from the department set, you use the following:

Select dept from PayrollEntities.Department as dept;

Reference

A reference denotes a logical pointer or reference, to a particular entity. In essence, it is a foreign key to a specific entity set.

Operators are used to perform operations on one or more operands. In E-SQL, the following operators are available to construct, deconstruct, and also navigate through references:

• KEY
• REF
• CREATEREF
• DEREF

To create a reference to an instance of Employee, you can use REF() as shown here:

SELECT REF (emp) FROM PayrollEntities.Employee as emp

Once you have created a reference to an entity using REF(), you can also dereference the entity using DREF() as shown:

DEREF (CREATEREF(PayrollEntities.Employee, ROW(@EmployeeID)))

Summary

In this article, we explored E-SQL and how it can be used with the Entity Client provider to perform CRUD operations in our applications. We discussed the differences between E-SQL and T-SQL and the differences between E-SQL and LINQ. We also discussed when one should choose E-SQL instead of LINQ to query data in applications.

Resources for Article:

Further resources on this subject:

• Hosting the service in IIS using the TCP protocol [article]
• Entity Framework Code-First: Accessing Database Views and Stored Procedures [article]
• Entity Framework DB First – Inheritance Relationships between Entities [article]