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

Entity Data Model (EDM)In-depth analysis, Part 2

Entity SQL is a new SQL language, which includes the concept-based query function not supported by the previous SQL language. ESQL extends the existing SQL language in a similar way as the relational model used in the EDM extension database. In addition, ESQL is not bound to any background database-specific syntax, so you can write queries (and/or applications) at one time, no matter which background database you are targeting.

Entity SQL is a text-based, set-oriented, delayed binding query language, is also affected by the T-SQL. You can use Entity SQL to create a query for EDM. Entity SQL can be executed either through Object Services components or by Entity Client components. Entity SQL is very flexible in design, so it becomes somewhat complicated. This article focuses on different query technologies. It only uses simple queries and does not contain complex conditions, associations, and aggregate formulas.
Previous articles in this series:
Entity Data Model (EDM) in-depth analysis, Part 1

1.Use ObjectQuery <T>Entity Type) Set

The following shows how to execute the Entity SQL query and return the collection of Entity-type instances.

1) first create the Northwind ObjectContext instance.

2) the Entity SQL statement itself is a string expression. In most cases, it is composed of SELECT-FROM query statements. In the SELECT statement, the VALUE keyword is used to indicate that the returned object is a data row.

3) Use Object Services components to perform the query. Call the ObjectContext factory method CreateQuery <T> () to create an ObjectQuery object, which indicates querying the storage data source. The query expression is an Entity SQL statement.

4) the Entity Framework uses Deferred loading ). Therefore, the SQL statement is executed only when explicit data is required. In this case, the query statement is executed only during the first iteration of ForEach.

NorthwindEntities context = new NorthwindEntities ();

Var SQL = "SELECT VALUE emp FROM NorthwindEntities. Employees AS emp ";

Var query = context. CreateQuery <Employee> (SQL );

Foreach (var emp in query)

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

In addition to using the factory method CreateQuery <T>, you can also directly create an ObjectQuery Object instance and pass in the Object Context parameter. The sample code is as follows:

NorthwindEntities context = new NorthwindEntities ();

Var SQL = "NorthwindEntities. Employees ";

ObjectQuery <Employee> query = new ObjectQuery <Employee> (SQL, context );

Foreach (var emp in query)

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

Add a WHERE condition below:

Var SQL = "SELECT VALUE emp FROM NorthwindEntities. Employees AS emp" +

"WHERE emp. Country = 'usa '";

Var query = context. CreateQuery <Employee> (SQL );

2.ObjectQuery with parameters <T>Query

Parameter variables are defined outside Entity SQL. In the query statement, @ compliance must be used as the prefix to define the variable name. The parameter is defined as an ObjectParameter object and then added to the ObjectQuery instance.

Add a Country variable below:

Var SQL = "SELECT VALUE emp FROM NorthwindEntities. Employees AS emp" +

"WHERE emp. Country = @ country ";

Var query = context. CreateQuery <Employee> (SQL );

Query. Parameters. Add (new ObjectParameter ("country", "USA "));

This function is also implemented using the ObjectQuery example object:

Var SQL = "SELECT VALUE emp FROM NorthwindEntities. Employees AS emp" +

"WHERE emp. Country = @ country ";

ObjectQuery <Employee> query = new ObjectQuery <Employee> (SQL, context );

Query. Parameters. Add (new ObjectParameter ("country", "USA "));

The ObjectParameter object can also be passed in the CreateQuery <T> method directly:

Var SQL = "SELECT VALUE emp FROM NorthwindEntities. Employees AS emp" +

"WHERE emp. Country = @ country ";

Var query = context. CreateQuery <Employee> (SQL, new ObjectParameter ("country", "USA "));

The third method is to use the Where extension method and use the keyword it to point to the current query statement:

Var SQL = "SELECT VALUE emp FROM NorthwindEntities. Employees AS emp ";

Var query = context. CreateQuery <Employee> (SQL)

. Where ("it. Country = @ country", new ObjectParameter ("country", "USA "));

3. ObjectQuery <T>Primitive Type)

In addition to the object type, you can also return a set of basic types. Therefore, make sure that the SELECT statement returns only one value, and specify the basic type to be returned in the CreateQuery method.

Var SQL = "SELECT VALUE emp. EmployeeID FROM NorthwindEntities. Employees AS emp" +

"WHERE emp. Country = @ country ";

Var query = context. CreateQuery <int> (SQL, new ObjectParameter ("Country", "USA "));

Foreach (var id in query)

Console. WriteLine ("{0}", id. ToString ());

Another example script:

Var SQL = "SELECT VALUE emp. Country FROM NorthwindEntities. Employees AS emp" +

"WHERE emp. EmployeeID = @ id ";

Var query = context. CreateQuery <string> (SQL, new ObjectParameter ("id", 1 ));

Console. WriteLine (query. First ());

In addition to using Entity SQL, you can also use the Query Builder method to achieve the same effect. Entity SQL provides the SelectValue method. You can mention the implicit row constructor and only return the specified column.

String country = context. Employees. SelectValue <string> ("it. Country", new ObjectParameter ("id", 1). First ();

Console. WriteLine (country );

4. ObjectQuery <T>Returns the anonymous type for the query.

You may also need to adjust the data and use ObjectQuery <T> to query the returned anonymous type. In the CreateQuery method, the SELECT statement is changed and the DbDataRecord class is used. The DbDataReader class is introduced in. NET 1.0. It supports binding data of any enumeration type.

Var SQL = "SELECT emp. LastName, emp. FirstName" +

"FROM NorthwindEntities. Employees AS emp ";

Var query = context. CreateQuery <DbDataRecord> (SQL );

Foreach (var emp in query)

Console. WriteLine ("{0} {1}", emp [0], emp [1]);

Another sample code:

Var SQL = "SELECT emp. LastName AS FamilyName, emp. FirstName" +

"FROM NorthwindEntities. Employees AS emp ";

Var query = context. CreateQuery <DbDataRecord> (SQL );

Foreach (var emp in query)

Console. WriteLine ("{0} {1}", emp ["FamilyName"], emp ["FirstName"]);

For more information about EntityClient, to be continued ---

Entity Framework recommendedRelated Articles:

1. Entity Framework-Update Model From Database, Part 1

Http://blog.entlib.com/EntLib/archive/2008/10/19/entity-framework-update-model-from-database-part-1.aspx

2. Entity Framework-Update Model From Database, Part 2

Http://blog.entlib.com/EntLib/archive/2008/10/19/entity-framework-update-model-from-database-part-2.aspx

3. Introduction to Entity Framework Architecture

Http://blog.entlib.com/EntLib/archive/2008/10/19/entity-framework-architecture.aspx

4. Compare the Data models of LINQ to SQL digoal and Entity Data

Http://blog.entlib.com/EntLib/archive/2008/10/19/linq-to-sql-diagram-entity-data-model.aspx

English link:

1. ADO. NET Entity Framework & LINQ to Entities,

Http://www.scip.be/index.php? Page = ArticlesNET12