Chapter 5. Working with Entity Client and Entity SQL

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.

In this chapter, we will take a look at both Entity Client and E-SQL and learn how to use them in our applications.

We will discuss the following areas:

• An overview of the E-SQL language

• Differences between E-SQL and T-SQL

• When to choose E-SQL over LINQ

• Working with the Entity Client

• Transaction management in Entity Framework

• Deferred loading and eager loading

Before we get started with Entity Client, we should have a proper understanding of E-SQL. This is a T-SQL-like query language used by the Entity Client provider. We will start this chapter with a discussion on the...

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...

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...

Data paging using E-SQL

Data paging is a concept that allows you to retrieve a specified number of records and display them in the user interface. The data is displayed one page at a time. You can use data paging to split the data rendered to the user into multiple pages for faster page downloads, an increase to user interface flexibility, and minimal load on the database server. Paging can be used when the volume of data to be displayed is substantial and you need it to be divided into pages of data records to be displayed more efficiently.

The following statement will return a result set that contains the top 10 records of the Employee table, ordered by employee names:

SELECT emp FROM PayrollEntities.Employee AS emp ORDER BY emp.EmployeeName LIMIT 10;

Suppose you need to display records 11 to 20 from the Employee table. Here is how you can do this:

SELECT emp FROM PayrollEntities.Employee AS emp ORDER BY emp.EmployeeName SKIP 10 LIMIT 10;

How does it work? When you say SKIP 10, it will skip...

Working with the ADO.NET Entity Client

The ADO.NET Entity Client is a data provider that provides a gateway to execute entity level queries using Entity Framework. You can use it to query against your conceptual model of data. Entity Client uses its own language called E-SQL, a storage independent language, to communicate with the conceptual model. You can execute the same E-SQL query against any data store. In other words, it is provider independent, and you need not make changes to your query if the underlying data store changes. Therefore, you can use the same E-SQL syntax to communicate to the conceptual model, regardless of the data store in use.

The E-SQL queries are converted to a command tree that is in turn passed to the storage-specific provider to generate native SQL statements. As an example, if you are using SQL Server as the database, the E-SQL queries that you are using will be converted to a command tree that will be passed to the ADO.NET provider for the SQL Server. This...

Other operations with E-SQL

In this section, we will take a look at how we can perform some additional operations with the E-SQL language. We will discuss the following:

• Inserting a record using E-SQL

• Inserting a record with a foreign key constraint

• Retrieving native SQL from EntityCommand

• Transaction management in E-SQL

Inserting a record using E-SQL

You can use E-SQL statements and easily perform CRUD operations. Let's assume that you have a stored procedure called InsertDesignation and you would like to use it to store a record in the designation table of your Payroll database. This is the code:

using (EntityConnection var conn = new EntityConnection("Name=PayrollEntities"))
 {
 try
  {
    conn.Open();
    EntityCommandvar cmd = conn.CreateCommand();
    cmd.CommandText = "PayrollEntities.Employee_Insert";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("FirstName", "Joydip");
    cmd.Parameters.AddWithValue("LastName", "Kanjilal");
    cmd.Parameters.AddWithValue...

Deferred, eager, and lazy loading

Eager loading refers to the process in which a query, when executed on a particular type of an entity, also loads the related entities at the same time. Lazy loading and eager loading are used for loading related entities that pertain to an entity. In the former case, the related entities are not loaded automatically along with its parent entity unless they are requested. In the latter case, the related entities are loaded automatically along with the parent entity.

To achieve eager loading in Entity Framework, you need to make use of the Include method.

Here is an example:

using (var context = new SecurityContext())
{
    // Load all user types and the related users
    var data = context.UserTypes
                          .Include(u => u.Users)
                          .ToList();
     // Load one user type and its related users
    var data = context.UserTypes
                        .Where(u => u.UserType == "ADUser")
                        .Include...

Summary

In this chapter, 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.

In the next chapter, we will take a look at Object Services and discuss how they can be used to perform CRUD operations against the Entity Data Model.