Entity Data Model (EDM) in-depth analysis, Part 3

EntityClient

The Entity Framework (Entity framework) introduces a new ado.net provider EntityClient based on the Ado.net 3.5 provider. EntityClient looks very similar to the ado.net provider used earlier, providing the first abstraction that allows developers to execute queries according to the EDM using standard Connection, Command, and DataReader objects. It also adds the Client view engine (defined by the EDM) that is required to map the domain model to the underlying relational database schema. When necessary, EntityClient can use the ESQL query string to allow developers to process entities in rows and columns without having to generate classes to represent the conceptual schema.

1. EntityCommand Query returns entity type

Entity SQL can also be executed through EntityClient, although the code is verbose, but in some cases it is also an advantage.

1 first create entityconnection, reuse the connection string for the Northwind data context, and open the connection.

2 Create the EntityCommand object and pass in the Entity SQL statement and the database connection object.

3 creates the DbDataReader object and loops through the returned result set.

NorthwindEntities context = new NorthwindEntities();

EntityConnection conn = new EntityConnection(context.Connection.ConnectionString);
conn.Open();

var sql = "SELECT VALUE emp FROM NorthwindEntities.Employees AS emp";
EntityCommand cmd = new EntityCommand(sql, conn);

DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
　Console.WriteLine("{0} {1} {2} {3}", reader["EmployeeID"], reader["LastName"],
　　　 reader["FirstName"], reader["Country"]);
}

When using SequentialAccess's dbdatareader, you need to access the data carefully, and be sure to read it sequentially.

If you change the order of the members, the InvalidOperationException exception will be thrown-"attempt to read from column ordinal ' 0 ' are not valid. WithCommandBehavior.SequentialAccess, you may only read from column ordinal '2' or greater."

Console.WriteLine("{0} {1} {2} {3}", reader["LastName"], reader["EmployeeID"],
　reader["FirstName"], reader["Country"]);

2. EntityCommand query returns anonymous type

The same technology can be used to return anonymous types.

EntityConnection conn = new EntityConnection(context.Connection.ConnectionString);
conn.Open();

var sql = "SELECT emp.LastName, emp.FirstName " +
　　　　　　　　　 "FROM NorthwindEntities.Employees AS emp";
EntityCommand cmd = new EntityCommand(sql, conn);

DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
　Console.WriteLine("{0} {1}", reader["LastName"], reader["FirstName"]);
}