Querying with Entity SQL
Entity SQL is similar to traditional SQL, but its emphasis is on the entities defined in an entity data model. eSQL also introduces dozens of new keywords too numerous to cover here. The MSDN Help documentation covers the eSQL language.
Listing 1 demonstrates how you can use the entity data model and request all of the customers, for example, from the northwindEntities object. (The base object created via the wizard is an ObjectContext; entities within an ObjectContext are defined as ObjectQuery<T>, where T is the entity type, such as Customer.) Listing 2 shows one example of an eSQL statement against an entity data model.
Listing 1 Entities can be requested from an entity model, with the results being returned as an ObjectQuery<T>.
northwindEntities entities = new northwindEntities(); ObjectQuery<Customers> customers = entities.Customers; Array.ForEach(customers.ToArray(), x => Console.WriteLine(x.CompanyName)); Console.ReadLine(); Console.Clear();
Listing 2 An eSQL statement against the northwindEntities Customers.
northwindEntities entities = new northwindEntities(); // eSQL SELECT VALUE with WHERE clause ObjectQuery<Customers> customers = new ObjectQuery<Customers>( "SELECT VALUE Customer FROM northWindEntities.Customers As Customer " + "WHERE Customer.CompanyName LIKE 'W%'", entities); Array.ForEach(customers.ToArray(), x => Console.WriteLine(x.CompanyName)); Console.ReadLine(); Console.Clear();
Listing 1 simply refers to all Customers. The CompanyName is displayed using Array.ForEach and the Lambda expression x=>Console.WriteLine(x.Company). (Think of Lambda expressions as an evolution of anonymous delegates.)
Listing 2 selects just a subset of Customers in which CompanyName starts with the letter W. Notice the use of the keyword VALUE and northwindEntities. These are not SQL constructs. SELECT VALUE Customer means that you're selecting Customer entity objects. northwindEntities isn't part of the logical data model; it's part of the entity data model.
With SQL, you would express column names and a table name (perhaps a schema name, too) and get ADO.NET objects back. With Entity SQL, you get back an ObjectQuery<T> (where, in this case, T is a Customers object) from an eSQL query. It's also worth mentioning that you could accomplish the same thing with LINQ to Entities.