Working with the Entity Client Data Provider in Entity framework

Microsoft’s Entity framework is an open source ORM framework for ADO.Net that simplifies data access in your application by allowing you to write code to perform CRUD (Create, Read, Update, and Delete) operations.

In Entity framework you have a powerful client side query engine that you can take advantage of when you need to query data or perform CRUD operations on the underlying database.

The Entity Client Data Provider

In this section we will explore the Entity Client Data Provider — a client side query engine that enables you to execute your queries against the conceptual model of data. The Entity Client Data Provider enables you to execute queries against the Entity Data Model using syntax and approach that is similar to the way you use your ADO.Net Provider. The Entity Client Provider works with ESQL (Entity SQL), a text-based, provider independent query language. Note that both LINQ and ESQL queries are converted into canonical command trees which in turn are converted into statements that are specific to the database provider in use.

The MSDN states: “When a query is executed, it is parsed and converted into a canonical command tree, which is an object model representation of the query. Canonical command trees represent select, update, insert, and delete commands. All subsequent processing is performed on the command tree, which is the means of communication between the System.Data.EntityClient provider and the underlying .NET Framework data provider, such as System.Data.SqlClient.”

The ESQL query language is a SQL — like, provider neutral, composable query language with support for a rich set of operators. The ESQL query language also supports a wide variety of canonical functions that include the following:

Mathematical

Aggregate

Bitwise

String

Date and Time 

You use query expressions together with the query operators in ESQL to form your queries and execute then against the conceptual model of data.

Working with the Entity Client Provider

Let’s now dig into some code. In this section we would learn how we can get started using the Entity Client Provider.

The first step is to create an instance of the EntityConnection class. To do this, you would need to pass the connection string to the constructor of the EntityConnection class as shown below.

string connectionString = "specify your connection string here...";

EntityConnection entityConnection = new EntityConnection(connectionString);

You may open the connection the same way you do with ADO.Net. Here’s an example:

entityConnection.Open();

You can verify whether the connection has been successfully opened using the State property of the EntityConnection instance and checking if its value corresponds to ConnectionState.Open – ConnectionState is an enumeration.

To execute your queries, you would first need to use the EntityCommand object – similar to the ADO.NET command object. The following code snippet illustrates this.

String queryString = "Select value a from IDGEntities.Author as a";

EntityCommand entityCommand = new EntityCommand(queryString, entityConnection);

Now that the command object is created and initialized, you may want to use the ExecuteReader method of the EntityCommand class to execute your queries against the conceptual model of data. Here’s the code snippet for you to have a quick look and understand how you can use the ExecuteReader method on the EntityCommand object and then iterate through the results sequentially.

EntityDataReader entityDataReader = entityCommand.ExecuteReader(CommandBehavior.SequentialAccess);

        while (entityDataReader.Read())

          {

                Console.WriteLine(entityDataReader.GetValue(1));

          }

As you can see in the code snippet above, we have used the EntityDataReader to iterate through the records returned on execution of the ExecuteReader method of the EntityCommand object. The EntityDataReader can be used to work with a forward – only, read – only set of records.

Here’s the complete code listing — please make sure that you specify the connection string specific to the database you would want to connect to. You would also need to create a database and then generate an Entity Data Model out of it in your Visual Studio IDE.

string connectionString = "specify your connection string here...";

            using (EntityConnection entityConnection = new EntityConnection(connectionString))

            {

                if (entityConnection.State != ConnectionState.Open)

                {

                    entityConnection.Open();

                    String queryString = "Select value a from IDGEntities.Author as a";

                    using (EntityCommand entityCommand = new EntityCommand(queryString, entityConnection))

                    {

                        using (EntityDataReader entityDataReader = entityCommand.ExecuteReader(CommandBehavior.SequentialAccess))

                        {

                            while (entityDataReader.Read())

                            {

                                Console.WriteLine(entityDataReader.GetValue(1));

                            }

                        }

                    }

                }

            }

I’ll present more articles on Entity Framework in my future posts here. You can learn more on the Entity Client Provider and Entity SQL from my latest book, “Entity Framework Tutorial (Second Edition)“.