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

Entity SQL (Entity sql), a new SQL language that adds a concept-based query feature that was not supported in previous SQL languages. ESQL extends the existing SQL language in much the same way as the relational model used in the EDM extension database. Additionally, ESQL is not bound to any background-specific syntax, so you can write queries (and/or applications) at once, regardless of which background database is not affected.

Entity SQL is a text-based, set-oriented, deferred-binding query language and is also affected by T-SQL. You can use Entity SQL to create queries to the EDM, Entity SQL can be executed either through the object Services components or through Entity Client components. Entity SQL Design is very flexible, so it becomes a bit more complicated. This article focuses on different query techniques, using only simple queries, without complex conditions, associations, and aggregation formulas.

This article series on the previous article:

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

1. Return entity type (Entity type) collection using objectquery<t> query

The following shows how to execute a entity SQL query that returns an instance collection of entity types.

1 Create the Northwind ObjectContext instance first.

2 The Entity SQL statement itself is a string expression, and in most cases consists of a Select-from query statement. Use the value keyword in a SELECT statement to indicate that the returned entity is a data row.

3 Execute the query using the object Services components. Invokes the ObjectContext factory method Createquery<t> () to create a ObjectQuery object that represents the query that stores the data source, and the query expression is a entity SQL statement.

4 The Entity framework entity frame adopts delayed loading (Deferred loading). Therefore, the SQL statement is actually executed only when the data is explicitly needed. In this case, the query statement is executed the first time the foreach is iterated.

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 create a ObjectQuery object instance directly and pass in the object context parameter, as shown in the sample code:

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<t> Query with Parameters

Parameter variables are defined outside of Entity SQL, and in query statements you need to define the variable names with the @ conformance as a prefix. The parameter is defined as a 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 functionality is also implemented with the ObjectQuery sample 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"));

ObjectParameter objects can also be passed directly to the Createquery<t> method:

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 approach is to use the where extension method, which points to the current query statement using the keyword it:

var sql = "SELECT VALUE emp FROM NorthwindEntities.Employees AS emp";
var query = context.CreateQuery<Employee>(sql)
　.Where("it.Country = @country", new ObjectParameter("country", "USA"));