From ADO.Net 2.0 to ADO.Net Entity Framework E-SQL

7 בדצמבר 2007

From ADO.Net 2.0 to ADO.Net Entity Framework E-SQL

One of the pillars of the new ADO.Net Entity Framework is leveraging the current investments in earlier versions of ADO.Net. This means, that taking an application code, working with ADO.Net 2.0 and converting it to work against ADO.Net Entity Framework should be rather easy.

This post shows a sample code using the ADO.Net 2.0 provider model. Then, after creating a simple model, shows the code that is used to query that model using ADO.Net Entity Framework.

Using ADO.Net 2.0 Code

Lets say I want to query from the Northwind Customers table and print out the CustomerID, CompanyName, and City columns of the ALFKI customer. Today, the code I'll use to perform this query using simple ADO.Net 2.0 is:

using (SqlConnection conn = new SqlConnection("…"))

{

  SqlCommand cmd = conn.CreateCommand();

  cmd.CommandText = "SELECT * FROM Customers as c WHERE c.CustomerID = 'ALFKI'";

 

  conn.Open();

  SqlDataReader reader = cmd.ExecuteReader();

 

  while (reader.Read())

  {

    Console.WriteLine("{0} {1} {2}", reader["CustomerID"], reader["CompanyName"], reader["City"]);

  }

}

Creating a simple Entity Data Model

The first thing we would usually want to do when working with ADO.Net Entity Framework is to create an Entity Data Model (EDM) that represents the entities in the eyes of the application, which can be different from the way it is represented in a relational database.

To do this, you should add a new item to your project of type ADO.Net Entity Data Model, which is a new item template that is installed with the Entity Framework Tools bits.

 E-SQL

Adding this model starts a the Entity Data Model Wizard that lets you create an empty model or generate on from an exiting database. During this wizard, after selecting the database connection to use in order to retrieve the database schema from, the connection string will show up. For example:

metadata=.\Northwind.csdl|.\Northwind.ssdl|.\Northwind.msl;provider=System.Data.Sql

I will talk later about what those connection string parts mean, but for now you just have to notice that it is different from the ordinary connection string we are familiar with.

For this example, I will only select the Customers table from the database when selecting the database objects to import into the model, and click finish the wizard.

E-SQL

E-SQLCompleting this wizard does several things: It adds the new model (a file of type .edmx) to the solution, and adds a reference to the assembly that contains the runtime of the Entity Framework (System.Data.Entity.dll).

E-SQLAdditionally, it adds a configuration file to the solution, and writes the connection string to it. Then, it opens the Entity Model Designer, that lets you design your Entity Data Model visually. Having selected only the Customers table during the wizard, the designer shows a visual representation of the Customers Entity that is mapped to that database table.

Now, that my application has an Entity Data Model that maps entities to database tables, I can query against it.

 

 

 

Querying the Entity Data Model using E-SQL

The ADO.Net Entity Framework introduces a new query language that works at the level of entities and not in the level of tables or views. Since the Model has the mapping information where the data of each entity comes from, whether its a SQL Server database and any database of another provider, we can query the model data to get the entities data we want – E-SQL (Entity SQL) is that it is provider independent.

So, in order to use E-SQL to query the data, we no longer use the SqlConnection and SqlCommand objects, but instead, we use the EntityConnection and EntityCommand objects. As this example:

using (EntityConnection conn = new EntityConnection("…"))

{

  EntityCommand cmd = conn.CreateCommand();

  cmd.CommandText = "SELECT VALUE c FROM NorthwindEntities.Customers as c WHERE c.CustomerID = 'ALFKI'";

 

  conn.Open();

  EntityDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

 

  while (reader.Read())

  {

    Console.WriteLine("{0} {1} {2}", reader["CustomerID"], reader["CompanyName"], reader["City"]);

  }

}

Notice how similar this code is to the previous code that was using ADO.Net 2.0!

Querying the Object Services Layer

The ADO.Net Entity Framework can query the model in a few levels. We've just seen how to use the EntityClient level using E-SQL, but we can also query the model and receive back objects.

Earlier, when we created the mode, Visual Studio has generated some code for us, specifically, the code for the Customers entity. Additionally, when we queried the model using E-SQL, we used the VALUE keyword, that returns the data as an object data, and not just as a collection of values like the ordinary DataReader.

In order to receive back objects, we can write this code:

using (ObjectContext ctx = new ObjectContext(connectionString))

{

  ObjectQuery<Customers> query = ctx.CreateQuery<Customers>(
    "SELECT VALUE c FROM NorthwindEntities.Customers as c WHERE c.CustomerID = 'ALFKI'");

 

  foreach ( Customers c in query )

  {

    Console.WriteLine("{0} {1} {2}", c.CustomerID, c.CompanyName, c.City);

  }

}

Notice that moving to the Object Services layer, I replaced the EntityConnection with an ObjectContext, and the EntityCommand with an ObjectQuery<T>. Having got a list of strongly typed customers objects, I can use then in a loop and use their properties when printing out the results.

Enjoy!

Add comment
facebook linkedin twitter email

2 comments

  1. ламинат24 באוגוסט 2008 ב 10:53

    8gThank's.9n I compleatly disagree with last post . vlq
    паркет 4q

  2. ламинат24 באוגוסט 2008 ב 21:25

    5iThank's.8j I compleatly disagree with last post . lug
    ламинированный паркет 5i

Comments are closed.