query entity data model with object services and entity sql

Queries can also be created against Entity Data Model with Object services. The classes which implement Object Services reside in the System.Data.Objects namespace.

Object Services enables us to query, insert, update, and delete data, against the instances of entity types. We can use LINQ or Entity SQL queries to work with object services. In this article we use Entity SQL to query against Entity Data Model.

The Entity SQL is the entity frameworks's T-SQL like query language. If you are comfortable with T-SQL language, you can choose this option. Even though the Entity SQL language is similar to T-SQL, there are many differences between T-SQL and Entity SQL.

The major advantage of using entity SQL over T-sql is that we can manage the relationships and inheritance as defined in the EDM. T-SQL is used basically for querying and managing relational databases, we cannot implement inheritance.

The statement
string querystring = "SELECT VALUE Customer FROM SalesOrderManagementEntities.Customer AS Customer";
is an Entity SQL query. In the above query, 'Customer' is the variable reference. 'Customer' is referred to as a variable in an Entity Sql. In the LINQ to Entities 'Customer' is referred to as control variable.

The keyword 'VALUE' specifies that a collection of single items is returned. In this case, Customer entities will be returned.

The variable 'Customer' is defined using the AS keyword and is referenced in the SELECT clause.

The SalesOrderManagementEntities is the EntityContainer, is a class inherited from the ObjectContext class and Customer (EntitySet) is the property of the class.

The statement ObjectQuery Customers = context.CreateQuery(querystring);
Creates an ObjectQuery.

In the above statement, the method CreateQuery creates an ObjectQuery using the Entity Sql query string. Note that the return type of query result is ObjectQuery.

Next, we will iterate through the collection of Customer items using for each loop.

using (var context = new SalesOrderManagementEntities())
{
	//Query all the customers using Entity SQL
	string querystring = "SELECT VALUE Customer FROM SalesOrderManagementEntities.Customer AS Customer";
	ObjectQuery Customers = context.CreateQuery(querystring);
	foreach (Customer result in Customers)
	Console.WriteLine(String.Format("{0} {1}",
             result.Name, result.Address));
}
In this article, we have seen how to query Entity Data Model using Entity SQL.